Disaster Recovery plan is our business continuity plan. Disaster recovery is a process that we can use to help recover information systems and data, if a disaster occurs. The planning includes the selection of a strategy to help recover valuable data. The selection of the appropriate disaster recovery strategy depends on your business requirements. Suppose there is a database system storing financial application data, an organization whole business is running on some applications and data is stored in database. The database system machine is placed in the same organization, if tomorrow database machine crashed it can create issues like loss of all data, business records. Will the organization be able to continue business? So if there will be solid Disaster Recovery plan for the organization data then the organization's business will not stop and it can continue. In disaster recovery what, backup mechanism is configured on some other machine to keep the same piece of information what on current database machine. If the current database machine crashes, data can be recovered from backup machine. Key Questions to Customer: The first step in testing the disaster recovery plan is to ask customer some poignant questions and respond in simple terms, maybe even 1 word answers.

• SLA - What is your Service Level Agreement (SLA) with the business with customers?
• Cost - What is the tangible cost of downtime for your users, application, business, etc?
• Prevention - What type of disaster are we trying to prevent?
• Recovery - What type of disaster are we trying to recover from?
• Time - How much time and money do have to spend on building, testing and maintaining the disaster recovery plan on a daily, weekly, monthly or quarterly basis?
• Responsibility - What are your disaster recovery responsibilities and why do you have those responsibilities?
• Plan –Documented disaster recovery plan?
• Testing - Have you tested the disaster recovery plan?

The selection of the appropriate disaster recovery strategy depends on customer business requirements. Disaster recovery in SQL Server Microsoft SQL Server database system provides multiple ways to configure disaster recovery.

• Failover clustering
• Database Mirroring
• Database Replication
• Log shipping
• Timely database Backup strategies

Failover clustering Microsoft SQL Server failover clustering is designed to failover automatically if a hardware failure or a software failure occurs. You can use SQL Server failover clustering to create a failover cluster for a single instance of SQL Server 2000 or for multiple instances of SQL Server 2000. Failover clustering allows a database system to automatically switch the processing of an instance of SQL Server from a failed server to a working server. Therefore, failover clustering is helpful if an operating system failure occurs or if you perform a planned upgrade of the database system resources. Also, failover clustering increases server availability with no downtime. Because failover clustering is designed for high server availability with almost no server downtime, the clustered nodes should be geographically close to each other. Failover clustering may not be useful if a disk array failure occurs. Advantage

• You have high server availability. Failover clustering automatically occurs if the primary server fails.

Disadvantages

• You incur a greater expense. The maintenance of two servers is two times the cost of maintaining a single server. Because you have to maintain two servers at the same time, it is more expensive to install and maintain clustered nodes.
• Servers should be in the same location. If the branches of the organization are across the globe and the Active/Active clusters must be implemented in the branches, the networking and the storage infrastructure that you have to use is very different from a standard quorum device server cluster. Therefore, although it is possible, it is best not to use geographically distant servers.
• You have no protection against a disk array failure.
• Failover clustering does not allow you to create failover clusters at the database level or at the database object level, such as the table level.

Database mirroring Database mirroring is a primarily software solution for increasing database availability. We can only implement mirroring on a per-database basis. Mirroring can only works with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level. Advantages

• Database mirroring increases data protection.
• Database mirroring increases availability of a database.
• Database mirroring improves the availability of the production database during upgrades.

Disadvantages 

• The mirror database should be identical to the principal database. For example, all objects, logins, and permissions should be identical.
• Database mirroring involves the transfer of information from one computer to another computer over a network. Therefore, the security of the information that SQL Server transfers is very important.

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 identical copies of the data. Advantages

• Read performance is improved because you can spread activity across all nodes.
• Aggregate update performances, inserts performance, and delete performance for the topology resembles the performance of a single node because all changes are propagated to all nodes.

Disadvantages

• Peer-to-peer replication is available only in SQL Server 2005 and 2008 Enterprise Edition.
• All participating databases must contain identical schemas and data.
• We recommend that each node use its own distribution database. This configuration eliminates the potential for SQL Server 2005 to have a single point of failure.
• We cannot include tables and other objects in multiple peer-to-peer publications within a single publication database.
• We must have a publication enabled for peer-to-peer replication before you create any subscriptions.
• We must initialize subscriptions by using a backup or by setting the value of the subscription synchronization type to replication support only.
• Peer-to-peer transactional replication does not provide conflict detection or conflict resolution.
• We recommend that you do not use identity columns.

Log shipping Log shipping uses a standby server that is not used during regular operations. A standby server is useful to help recover data if a disaster occurs. We can only use log shipping at the database level. We cannot use it at the instance level. When a standby server is restoring transaction logs, the database is in exclusive mode and it is unusable. For Applications such as decision support servers that require continuous processing on a database server, log shipping is not an appropriate option. The latency on the standby server is based on how frequently the transaction log backups are taken at the primary server and then applied at the standby server. If the primary server fails, you may lose the changes that were made by the transactions that occurred after your most recent transaction log backup. For example, if transaction log backups are taken every 10 minutes, transactions during the most recent 10 minutes may be lost. This does not necessarily mean that the data updates that are made to the primary server during the latency period will be lost. Typically, new updates in the primary transaction log can be recovered and applied at the warm standby server with only a small delay in switching from the primary server to the standby server. The main purpose of log shipping is to maintain a warm standby server. If maintaining a warm standby server is your main objective, log shipping is likely to be more appropriate than the other solutions that this article discusses. Advantages and disadvantages of using log shipping Advantages

• We can recover all database activities. The recovery includes any objects that were created such as tables and views. It also includes security changes such as the new users who were created and any permission changes.
• We can restore the database faster. The restoration of the database and the transaction log is based on low-level page formats. Therefore, log shipping speeds up the restoration process and results in the fast recovery of data.

Disadvantages

• The database is unusable during the restoration process because the database is in exclusive mode on the standby server.
• There is a lack of granularity. During the restoration process, all the changes in the primary server are applied at the standby server. You cannot use log shipping to apply changes to a few tables and to reject the remaining changes.
• There is no automatic failover of applications. When the primary server fails because of a disaster, the standby server does not failover automatically. Therefore, you must explicitly redirect the applications that connect to the primary server to the standby (failover) server.

Note If your main purpose is to maintain a warm standby server, Microsoft recommends that you use log shipping. The warm standby server reflects all the transactions that occur on the primary server. However, you cannot use the standby server when the primary server is available. Timely Database Backup Strategies If our disaster recovery plan we are planning for timely databases backup then all what we have to do is:

• Take backup of all database from the database server (you can schedule a job in sql server to do take backup of all databases)
• Copy all back up files to your file server or some other directory
• Need to write schedule daily/weekly backup plan in sql server
• Write ourr daily backup / weekly back up in disk and store the disk at some safe place

Conclusion In this article we had discussed about disaster recovery and disaster recovery plan in sql server. There are many ways you can configure disaster recovery for your database, in sql server database we can use Failover Clustering Database Replication, Timely Backup Strategies, Log Shipping, and Database Mirroring.