Disaster recovery plan

A SQL Server disaster recovery plan (DRP) is a process to have SQL Server up and running, and to overcome data loss after a disaster. 

A good SQL Server disaster recovery plan must take into account numerous factors: sensitivity of data, data loss tolerance, required availability, etc. The plan can be based on few a solutions:

• Failover clustering
• Database mirroring
• Replication
• Log shipping
• Backup and restore

Failover clustering is a concept where a SQL Server instance is installed on the shared storage. It provides the infrastructure that supports high-availability and disaster recovery scenarios of hosted server applications. If a cluster node fails, the services that were hosted on that node can be automatically or manually transferred to another available node in a process known as failover. There is a short period of downtime while SQL Server is failing over.

Database mirroring is a solution for increasing availability of a SQL Server database. It maintains two exact copies of a single database. These copies must be on different SQL Server instances. Two databases form a relationship known as a database mirroring session. One instance acts as the principal server, while the other is in the standby mode and acts as the mirror server. Two SQL Server instances that act in mirroring environment are known as partners, the principal server is sending the active portion of a transaction log to the mirror server where all transactions are redone

There can be two types of mirror servers: hot and warm. A hot mirror server has synchronized sessions with quick failover time without data loss. A warm mirror server doesn’t have synchronized sessions and there is a possibility of data loss

This solution will be removed in future versions of SQL Server

Replication can be used as a technology for coping and distributing data from one SQL Server database to another. Consistency is achieved by synchronizing. Replication of a SQL Server database can result in benefits like: load balancing, redundancy, and offline processing. Load balancing allows spreading data to a number of SQL Servers and distributing the query load among those SQL Servers. A replication consists of two components:

• Publishers – databases that provide data. Any replication may have one or more publishers
• Subscribers – databases that receive data from publishers via replication. Data in subscribers is updated whenever data the publisher is modified

Log shipping is based on automated sending of transaction log backups from a primary SQL Server instance to one or more secondary SQL Server instances. The primary SQL Server instance is a production server, while the secondary SQL Server instance is a warm standby copy. There can be a third SQL Server instance which acts as a monitoring server. The log shipping process consists of three main operations: creating a transaction log backup on the primary SQL Server, copying the transaction log backup to one or more secondary servers, and restoring the transaction log backup on the secondary server.

The Backup and restore technique should be used as basic option for assurance. There are two major concepts involved: backing up SQL Server data and restoring SQL Server data. Backed up data is moved to a neutral off-site location and restore is tested to assure data integrity.

Recovery Models

Full recovery Model

The "Full" recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated. The way this works is that all transactions that are issued against SQL Server first get entered into the transaction log and then the data is written to the appropriate data file.  This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason.  So when the database is set to the "Full" recovery model since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table.

The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.

Here are some reasons why you may choose this recovery model:

• Data is critical and data can not be lost.
• You always need the ability to do a point-in-time recovery.
• You are using database mirroring

Type of backups you can run when the data is in the "Full" recovery model:

• Complete backups
• Differential backups
• File and/or Filegroup backups
• Partial backups
• Copy-Only backups
• Transaction log backups

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

Simple recovery model

The "Simple" recovery model does what it implies, it gives you a simple backup that can be used to replace your entire database in the event of a failure or if you have the need to restore your database to another server.  With this recovery model you have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup).  With this recovery model you are exposed to any failures since the last backup completed.  

The "Simple" recovery model is the most basic recovery model for SQL Server.  Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions.  Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed.  Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the "Full" recovery model.

Here are some reasons why you may choose this recovery model:

• Your data is not critical and can easily be recreated
• The database is only used for test or development
• Data is static and does not change
• Losing any or all transactions since the last backup is not a problem
• Data is derived and can easily be recreated

Type of backups you can run when the data is in the "Simple" recovery model:

• Complete backups
• Differential backups
• File and/or Filegroup backups
• Partial backups
• Copy-Only backups

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE

Bulk-logged recovery model

The "Bulk-logged" recovery model sort of does what it implies.  With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log. The advantage of using the "Bulk-logged" recovery model is that your transaction logs will not get that large if you are doing bulk operations and it still allows you to do point in time recovery as long as your last transaction log backup does not include a bulk operation as mentioned above.  If no bulk operations are run, this recovery model works the same as the Full recovery model.  One thing to note is that if you use this recovery model you also need to issue transaction log backups otherwise your database transaction log will continue to grow.

Here are some reasons why you may choose this recovery model:

• Data is critical, but you do not want to log large bulk operations
• Bulk operations are done at different times versus normal processing.
• You still want to be able to recover to a point in time

Type of backups you can run when the data is in the "Bulk-logged" recovery model:

• Complete backups
• Differential backups
• File and/or Filegroup backups
• Partial backups
• Copy-Only backups
• Transaction log backups

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED