September 21, 2007

688 words 4 mins read

From the “all-your-databases-are-belong-to-me” department...

I figured the best way to start this spot is with 4 ways for absolutely anyone to make their DBA’s life easier.

  1. Never start a conversation with “I just need this one thing, really quick”. This rule can be expanded to replace one with any given number.
  2. Reasoning: You are not a DBA and most likely do not know what else the DBA is working on, it is much better to ask, “I have a couple things I need from you; do you have time to go over them now?”
  1. Don’t touch the development database(s). EVER. Seriously. Your DBA is in charge of monitoring changes to the database(s) and making sure they (eventually) get pushed to production. Unless you are given explicit permission by the DBA (and no, their higher ups or underlings do not count) do not attempt to modify ANY schema EVER. Doing so can cause serious problems during an upgrade cycle as everyone tries to figure out why functionality that worked well in development is going haywire in production.
  2. Don’t touch the production database(s). EVER. I really shouldn’t even have to state this rule given its predecessor. And yet, I have; why is that? Because some people seem to think it is alright to do whatever they want to a production database, since it is, well, in production.The logic, as I have had it explained to me is as follows: “Once [the database] is in production, what do you care what happens to it? You don’t have to monitor changes to it; you just have to make sure it gets backed up properly, so that if a customer breaks something we can roll back.” This is a serious problem.
Let's use a little example: A customer calls in and requests some changes. Bob, being the genius that he is, realizes that he has access to the production database of the customer in question (for some reason known only to IT) and decides that in his infinite wisdom he will make the changes to the customer's database and drop a line (something akin to: “FYI: I made some changes to database xyz”) to the DBA afterwards.

There is nothing more frightening to a DBA to see than an email from someone who thinks they know what they are doing than that. In the aforementioned example, Bob has not actually performed the changes to the correct database. He has in fact changed another customer's database. This other customer is now quite upset that something has gone horribly awry and demands a fix. The DBA now has a choice to make:

<ol type="A">
  • Talk with Bob and try to pin down exactly what he did. Sadly, this choice often leads to the following conversation:
    DBA: You made changes to the wrong database. I need to know EXACTLY what you did.
    Bob: Oh you know, just this and that. Added some columns, dropped some columns that sort of thing. Don't you have monitoring software to record stuff like that?
    DBA: Let me get this straight, you made schema changes and didn't bother to save the script?
    Bob: What's a script?
  • Restore from a previous backup. This of course causes the customer to lose out on any data from after the backup was performed, but is much faster to do. If it is an important customer, then the DBA can script some of the data over from whichever tables they were using, however this will take time. This of course results in angry customers. Plus, the first customer still hasn't had his changes made.
    1. Script everything. It doesn’t matter if you are ignoring 2 and 3, or if you have been given explicit permission from the DBA. If you make modifications to a database, especially for schema, script it all out. Your DBA will thank you for it. It makes things so much easier.

    Lastly, just for you IT folks out there, the number one way you can make your DBA’s life easier right this very second.

    Revoke access to the databases from EVERYONE, but the DBA.

    Yeah, I know, not going to happen, but a DBA can dream, right?