Log Shipping Role Reversal Process

Log Shipping :

Log Shipping is one of the four techniques we have for High availability(with each one having its own Pros and Cons).

Out of all the options, IMHO, Log Shipping is easier to configure and monitor because it is based on Simply Backups and Restore concept.

In a nutshell, you basically will take Log backup(s) on the Primary database and copy them to the destination server where the secondary server can access and apply them on the secondary database.

We can configure more than one Secondary database using Log Shipping. This cannot be done with database mirroring.
Another major score is both the servers do not need to communicate directly. (I.e. the secondary server could really be a remote server).
Log shipping needs database to be either in full or bulk recovery mode.
You can also restore the logs in standby mode which gives the users the ability to access the secondary database.
In this post, I would like to discuss on the steps to be taken in order to reverse the roles:

1. Disable the Log Shipping Jobs on the Primary and Secondary Server.
2. Make sure all the log backups taken on the primary were copied on to the Network Folder and restored on to the Secondary. If not, apply them.
3. Backup the Tail log on the primary with no recovery. This will set the primary database in ‘restoring mode’.
4. Apply the tail log backup on the primary with recovery. This brings the secondary database online.
—To reverse the role—–
5. Configure the New Secondary database as Primary Log Shipping Database just like Initial Log shipping configuration.
6. Instead of the creating the secondary database from scratch, select ‘No. The secondary database is initialized’ while creating the secondary database and choose the secondary database from the drop down.
7. Choose the restoring as per your need either restoring or stand by. I prefer standby mode as users can connect to it but it purely depends on the purpose of doing Log Shipping , i.e. is it just for high availability or for both(HA + Offload the query distribution like reporting).