Sql server Blog Forum

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

• Geo Clustering

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

• Clustering

3. Database/Drive disaster

• Database mirroring

• Log shipping

• Replication

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.

• Instant file initialization is the best option to bring the database online as quickly as possible.

• SQL server 2008 has backup compression technology. It helps to minimize the down time i.e. minimizes the backup and restoration time.

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.

• Peer-to-peer replication is available only in SQL Server 2005
  Enterprise edition.
  For more looks the Microsoft link: http://msdn.microsoft.com/en-us/library/ms151196(v=sql.90).aspx

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.