A Wandering Mind

As a development DBA I often got requests from developers to elevate their rights to sysadmin for the purpose of “trying out some things for a proof of concept.” Examples would include things like Service Broker, replication, and CLR. And every time my answer was the same: No.

Now, before you stop reading and go off muttering “Man, what a typical DBA-hole”, you should understand why I took this view. The systems where the developers wanted this access were shared enterprise development and QA systems, which were often used by many different teams. If I had caved and given the developers this access, and they’d somehow managed to take the instance down or otherwise affect its stability, I would have had scores of people standing at my desk screaming about lost productivity. And rightfully so, because as a DBA it’s my job to make sure their systems are working.

On the other hand, there is clearly a need to allow folks to experiment with methods and architectures. Otherwise, how would we ever learn how to do new things, or know if a particular approach is going to work? But the enterprise environment isn’t the place for that. Instead, this is where one of a developer’s most important tools comes into play: a sandbox environment.

The basic concept is this: you have an area where you can play to your heart’s content without fear of affecting others. You can tear things down and rebuild them as you please (and hopefully in automated fashion, because good developers are always lazy). Personally I like to have this setup on my personal machine, using a series of virtual machines. With hard drives and memory being pretty cheap, SQL Server Evaluation Edition, and great free virtualization products like VirtualBox readily available, there really isn’t an excuse not to have something like this. Using some scripting and products like the excellent SQLSPADE automated SQL installation framework, you could spin up a couple SQL servers in a matter of an hour.

Once you’ve proven out your idea, and gotten the setup process streamlined (because no DBA ever wants to get a multi-page document full of screenshots of clicking “Next” buttons), that’s when you can move on to the shared environments, to make sure things are going to work properly in a leveraged setup.

Since moving into a full time development role, I’ve become even more convinced this is an essential tool for being a successful database developer. It makes my life so much easier not having to constantly go through channels when I need something done like setting up a login on a server. Mind you, I still agree with those processes, because they protect the shared environment from renegades like me.

Several months back at SQL Saturday 121 in Philadelphia, I had a great conversation in the speaker’s room with Mike Hillwig (blog | twitter) about career paths. One point that he made which really stuck with me was (and I’m paraphrasing roughly here) that the best way to find the job you truly love is simply to figure out what it is you like about your current one, then find a job where that’s all you do. I remember he attributed the advice to someone else, but for the life of me I can’t remember who (Mike please feel free to jump in and jog my woeful memory if you read this); in any case, the words really resonated with me.

For the last three years and change I’ve been in some form of a DBA / support role. And while overall I really enjoy my job, there’s a lot of aspects of it that I don’t find terribly fun. For instance:

• Doing routine administrative work, such as creating logins or databases on servers, fixing broken backups, etc.
• Having an endless barrage of “this server is broke, please fix it” e-mails, when in nine out of ten cases, the problem is their code, not my server.
• Troubleshooting hard to trace infrastructure issues. Don’t even get me started on my frustration with Kerberos and Network Load Balancing.

But, let’s face it; those tasks are part of a DBA’s role.

After Mike’s words had rattled around in my brain for a few weeks, I began thinking: “Self! What are the things that you really enjoy about your job (outside of the people on your team, who are great)?” So I pulled out my thinking notepad, and started scribbling. The top three that I came up with, in no particular order, are:

• Solving problems – not to be confused with “troubleshooting”; perhaps a more apt description would be “determining solutions for business and technical problems”.
• Designing data structures – I’m by no means a data modeler, but I do enjoy the process of normalizing data, and creating elegant, simple structures to store information.
• Performance Tuning – In an ideal world this skill wouldn’t be necessary, since all code would be written properly from the start and scale well. Fortunately for me, that’s not the case. There’s just something incredibly satisfying about taking some god-awful Gordian knot of nested subqueries and views, untangling it, and seeing an application’s performance just soar through the roof.

After this exercise I began thinking about what kind of job would let me focus on these things. After some thought, I came to a startling conclusion: I had to join the dark side. I had to become a developer.

But not just any developer, mind you. I could never be one of those heads down, coding machines that are just handed specs and go off on their merry way. No, I needed to find a job where I would have a fair bit of interaction with people, while still writing the code and getting my hands dirty.

Then, almost as if by magic, an amazing offer came my way. I was given a chance to join an elite group of database architects / developers within my company. This team is essentially the SQL Server equivalent of the A-Team; If you have a problem with SQL Server, and no one else can help, you bring these guys in. They do not own any specific applications or databases themselves. Rather, they come in, do a targeted assessment of the situation, then perform surgical work to get things back on track. Need help determining how best to store your database in source control (because you are doing that, right?)? They’ll deliver tools to help script out and organize your code, plus make deploying new releases a painless process. Have a query that is running horribly and can’t figure out why? They’ll rip apart the code and rewire it behind the scenes, and show you how to do it yourself going forward. In many ways, they are a lot like an internal consulting group.

At the time the offer came down the team only had two members, but combined they have almost twice my lifetime in experience working with databases. With their workload getting larger and larger by the day, it became apparent some additional help was needed. And I’m truly honored to say I’ve been given the chance to join them.

It’s bittersweet, for sure. I have loved my time in production support, and will miss the people and fun times I’ve had there. But I’m also incredibly excited about this new opportunity and the challenges it will bring.

And to Mike (and whomever originally passed the advice down to him): thanks for the push.

I am a very firm believe that only a DBA should have sysadmin rights on a SQL server instance, including in development systems. The fact of the matter is that in the majority of cases, system administrative rights are nothing but overkill; developers don’t need (nor in many cases understand the how / why) to do things like change sp_configure settings or alter database level settings like auto_shrink (don’t get me started). What they do need is this:

• Full control over their own database (db_owner). This allows them to:
  1. Backup up the database before applying changes
  2. Change schema and security at the database level
• The ability to create and remove logins (but not system administrator logins) for use by their applications.
• The ability to restore a backup of their database to roll back changes as part of testing or development.

In my environments I give developers this basic set of rights by giving them these permissions:

1. db_owner on their own databases
2. db_creator at the server level

   When I give them this right it is for restoring their own backups, not for creating databases willy-nilly. This is explicitly communicated to them and violations result in revocation of this privilege.

3. ALTER ANY LOGIN at the server level

   See here for why I didn’t say securityadmin.

In most cases this takes care of the developer’s needs and I don’t get complaints. But from time to time, special requests come up. Yesterday I has one such item, where the developers wanted to be able to using Database Tuning Advisor and the built in SSMS reports such as the “Schema Change” report. Naturally, their request was for sysadmin rights. Not convinced, however, I looked into what was actually needed.

Database Tuning Advisor

In another case of RTFBOL, here is a clear explanation of what rights are required. Basically, a user with sysadmin rights has to initialize the system first. After that, any user with db_owner rights can use Tuning Advisor.

Built-In SSMS Reports

While I couldn’t find any built-in documentation on this subject, I was able to run a trace while running several reports (inlcuding index usage and schema changes). What I found was that the reports were doing one of two things: 1) querying various management DMVs, or 2) reading the default trace using fn_get_tracetable. Bases on these findings, I granted the VIEW SERVER STATE and ALTER TRACE permissions. Problem solved.

So no, Mr. Developer, you still can’t have sysadmin rights on my box.

Now that I’ve been through a full week in my new development-centered position, I thought I should take a few minutes to note some observations. It was overall a very pleasing first week, with already some lessons and changes made.

Developers (In General) Do Not Make Good Database Stewards

To me one of the most important roles of a DBA is that of data custodian. It’s your job to make sure data is kept safe, both from a security / access perspective, and also from an integrity / availability perspective. So far at least, my experience has shown that developers either a) don’t care or b) don’t know how to accomplish this goal. Now having been a developer myself, I do understand somewhat that their focus has to be on putting out code, not securing and stabilizing their environment. But at the same time, it was downright stunning to see some of the states these machines had gotten into. Even simple backups or integrity checks were mostly missing; granted a lost database wouldn’t mean immediate outages for clients, but it would mean lost work and project time.

People Are Remarkably Welcoming

Even though I’ve gone into this with the attitude that I am not a traffic cop here to lay down the law, I was concerned that any suggestions or attempts at change would be met with resistance. And while that has been the case for a few teams, largely I’ve gotten a very positive reception and folks seem genuinely eager to have me on board. A lot appear relieved that now they will have someone who will be watching over their systems, so they can focus on churning out the code.

The Three D’s Will Be Key

Discipline
I am going to have a lot of competing demands for my time, so it is going to be crucial that I make good use of what I have. Already projects are piling up and, while people are generally understanding that I am only one mere mortal, they also want theirs done first and fast. I’m going to have to make how I spend my time brutally efficient and wholly transparent. I think that last one is extremely important; if I’m going to be able to say with credibility that I can’t take something on until March, I’d better be able to show clearly what’s on my plate from now until then.
Documentation
Even though I’m essentially a team of one, I’m going to have to be ruthless about documenting procedure and policy. Because my backups (I technically have two of them) will not be in the midst of my daily work, documentation will be needed to ensure requests are handled appropriately and in a consistent manner when I’m not around. That’s why my rule will be that if I do something more than once, it must be written down somewhere. Period. I hate writing documentation as much as anyone, but it’s a necessary evil.
Diplomacy
If I’m going to have success getting these teams to change the way they interact with SQL Server, it’s going to take some serious cajoling, prodding, and yes, occasionally some pointed and sharp yelling. If I come charging into the room acting like some holy warrior who is here to save us all from the perils of bad T-SQL… well, I think the reaction would be as bad as that last image is ridiculous.

This is where my old psychology degree will probably come in handy. You see, I’m going to have to get into these folks heads; not in some Machiavellian, manipulative way (at least not too much), but in a genuine “let me understand your view so I can help you”, shrink-like kind of way. That means I’m going to have to listen to some degree of whining and complaining about how hard life is, how the business rules all and how they don’t have time to worry about defining a proper security model. I should seriously get one of those Freudian style couches for my office!

Ultimately, I had a great week and am still very excited for the future. Yes I’ll be busy, and yes I’m going to have an uphill battle at times. But I really do feel that I can make a difference and help those around me. Maybe I’m naive; only time will tell.