Migrating Log Shipping Database

Log Shipping is one of the HADR solution available in SQL Server and it works in a simple way – take log backup on the primary database and restore it on the secondary database. There can be more than one secondary databases. All this can be scheduled using SQL server agent jobs and even if the feature is not available is directly, it should be fairly easy to set one up in a traditional way.

I recently had to move one of our log shipping primary database to a different database server. Below are the steps, I followed the below steps to eliminate the need to reconfigure the log shipping from scratch.

Scenario :
Database – TESTLOGSHIP
Database Server : Mercury – Primary Log shipping ;
Database Server : Venus – Secondary Log shipping
There is no witness server configured.

Now, I need to migrate ‘TestLogship’ database to new database server ‘EARTH’ and configure it as log shipping primary.

1.Take full backup on the mercury server for ‘testlogship ‘ database.

2.Restore the full backup on the Earth database server in recovering mode.

3.Run select * from msdb.dbo.restorehistory where destination_database_name=’testlogship’ on the venus database server, to find which was the last log backup applied.

4.Make sure none of the log shipping jobs(backup\copy\restore) are running and disable them.

5.If there are any log backups taken but not applied on to Venus database server – apply them. This can be tracked by checking backupset table on Mercury and comparing it with restorehistory table on Venus.

6.Check if there are any log backups taken after the full backup(step1) was taken and restore them on database server Earth in ‘recovering’ mode.

7. Make sure both the Earth and Venus, database servers have same log backups applied. You can verify this by checking ‘select * from msdb.dbo.restorehistory where destination_database_name=’testlogship”.

8.Restore the database on earth with recovery.

9. Right click on the database ‘testlogship’ on the EARTH server and –tasks — ship transaction logs and configure log shipping, like you normal would but choose ‘secondary database is initialized option’ and everything else would obvious, if you have configured log shipping before.

NOTE: One easy way to do this is instead of restoring all the log backups, you can stop the log shipping jobs and take differential backup on the primary ‘Mercury’ and restore the differential backups on both ‘Venus and ‘Earth’ and then continue from step 8 above.

Advertisement

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).