Software QA FYI - SQAFYI

SQL Server Disaster Recovery Test

By: satnam singh

This article describes a simple procedure we implemented to ensure that the data on our the database on the Disaster Recovery server is consistent with the production server. Our DR Server is a separate server which is located in a separate data centre. The DR server receives data from the production database server via Transactional Log Shipping. If the database on the Production Server is named as ABC; then the DR Copy for this on the DR Server will be named ABC_DR which is always in NORECOVERY mode. Note that in Transactional Log Shipping the DR copy is always kept in NORECOVERY mode so that additional Transactional Log Backups can be applied over it. Also the Production Server as well as the DR Server are both in the same domain.

On the DR Server, I normally use the following RAID Configurations for SAN drives:

* Since tempdb is a very High Read/Write intensive database, the RAID level to be used is 10.
* Performance is not a primary concern for a backup drive and therefore I would recommend RAID 5 for that.
* Since the performance of the database is dependent upon the speed of the Transactional Log file, I would recommend RAID 10.
* For Data drives, RAID 5 is a good candidate.

To ensure that the Transactional Log Shipping is working, I scheduled a T-SQL script to run using a SQL Server Agent Job on the Production Database Server which will alert the DBA team if the Transactional Log Shipping goes out of sync. Please refer to the below article for more details on this.

Prior to running the test, logon to the Production instance and open the Transactional Log Shipping monitor and examine if the status is ‘Good’.

SQL Server Transactional Log Shipping has 3 types of Jobs :
* Backup Job – Hosted on Production Instance. * Copy Job – Hosted on DR Instance.
* Restore Job – Hosted on the DR instance.
Manually execute each of the 3 jobs in a sequence to ensure that the DR Copy of the database on the DR server has Point in Time data in it.

Once all the 3 jobs are executed in a sequential order, the DR server should have up-to-date data. Next you will need to break the Transactional Log Shipping.

On the Primary Server (i.e. the Production Server) right-click the database named ABC (i.e the db whose Log Shipping we need to break), then go to Properties and select Transactional Log Shipping and uncheck the check box as shown below.

Next, connect to the master database on the DR server and execute the below T-SQL to bring the database named ABC_DR online.

RESTORE DATABASE ABC_DR WITH RECOVERY
Once Log Shipping is broken, we first need to create a T-SQL job for taking a Differential and Transactional Log Backup along with a manual Full Backup of the main ABC database.

A differential backup will contain all the changes which have happened since the last Full Backup. I typically follow the practice of scheduling this every 3 hours on the Primary Server, the main advantage of having a Differential Backup in place is that it allows a faster recovery of databases in case of a disaster.

The below two scripts schedule Differential and Transactional Log Backups.

Full article...


Other Resource

... to read more articles, visit http://sqa.fyicenter.com/art/

SQL Server Disaster Recovery Test