Simple yet vital steps to remember while using log shipping for DR …

Log shipping is one of the oldest DR solutions available with in SQL Server. While log shipping technology ensures that secondary database can be recovered if primary fails anytime with minimum efforts, this technology does not do anything to sync server level objects automatically.

I often found (with in the organization where change control and DR processes are not mature enough) a serious symptom develops with time. People often implement changes in primary server and miss to apply the changes in secondary instance or to update the change in DR manual (This can be deemed a change implementation failure). Also if the organization does not test the DR environment periodically (as a result of no or poor DR policy), primary and secondary instance’s server level object can be widely out of sync. This lethal combination of change implementation failure and poor/no DR policy can be significantly damaging to fail the “recovery procedure” in case of a real disaster.

So from my experience, I do always recommend remembering below steps if you are responsible for an environment and use log shipping:

1. Make sure that all the server level changes (eg. Logins, DTS/SSIS, Jobs etc) in primary instance are immediately implemented in secondary instance and all the scripts/changes are properly document in DR manual. Do not consider a “Change Implementation Task” as complete until you are absolutely sure that your secondary instance is in complete sync with primary instance and documents are updated.

2. Test your DR strategy at least once in a quarter. This is vital to identify and then rectify any miss which can be a major obstacle in a successful disaster recovery. Also performing a through DR test once in a year is highly recommended.

3. Audit your DR manual (document) at least once in every quarter to ensure it can act as “Bible” if you really have to execute “Recovery Steps” in a disaster.

Lastly following these steps are actually very important for any other similer DR solutions (like for mirroring) too.