This blog demonstrates the detailed steps needed during the DR testing with reverse Log shipping method.
One common task for every DBA is to make sure that all mission-critical SQL Server instances and the databases within them are available around the clock to keep the business up and running by minimizing or no business disruptions. When your primary location experiences a geographical disaster like an earthquake, flood, or fire the business must be prepared by recovering or resuming the services from a geographically different location. In SQL Server databases, Log shipping is one of the oldest methods of providing disaster recovery which is implemented in many organizations where other options may be challenging due to environment, administrative skills, or budget. Why do we need a DR Drill? - In order to avoid business downtime we always have to maintain a proper DR mechanism and it’s mandatory to conduct a DR test to verify the application connectivity and the data synchronization state once we restore or recover the database on the DR site. It is a best practice to conduct a DR drill every six months, although it entirely depends on the client’s requirement.
I have already configured the Log shipping and below are the configuration details :
Please follow the checklist for a smooth DR exercise and get the steps prepared for the failover and failback.
Take the confirmation from customer to stop their applications from connecting to the LS primary server.
Once the customer confirms for go-head, Run Manually LS Backup job on Primary Server and LS Copy, LS Restore on DR server.
Upon completion of the above jobs, Check the LS report and make sure Last backup, Last copy, and last restore are having the same backup file name.
On confirming the backup files are same on step#3, Disable all the LS Jobs mentioned on the Primary and DR server.
Take the Tail log backup on the primary server and this will leave the databases into Restoring state on the primary server.
Copy the Tail log backups from primary to DR and Restore with recovery on the DR server, this will bring the database online state.
From DR server, now configure the Log shipping and during the configuration on secondary database settings, select ‘No. The secondary database is initialized’ Instead of creating the secondary database from scratch and choose the secondary database from the drop-down.
Copy any application related Jobs, logins from the primary server to DR and fix any orphan users.
Confirm to the customer that Failover has been completed and they are good for application testing.
NOTE: - Clear the tail log backups on the folder which were copied during the failover.
NOTE: Before copying clear all the tail log backups on the local path of the Primary server which were taken during the failover.
Well, we can see the status of the Log shipping is Good on both Primary and secondary.
I hope you find this discussion useful when you are planning the DR drill with Log shipping.
Use the Feedback tab to make any comments or ask questions. You can also start a conversation with us.