Voice of the DBA

Category: Editorial

  • The Proliferation of Roles

    The best practice guidance for SQL Server security is to use roles for permissions, instead of granting rights to users. I’ve always followed this guidance in my career. I’ve learned that if one person needs access, sooner or later someone else will. Even if it’s an automated process, I’ll use a role so that I can build a test login to ensure I’ve configured things correctly.

    In small companies, or in relatively static applications, this makes perfect sense and few people argue with the rule. That is, until they get some requirement that only one account will ever be used. Then they want to just grant rights to a user. After all, why add the role for one person. See my thought above. Sooner or later, someone else will want access.

    However I also have had people complain that if each new required position or process needs their own role, sooner or later we’ll have this proliferation of dozens of roles.

    True.

    I worked in a large Fortune 100 company and we had thousands of groups in our AD forest, and easily dozens of roles in many databases. While that might seem complex and confusing, it wasn’t bad. We named roles to match AD groups or job functions, and adding in new users was simple as we usually mapped them to the same roles as a previous user. If a new system or person needs access, usually their access is the same as some other account.

    However, I know they can be complex, so I’m wondering if you have any tips, tricks, hints, or even gotchas for using roles. I’d also be curious if you think there are cases where roles don’t make sense.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Solving Tempdb Issues

    While reading Remus Rusanu’s piece on SQL Server 2016 development, there was an item that struck me. There’s a part of the piece where he notes that an engineer at Microsoft realized that there was a tempdb issue with Azure SQL Database. To solve it, a failover was needed, which could be problematic. The basic issue was that tempdb files were sized unequally, discovered after “months of investigation.”

    Now, on one hand this seems silly. After all, we’ve known since SQL Server 2005 that the guidance was for all files to be sized the same. Shouldn’t engineers at Microsoft be following all the practices  known for optimizing SQL Server performance? I think all Microsoft people should follow this, especially those working on other products (*cough* Sharepoint *cough*), but at the very least SQL Server engineers should have a huge list, perhaps with PBM or unit tests, that warn about non-compliance.

    On the other hand, since we’ve known this is an issue since SQL Server 2005, why does SQL Server allow this? I’d think some simple tooling and a few checks in code could eliminate this as an issue in any new install. Catch the issue during upgrades and grow (or shrink) files to be the same size, or at least warn the administrator. I know there are situations where you need to add a new file in an emergency, but shouldn’t this be an item we push administrators to correct quickly after the emergency is past? Or at least force them to consciously make an exception.

    There are plenty of limitations and restrictions in SQL Server systems that Microsoft forces on us. We have limits on where new features can be used, various historical limits on function parameters, and more. Why not also just enforce limits that prevent issues like this? I’m sure people will complain, but wouldn’t those complaints be outweighed by less issues overall from all customers?

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Use Tools

    This editorial was originally published on May 23, 2012. It is being re-run as Steve is out of town.

    As someone that works with Information Technology, I usually work on applications designed to make work easier. For example, we have accounting systems that ease the handling of debits and credits, they detect mistakes in data entry, and in general require less people to handle the accounting needs of many firms. Do we have less accountants?

    I suppose we might have less, but it seems that the finance departments, handling AP and AR in many companies is as large as I remember from my younger days. Perhaps the department is smaller than it might otherwise be, but it’s usually not small.

    In IT, we have all kinds of tools available to us that can help with our jobs. What’s more, we can create new tools as needed to do our jobs. Powershell or Perl scripting, Codeplex projects, and more are available to most of us to help us better manage systems. In SQL Server, we even have a built in framework, Policy Based Management, that helps us prevent changes or problematic configurations. If you find a third party tool that can help, you can make a case for its purchase, using the time savings translated to actual costs. One of those tools might really make your job more enjoyable..

    In theory, we ought to be able to manage many more systems per person than we used to. Does that many that we need less people? Perhaps, but it seems we grow systems fast enough that we still need to hire more staff in many environments.

    I so often see people working in IT fail to take advantage of all the tools we have to automate much of their jobs. They often tell me if they automate too much of their jobs, they won’t be needed and may get let go. Personally I think that’s an excuse not to exercise your skills, challenge your mind, and get rid of tedious work There’s no shortage of work to be done in most companies. If that’s true, why not use your tools to get rid of the tedious work and spend your time on something more interesting?

    Steve Jones

  • A Lifetime of Software

    This editorial was originally published on April 27, 2012. It is being re-run as Steve is out of town.

    I’ve been working with computers and software for most of my life, but it’s been a career for a couple of decades now. I don’t do as much technical work as I used to, mostly testing and experimenting, but my job is related to SQL Server and software, and I anticipate that’s what I’ll be doing for the next two decades.

    However that’s not necessarily the plan for everyone that works in the technology business. I know plenty of people that would like to move into management, or even move into some other career field if they can afford to do so. In the responses to many editorials in the past, I read that quite a few people think the technology business isn’t a great choice and wouldn’t encourage their children to enter this field.

    I disagree, and think that this business has been very good to me, overall I’ve enjoyed it, and I think it would make a good career  for any of my kids. However this Friday I wanted to ask the rest of you what you think.

    Do you expect to work in the technology field until you retire?

    I know many of you will change your focus in technology, perhaps moving to develop from DBA, vice versa, or moving into another field. I know most of you will change companies before you retire, but I’m curious about whether you think you will remain a technology worker for the rest of the time you work.

    Steve Jones