Many times, I’ve started with a company as one of their first, if not the first DBA. They’ve acquired enough servers now, with enough data moving around, that they just can’t continue as they are.
There’s no shortage of developers to can write stored procedures or SSIS packages, or to write a new page for the website, but things just seem to be going further and further downhill. Pages take longer to load, SSIS packages take longer to run, production issues spring up all over the place, and so on. Above all, they want the environment locked down. Way too many people have way too many permissions to the databases… and that’s one of the main reasons for the problems.
So, they decide to bring in a DBA to help them sort it all out.
The DBA enters the scene
Ok, here I am. I’m the new DBA and things are exactly what they said they were. I start with the first task: limiting permissions. I mean, if you’re bleeding to death, you want to prevent new cuts, right? It’s a good place to start.
Start by asking all the teams what permissions they need to do their jobs. What a surprise! Everyone who response, reports that they need sysadmin! Weird, right? There’s also a whole bunch of people that don’t even reply.
Next, I go to each one of them and ask what they actually do in the database. I get varied answers, but most of them amount to 1, running some kind of query to look up customer data, or 2, to run a report for something. I manage to hold back my rant about the huge disparity between sysadmin and read permissions, quietly decide on read permissions for them, and move on.
Then I come to a development system. I find the main developer on that box and ask what it is that he does. He currently has sysadmin, and he doesn’t need that. The dev starts to get a little irate. He tells me that it’s his box and he needs sysadmin because who else would do all the stuff he does on it? Well, I will. That’s what DBA means. “But it’s a dev box,” he says. “I can’t see any reason why I can’t have sysadmin on a dev box. I understand why you took it away from me in production, but in dev? Give me one good reason!”
The road to giving up sysadmin
Ahhh, one good reason. Ok, here we go then. Now is my opportunity to take away his excuses to have sysadmin. So, I start with a short series of questions.
What happens to the database when you back it up?
Nothing, it gets backed up.
I notice you’re backing up to the same drive the DB is on. What happens if that disk goes down?
We’ll restore from backup.
How? The backup disk is down.
Oh yeah.
Now, even if the disk isn’t down, what happens if the backups fill up the disk and you can’t insert data you need for a unit test?
I have to free the space.
And what happens if someone deletes one of your tables your developing against? Do you have a local copy so you can easily re-create it on the server?
Sometimes, but mostly I create them directly on the server.
So what kind of pace do you keep here? Are you allowed to take as long as you want to dev something, or is it pretty fast-paced?
Oh no, we’re slammed all the time with bug fixes and new features. It’s constant.
And if someone runs a big query in the middle of you writing an SP, and the performance is so bad you have to stop and reboot the server? What about that? Have you planned for that in your daily work cycle?
No, that happens sometimes and I usually lose work. I don’t have time for that.
I’m your sysadmin now, because…
Right, so what you’re looking at here is a production server. This box, though you use it for development, is a production development server. Sure, it’s not customer-facing, but that doesn’t mean it’s not production. If any of those things happen, your current dev effort will be screwed. You may have taken two days to get something just right, only to have to start over. You don’t have time for those mistakes. Nobody does!
So no, you can’t have sysadmin on your own dev box, because it’s actually a production box.
I’m also taking over the management of the backups, so we can actually have something that’ll restore when we need it.
I’m going to put you on a schedule so everyone knows what can and can’t be restored.
I’m going to setup reindexing routines so your queries will hopefully keep going fast. There’s this fabulous set of tools called Minion that I’m going to use. They make this kind of thing a breeze.
So anyway, leave everything to me. You’ll be able to create objects in the database, but you won’t be able to drop anything. Let’s see how that goes, and then we’ll look at whether we’ll have to put you into resource groups to keep from maxing out the server and causing people to lose work.
Denouement
So you see, the moral of this story is that just because you’ve had sysadmin since you got there, and just because the box isn’t customer-facing, doesn’t mean it isn’t production. If you’re on a tight dev cycle and you can’t afford to lose work, the more you need permissions locked down…even on your own dev box.