Wednesday, 25 January 2012

SQL Disaster Recovery Considerations

In this post I hope to  provide better insight  into what you need to consider when planning your DR strategy.
I often find that organizations never have a sufficient DR plan especially around their databases. I have seen organizations take a week or more to restore their primary database due to insufficient planning. What made it worse was that their SLA stated that their vendor could restore any database within a 3 month window.

This annoyed the crap out of me to put it mildly. Luckily it wasn't a SQL database but I felt bad for the support guys . I recently presented at SQLSaturday88 in Cape Town ,South Africa on the importance of planning your DR strategy from a DBA point of view . This is what I recommend :

  • Define your systems e.g. Tier1,Tier2 ,Tier3. The reason for this is so that you know which databases you will restore first. 
  • Decide early on which method you going to use for your restores , are you going to restore directly from tape,disk or will you be using third party software
  • Consider if you need point-in-time recovery. Most hardware vendors have replication software that allows you to copy the mdf and ldf files to a point in time
Restoring Directly from Tape
  • This part you will require your backup administrators help,the reason for this is so he can ensure that the backups are loaded in the order you want to restore them.
  • Depending on which backup software you using e.g. Netbackup,Backup Exec,Tivoli TSM, you will be using their respective UI's,remember the same SQL restore principles apply
Restoring Using 3rd Party Software
  • I love these,3rd party replication software,they rock and cut your restore times down to minutes instead of hours (Last time I used it to restore a system that normally  takes 8 hours to restore,with the RM software it took 15 minutes) e.g EMC replication manager,HP DRM
  • Consider if you need point in time recovery.Most Hardware vendors have replication software that allows you to copy the MDF and ldf files to a point in time.
The Power of Documentation
  • Keep your database documentation updated.Your documentation can help you recover your database structures in the event that you have no backups
  • Once again make your life easy 3rd party software, RED-GATE's SQLDOC or PragmaticWorks BI Documenter,The database edition is free.
  • They usually document all DB schemas,objects ,permissions and include samples of your data
  • Do NOT under estimate the power of your documentation in worse case scenario when you have no backups,no tapes this will be your only tool
  • Update it at least once a week depending on your database structure changes 
When Crap Hits the fan and you have to restore the master database

Restoring Master

  • This should be your last resort and should only be done when you 
  • ONLY if you don't know have any idea of which databases you need to restore.
  • To do this you need to put SQL in single user mode to do this go start --> programs-->Microsoft SQL Server 2008 R2-->Configuration tools-->SQL Server Configuration Manager

  •          Once in the SQL config manager right click on properties

    Now click on the advanced tab and go down to start-up parameters , go to the end of the line and add a semicolon (;) , then add -m

    • Click on apply and then ok and in SQL Config manager restart the server

    • Now open the command line and type in the following,Before you do this make sure that the SQL Agent on the server is stopped.
    • Once the database is restored,go back into SQL Config manager  ,right click,properties ,Advanced,start-up parameters and remove -m,click apply then ok and restart the SQL service
    • Open SQL Management studio and you should see the following
      before you restore you user databases have a look under security, logins,you will see that all your logins are there(saves you from having to scripting your logins)


      • You can now restore your model(if you have customized it) and MSDB database
      • After restoring the MSDB database(This will restore all your jobs)

      • With restore methods 1&2 you will need to script out you logins,make your life easy,There is a tool call SQLPermissions from Idera Software and run the script(s) after the restores,best of all it's free
      • If you have any questions  feel free to ask or even better send me feedback  Email Jody or on Twitter

      1 comment: