The Minimalist Guide to Database Administration

07 Feb The Minimalist Guide to Database Administration

Your office should look like this.

Every great DBA I know is both driven AND lazy. They are driven to be as efficient as possible, so they can do less work. It’s a wonderful pair of personality traits to have as a DBA.

The unfortunate part is that the more driven they are, the more efficient and lazier they get, which often results in their boss looking at them and saying “why am I paying you to just sit around?”. This results in many DBAs being dragged into administering other applications (like Sharepoint) because, hey, they run on databases too, right?  With you were sitting there looking like you weren’t doing anything it just made sense to give you extra work.

I have listed seven items below that I believe are universal for anyone that has the letters “DBA” in their job description. Some DBAs need to be a part-time SAN admin, or VM admin, or know all about security, or Active Directory, or .NET. It differs from one shop to another. Whether it is day one or one hundred in your career as a DBA you need a guide that helps you stay focused on your core duties. That’s what I have created here. It is the Minimalist Guide to Database Administration, and my goal is to make sure that no matter what your level of efficiency and laziness you are able to focus on the bare essentials (the minimum) necessary to be a rockstar DBA.

1. Backups

It’s simple, really: get them done.

There really isn’t a lot to say here, but as a DBA your number one job is to ensure that you can recover data. You cannot recover data without having configured your database backups to happen. And while it is surprisingly easy to configure database backups and yet a shocking number of people never bother. You can use custom scripts or maintenance plans to get the job done, just make sure you are getting them done. Grant Fritchey has an excellent article over at Simple-Talk that I would recommend you read.

In order to keep with the minimalist theme I am going to tell you to use backup compression (depends on your version). You can enable this at the instance level or as an option included within the backup command. This will help keep disk space to a minimum. Also, you need to talk with your business users to find out how often you need to do full backups, differential, and transaction log backups as well. My default choices were weekly full backups, nightly differential backups, and hourly transaction log backups. This was a good place to start and I could adjust as needed.

2. Only Be Alerted When Necessary

I’ve never been one to want to be alerted just for the sake of being alerted. It makes no sense to me to be paged at 11PM just to know that something successfully happened. I expect success, and would prefer to only be told about when something has failed. As far as failures go, I only want to be told about them when it is something for me to fix.

When I only had a handful of servers under my care I had alerts for all sorts of things. I was even alerted to when a session had been blocked for more than five minutes. Sounds good, right? Sure, except then I would call the end user to inform them of the situation and ask them what they wanted me to do and I would usually get told “I dunno”. Well, OK then. If you don’t want, or know, what action I should take when you have such blocking, then why would I continue to want to alert myself about this activity?

I wouldn’t. Neither should you. Only be alerted to the things you can take action upon immediately. Knowing your server CPU utilization is at 80% might sound like a nice thing, but tell me exactly what actions do you plan to take as a result? Now…tell me what actions you will take if you had over a dozen servers at the same time reporting more than 80% CPU utilization?

At some point your reach is greater than all your available hands. Split out your alerts into those that are informational versus those that are actionable. If they are not actionable then at some point you will simply turn a deaf ear to all alerts. That is not going to be good for anyone.

3. Automate

If you haven’t been automating any of your processes, get started. As you get more and more servers shoved your way you will find that automation will be your BFF. In my case I dove into Systems Center Operations Manager (OpsMgr) as a way to automate a lot of the things I needed done. Once OpsMgr recognized that an instance of SQL was running on a server it would simply execute all the scripts I needed done for me. I could use OpsMgr for just about anything I dreamed up, including having it alert me if I had not taken a backup recently.

There are many other ways to automate your systems, including the use of Powershell, Central Management Servers and policy-based management, and even old-school SQLCMD. Use whatever works best for you, but use something and get your stuff automated. Don’t wait until you have too many servers to manage before you start automating your processes. If you have more than one server under your care you should be automating things right now.

4. A DBA Has Got to Have Standards

You want to have a standard build and configuration for your database servers. It makes troubleshooting much easier when you know that each server is essentially similar to all the others. The fewer of those servers classified as “one offs”, the better it is for you and ultimately for your end users that need you to solve problems quickly.

Some of the things you want to have standard would include (but not limited to) the following:

  • data and logs on separate disks
  • authentication mode (pick one and use it everywhere, consistency is key)
  • version level/patching
  • naming standards
Those are just a few examples to get started. Your shop may focus in some particular area and you will probably want to have standards specific to that need. Whatever the standards you want, just get them written down and agreed upon and then make sure they are adhered to at all times. This is where automation comes in to help as well, you can build a set of scripts/policies to quickly check to see if any particular instance is not adhering to a defined standard.

5. Only Install What You Need

Another way to reduce your surface area of administration and troubleshooting is to only install the minimum services needed for your database servers. This has an added benefit of also

Get used to these screens.

reducing the surface area for an attacker, something your security folks would appreciate. A great way to get this done for SQL 2012 is to utilize Server Core.

What Server Core does for you is it allows for you to have a “low-maintenance” environment. What that essentially means is that your server isn’t running any unnecessary software, including a GUI for you to use. That’s right, when you go Core you end up doing everything from a command line. It’s as if Microsoft looked at everyone and said “You like the Linux? Fine, here you go, enjoy!”

As a DBA (for any platform), this makes perfect sense for you as it reduces the number of things you need to be an expert in and examine while troubleshooting. Don’t install unnecessary services and you won’t have to worry about them interfering with your database engine.

6. Restores

I told you that your number one job is to recover data, right? And you are doing your backups, right? OK, quick question for you: how do you know you can restore those backups? Is there a way for you to verify that your backup can be restored?

Yes, there is, it is called a restore.

That’s right. The only way to truly know if your backup can be restored is to actually perform a restore. This may not be practical for you, doing restores of thousands of databases each day in order to verify that they are valid. That’s why I wrote a post on how to get the job done in an efficient, minimalist style. Turns out you may not need to do all of them after all, but you certainly do need to be testing some of them.

7. Moment of zen

Any time you hear about “minimalist” it is usually accompanied with the word “zen”. There’s something to be said for that with regards to being a DBA.

Don’t panic.

That’s right, stay calm. Breathe deep. You don’t know everything. You will constantly be learning on the job. Never panic, no matter what. When everyone about you is losing their composure they will need someone to be a steady influence. Let that be you.

In your career as a DBA you are going to be subjected to a lot of stress by others. Don’t let their negative energy become your burden.

It will serve you well to remember that life is an experience, and there is much for everyone to learn.

Also, stabbing people with a paper clip in the neck just annoys them more.

Those are my seven bare essentials for any DBA, my “minimalist” guide. What would be in your guide? What tips and tricks have you learned to help yourself scale as a DBA, to be able to take on more and more responsibilities as you get more and more efficient? Start writing down your own guide and compare your list to mine. Did I capture the essentials? Did I include something that shouldn’t be there? Leave a comment.

3 Pingbacks/Trackbacks

  • dev_b

    “Only be alerted to the things you can take action upon immediately”
    I think, instead of SMS alerts, that should be an email alert, so you can find the root cause for that alert next day assuming you have all kind monitoring to trace it back.

  • Pingback: Something for the Weekend – SQL Server Links 10/02/12()

  • J_r_barnett

    I would add as an essential “Understand the business” – the databases are used by front end applications that support the organisation.  Whether they are functions that will be found in all organisations such as finance or HR/payroll, or sector specific such as banking, insurance, student management in colleges etc. A high level overview of the business environment that your organisation operates in is essential to understanding the real world usage of the software.

  • John Romeis

    This appeals to me, not just for DBAs…

  • Pingback: SQLQuill – Link Round Up – February 2012 Edition « SQL Feather and Quill()

  • Pingback: Seven Tips For Solving the Accidental DBA Problem - SQLRockstar - Thomas LaRock()