Every DBA in their career should have done DR drill at some point, in fact it should be done in a regular bases like bi-yearly or yearly. To better plan for DR drill for SQL server, I will start a series on how those can be done on different SQL technology, which hopes to help you for your own DR dill.

The main purpose of DR drill is to ensure that if production environment is down, the servers in the DR site can pick up the workload and have minimal impact to the business (with minimal or no data lost). There are number of ways to set this up in SQL server, they are:

1. Log Shipping
2. Database mirroring
3. High availability groups (available in SQL 2012 on-wards)
4. Geo-Clustering - MS Cluster
5. 3rd Party Cluster solution

I will go through each one in detail in later post and the method to best help with DBAs to conduct the drill.

From DBA perspective, we will need to ensure that the database can be started in the case of disaster. In the drill, you normally will be asked to bring up the database instances from the DR servers, allow users access it from application(s), perform their daily functions and ensure everything functions normally as their were in production. Once the testing is done and confirmed by the business users, DBA will need to discard all the changes made to the DR databases and resume the data sync from production.

As you can imagine, there are multiple methods to conduct the drill, one is isolate the network in production site, thus ensure that all applications can only connects to the DR servers. Or manually power down all hardware in production site, which to simulate total power down of primary site (the disaster) and many others suits to your company needs. Which method to choice from will be depends on the company and management, each will have their own issues, but we wouldn't discuss them here.

Let move on to the first method - Log shipping in the next post 🙂