•  
  • DR/HA
  • SQL server Disaster Recovery plan

SQL server Disaster Recovery plan

SQL server Disaster Recovery Options

This month’s TSQL Tuesday party is being hosted by Allen Kinsel (Blog | Twitter) and the topic this month is disaster recovery. It’s a DBA Topic/Job.


The disaster recovery is really “It depends”.

What is disaster recovery?

Disaster recovery is called DR. Hmm…

Disaster recovery is the best option for the business to minimize their data loss and downtime. The SQL server has a number of native options. But, as I already told everything is depends upon your recovery time objective RTO and recovery point objective RPO.

That is how much data can you afford to lose? How long can you wait? How much money do you ready to spend?

If you have answers for these questions then, you can choose your best DR plan.

Who knows what will happen tomorrow?

I have seen most of the company don’t have at least simple DR setup, Like keep the backup in safe DR site. The reason is they didn’t face any disaster yet.

Truncating and deleting the ‘critical tables’ also consider a big disaster. So guys keep the backup, backup and backup the database. Before going to set-up your DR plan asks the questions to DR Guru’s via online (Like twitter, SSC and MSDN) and also read the DR Guru Paul Randal’s white papers it will help.

I have recommended to watch the brentozar‘s DR video. Its amazing video and it covers the basics DR plan.

Seriously I have no idea about Geo cluster. Thanks to @sqlsoldier and @SQLpoolboy  for their twitter reply about the Geo cluster method.

For understandable I will classify the disaster recovery (DR) into three types. I am not going in depth just I will cover the basics of high availability HA and disaster recovery DR.

1. Data center disaster

2. Server(Host)/Drive (Except shared drive) disaster

3. Database/Drive disaster

Backup/Restore

Backup restore is common method its simplest (less expensive) and good option for disaster recovery but it’s depends upon your backup strategy and recovery model. The backup restores will work for all the above three DR methods. The DBA job is keeping the data as much as safe and application availability (Zero down time). That is test and keep your database backups safely try to automate the backup test and move them automatically to the DR site.

The backup restore does not good choose for big databases (TB) in case of any disaster, because the restoration takes more time to bring the database online. However you can speed up the backup restoration using the following SQL native features.

Clustering

Clustering is a combination of one or more servers it will automatically allow one physical server to take over the tasks of another physical server that has failed. Its not a real disaster recovery solution because if the shared drive unavailable we cannot bring the database to online.

Clustering is best option it provides a minimum downtime (like 5 minutes) and data loss in case any data center (Geo) or server failure.

  • Clustering needs extra hardware/server and it’s more expensive.

Database mirroring

Database mirroring introduced in 2005 onwards. Database Mirroring maintain an exact copy of a database on a different server. It has automatic fail over option and mainly helps to increase the database availability too.

  • Database mirroring only works FULL recovery model.
  • This needs two instances.
  • Mirror database always in restoring state.

For more details read Robert L. Davis’s Pro SQL Server 2008 Mirroring book.

Log shipping

Log shipping is the process of automating the full database backup and transaction log on a production server and then automatically restores them on to the secondary (standby) server.

  • Log shipping will work either Full or Bulk logged recovery model.
  • You can also configure log shipping in the single SQL instance.
  • The Stand by database can be either restoring or read only (standby).
  • The manual fail over is required to bring the database online.
  • Some data can be lost (15 minutes).

Peer-to-Peer Transactional Replication

Peer-to-peer transactional replication is designed for applications that might read or might modify the data in any database that participates in replication. Additionally, if any servers that host the databases are unavailable, you can modify the application to route traffic to the remaining servers. The remaining servers contain same copies of the data.

Note: Database mirroring, logs shipping and replication also treated as DR if it’s located in different data center. We can use both the HA and DR combination to set up the disaster recovery.

Again everything is “It Depends”. Read Paul Randal’s white papers before going to configure the DR.

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Share this
28 Comments - Leave a comment
  1. vinoth says:

    Geo cluster is new to me too. Glad to see your contribution.

  2. Muthukkumaran says:

    Thanks @vino my dear.

  3. Anonymous says:

    Hi,
    Good one. Additionally,Backup compression only available 2008 enterprise edition.

    mike

  4. IT Disaster Recovery says:

    Our Data Replication Services involves replicating your production data, real-time or scheduled over night over a secure VPN link to our Data Centre, either in London or outer London. http://www.newtonit.co.uk

  5. Muthukkumaran says:

    thanks mike.

  6. Muthukkumaran says:

    @IT disaster recovery

    Thanks for the information. I am going to look your link sometime.

    Thanks.

  7. Anonymous says:

    Hi muthukkumaran,

    You said L.S can be configure single instance.

    (You can also configure log shipping in single SQL instance)

    Is it true?

  8. Muthukkumaran says:

    Mike sql 2008 Developer edition also support compression.

    Thanks

  9. Muthukkumaran says:

    Yeah Its possible you can configure a log shipping single instance alone.

  10. Muthukkumaran says:

    Find the T-SQL Tuesday #19 disaster recovery summary here–>
    It has great posts.Have a look

  11. PeterPatrickGo says:

    yeah, good method of disaster recovery could indeed save much money for the company.

  12. Muthukkumaran says:

    I'm glad you liked it.

    Thanks Peter.

    Please have a look to my previous comment i've added the whole D/R summary list. Its really good collection from the worldwide DBA's.

  13. Kim says:

    Wonderful blog. it's really good collection from the worldwide DBA's. good work . keep it up..

    IT Disaster Recovery

  14. Muthukkumaran says:

    Thanks kim.Glad you liked it.But,

    The real hero is Adam machanic.He only got an idea to write anyone about a particular topic.

  15. records management company says:

    Thanks for the blog, it's great! I don't think that everyone realizes just how much of a nightmare it is to lose your data and have no backup. You guys are my heroes!!!

  16. Muthukkumaran says:

    Thanks records management company.

    I'm glad you liked it.

  17. VadoShoothe says:

    Getting study this I assumed it absolutely was really educational. I recognize you taking enough time and energy to place this informative article with each other. I the moment yet again discover myself paying strategy to considerably time equally looking at and commenting. But so what, it absolutely was even now worthwhile!

  18. Thanks Muthukkumaran kaliyamoorthy for the Awesome share of information, it was very helpful to me. I really love the manner in which you have framed your issues regarding this situation, keep up the awesome work. All the Best. John

  19. Document Management says:

    Whats Happening i’m new to this, I stumbled upon this I’ve discovered It absolutely helpful and it has aided me out loads. I hope to give a contribution & aid different users like its aided me. Good job.

  20. Thanks so much for the blog.Really looking forward to read more. Awesome.

  21. Brad says:

    For those who could e-mail me with just a few recommendations on simply the way you made your blog look this wonderful, I would be grateful.

  22. Matthew C. Kriner says:

    your blog design is wonderfull, it makes me want to hang around longer, you obviously know what you are doing, cheers

  23. Anandanarayanan says:

    Hi Muthu…

    This is good information for database basic HA & DR setup..
    I want to contact you..Mobile no.Pl

  24. this post is quoted by valentine gifts says:

    personalized gifts for men gifts for her baby gifts…

    You made some first rate points there. I regarded on the internet for the difficulty and located most individuals will go along with together with your website….

  25. this post is quoted by Google says:

    Google…

    the time to read or pay a visit to the material or sites we have linked to beneath the…

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">


Welcome , today is Tuesday, May 23, 2017