Disaster Recovery Planning

As a SQL Server DBA it is absolutely vital you regularly take backups of the databases you look after. If something goes wrong, a restore is required and there is no (valid) backup, guess who will get the blame? You really don’t want to be sitting in a meeting with fingers pointed at you.

Earlier this year I attended a SQL Skills training course (www.sqlskills.com) and one piece of advice that really stuck with me was that you should think about your restore strategy, not your backup strategy when it comes to disaster recovery planning.

I think this is a great way of approaching disaster recovery, think about it. Do you really want to be restoring hundreds of transaction log backups? How long would that take?

The best way of determining the best recovery strategy is to test restoring the backups taken and seeing if the plan you have put in place meets the RTO and RPO requirements of the system:-

RTO – Recovery Time Objective – What is the acceptable time period to bring the database online?
RPO – Recovery Point Objective – What (if any) is the acceptable data loss in the event of a disaster?

When testing the two questions any DBA should be asking are:-

What will I do if the database becomes corrupt?
What will I do if the server hosting the database fails?

You must be able to recover the database as quickly as possible, with the minimum amount of restores in the event of either the database being corrupted or the server failing. Being able to recover the database with no data loss but taking a week to do so really isn’t going to be very good.

I think there is a danger, with a demanding workload, for DBAs to implement a standard “default” recovery strategy and then not performing any testing to verify its suitability. The only time the plan will get tested is when a live database needs to be restored, a dangerous place to be.

My favourite online references

There is a wealth of information about SQL Server online. Absolutely hundreds of blogs, white papers, editorials, the list goes on and on. Trying to filter out the really useful, accurate (!) information from the not so useful (or just plain wrong) can be tricky. There are conflicting opinions out there which can be really confusing when you are researching a problem you are experiencing.

Below are just a few of the sites that have helped me in my day to day work immensely.

SQL Server Central
Contains an absolute wealth of knowledge on SQL Server. Articles, editorials, training and a question of the day. I use the forum on this site more than any others.

Microsoft SQL Server Forums
The official Microsoft SQL Server forum. Loads of people willing to help you out with any problems/queries you may have.

Brent Ozar
These are the guys who wrote sp_Blitz. I’d definitely recommend downloading this proc, it’s a great way of getting a quick overview of a new SQL instance. The site also has a good blog.

Adam Mechanic
Download sp_WhoIsActive. A really useful proc that shows information on currently executing queries.

#SQLHelp is a great way of asking quick questions regarding SQL Server

This site provides online training for a monthly fee. It’s not expensive and the information you can get out of the courses is invaluable. However, try not to end up (like me) buying a month subscription and only doing half a course!

SQL DBA With A Beard
An old colleague of mine who is an absolute powershell whizz. If you don’t know how to use powershell, you should start learning!

If you are going to learn powershell you could probably start here powershell.com but Rob’s blog has a lot of good information specifically aimed at DBAs.

SQL Server Central and Brent Ozar’s site send out weekly newsletters which I would strongly recommend that you sign up to. I have learnt so much from both these sites.

Obviously I’ve not even scratched the surface of what is available on the internet regarding SQL Server but the sites listed are an absolute must for your bookmarks.

My 5 pieces of advice for new DBAs

As my first post, I thought I’d list a few points that I would say to any new DBA. I’ll probably turn each one of these into a post on its own but for now here they are:-

Of all the tasks that you perform, making sure that there are recent, valid backups being taken is the most important. If something goes wrong, a restore is required and there is no valid backup, guess who will get the blame?

Be paranoid, as the saying goes ‘Hope for the best, plan for the worst’. You are responsible for the security, resilience and performance of the live environment(s). Carefully review anything that is going to be deployed and make sure you have a rollback plan.

There is a wealth of information on the internet regarding SQL Server from Books Online to blogs this like. When asking questions about SQL Server, the most common answer you are going to come across is ‘it depends’. For any questions that you have, collate information from multiple sources and then test, test, test, test, test (you get the idea)…

At some point along the line, you will make a mistake. Everyone does. I’m not saying this means you can start working rashly without care but even if you are the most diligent DBA in the world one day you will make a mistake. It’s how you react to it that matters, suck it up and learn from it (I know everyone says this, but what else can you do?).

We are in a profession which relies on technology that is ever evolving, meaning that even if you’ve been working with SQL Server for 20+ years, there will always be something new to learn. I’d have a look at the Microsoft approved entry level course:- http://www.microsoft.com/learning/en-us/Course.aspx?ID=40364A

As your knowledge grows you can attend more advanced courses.