SQL Server Disaster Recovery Test

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.


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.

Differential Backup Script

Log Backup Script

Once the above step is completed, we normally follow a
practice of renaming the DR database – in this case ABC_DR as ABC situated on the
Secondary Server by performing the following tasks:

  1. Execute the below T-SQL query against the master database on the
    Secondary Server:

    alter database ABC_DR
    set single_user with rollback immediate

    This will set the database named ABC_DR to Single
    User mode so that all the existing sessions in it are killed and we can rename
    the database as shown in Step 2 below.

  2. EXEC sp_renamedb 'ABC_DR', 'ABC'
  3. Ensure that the database named ABC on
    the DR server will come ONLINE and multiple users will be able to access it :

  4. Ensure that all the SQL Server Logins are mapped appropriately to the database
    named ABC on the DR database. Also ensure that you use a separate login for both
    the Production and the DR databases to ensure that there is no security threat.

After the logins are mapped on the DR instance we then
need to perform a Reindexing and Update Statistics operation against the
database named ABC on the DR instance to improve its performance by executing
the below two T-SQL scripts against it.

ReIndexing Script

Differential Backup Script

Please let us know in the comments if you have any feedback or suggestions, alternatively you can contact me at singhsatnam84@yahoo.com.


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |