Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Migrating a VLDB in SQL Server with Log Shipping


By:   |   Read Comments (5)   |   Related Tips: More > Upgrades and Migrations

Attend these FREE MSSQLTips webcasts >> click to register


Problem

You just had a great weekend. You go grab your coffee and before you take a sip a manager grabs you and says, "we need your help." We need to migrate a very large database to our new data center. We need to keep the data in sync and have a short period of downtime when we cutover.

Solution

To satisfy the requirements of making the data available and keeping the data in sync and have a short cutover period you decide to use log shipping.

To summarize our plan we will do a full backup and send it to the new hosting location. Next we will create a differential backup and a transaction log backup and send them over the network to the new hosting location. Once this is done we will enable log shipping. After all of the transaction logs have been applied we can swap the primary and secondary servers.

Step 1
Make sure your database is in the Full or Bulk-Logged recovery model. You cannot enable transaction log backups or transaction log shipping in Simple recovery model.

To satisfy the requirements of making the data available and keeping the data in sync and have a short cutover period you decide to use log shipping.

Step 2
Alter your backup plan, so full backups will not occur during the time needed to mail your full backup and restore it on your destination server in the new data center. The reason for this is so that we can create a differential backup to apply any changes that have occurred since the full backup, prior to enabling log shipping.

I would recommend sending the backup overnight to get it there as soon as possible. If the file is not extremely large you could probably FTP a compressed backup file.

Continue to do differential and transactional log backups as normally scheduled for your backup plan (a/k/a recovery plan).

Step 3
Create a full backup or use the latest full backup that you have. If you changed the recovery model in Step 1 you should create a new full backup. Copy the backup file to a USB drive and mail the drive to your data center. In my case I sent this overnight across the US and was able to get access to the backup file within three days.

Step 4
Restore the backup on the secondary server using NO RECOVERY, in order to restore the differential and transaction log backups.

Restore the backup on the secondary server using NO RECOVERY

If you restored the database in "No Recovery" mode you will notice that the database says "Recovering..." as shown below.

notice that the database says "Recovering."

Step 5
Take a differential backup and copy it to the secondary (destination) server over the network. This should be much smaller than the full backup. The file size will really depend on the amount of transactions that occurred since the full backup was created.

Step 6
Restore the differential backup on the secondary server using NO RECOVERY. No recovery must be selected to restore a transaction log backup and to kickoff log shipping.

Step 7
Disable transaction log backups from your backup plan on the primary (source) server. We are about to enable Log shipping and this will be used to create transaction log backups on the primary server and they will be sent and restored on the secondary server.

Step 7a
If any transaction log backups were created after the differential was created in Step 5 these will need to be copied and restored as well using the NO RECOVERY option.

Step 8
Create a new transaction log backup on the primary server and copy it to the secondary (destination) server over the network. This is done just to keep the two databases in sync, so enabling Log Shipping goes quicker.

Step 9
Restore the transaction log backup on the secondary server with NO RECOVERY. Remember the secondary server must be in NO RECOVERY mode to initialize log shipping.

Step 10
On the principal server right click on the database and select tasks and then "Ship Transaction Logs...". This will load the menu to start Transaction Log Shipping.

On the principal server right click on the database and select tasks and then "Ship Transaction Logs..."

Step 11
Once you click on "Ship Transaction Logs..." you will notice the next window. Make sure you click on the backup settings button. This is where you will configure the log shipping settings as shown below.

This is where you will configure the log shipping settings

Step 12
On the next window we will configure how often transaction logs will be backed up and the path used to store the transaction log backups. Once this is done we will return to the transaction log shipping configuration window

Step 13
We will now click on the add button to add a secondary server. This will be your destination (secondary) server in the data center.

now click on the add button to add a secondary server

Step 14
Click on the "Connect..." button and connect to the secondary database server at the data center and select or enter the database name. In my case I am using "AdventureWorks". We want to select "No, the secondary database is initialized" radio button, since we have already shipped and restored the full backup. Once this is done select the "Copy Files" tab.

connect to the secondary database server at the data center and select or enter the database name

Step 15
On the "Copy Files" tab specify the network share path you will use on the secondary server. Transaction log backups on the primary server will be copied to and restored from this location. You can also modify the job that will be created to copy the files and also modify how long backups will be retained after they are copied.

specify the network share path you will use on the secondary server

Step 16
On the "Restore Transaction Log" tab this is where you will need to specify the state of the secondary server. You will have three options including No Recovery mode, Standby mode and Standby mode disconnect connections when restoring. If you have a requirement to allow read access for testing before the final cutover you will have to choose one of the two Standby options. Note: standby only works when the primary and secondary servers are using the same version of SQL Server.

Note: standby only works when the primary and secondary servers are using the same version of SQL Server.

Step 17
We have now completed the setup for Log Shipping. Click on the OK button on the screen shown in step 12.Note: standby only works when the primary and secondary servers are using the same version of SQL Server.

Note: standby only works when the primary and secondary servers are using the same version of SQL Server.

Step 18
Now we will wait for log shipping to sync all transactions from the primary database to the secondary (recovering) database. This will be done with the following SQL Server Agent jobs shown below. (Note: the black lines are just removing personal data)

wait for log shipping to sync all transactions from the primary database to the secondary (recovering) database

The two jobs above are created on the primary instance. The backup job will backup the transaction log to the file share specified in step 12.

This will be done with the following SQL Server Agent jobs shown below

The three jobs above are created on the secondary instance. The copy job copies the backups created on the file and moves them and restores them to the file share in step 15.

Step 19
Once the jobs have executed for a while the secondary database will be in sync with the primary database. At this point it would be a good idea to acquiesce the primary server so no additional transactions are created, do one last transaction log shipment and then make the swap.

You can now terminate log shipping and restore the secondary database.

Right click on the primary database and click on properties. At the bottom of the list on the left you will see Transaction Log Shipping. Select it and then uncheck the checkbox with the label "Enable this a primary database in a log shipping configuration." You will see the window shown below. Click on the yes and the ok buttons for this window and the properties window and this will remove log shipping.

You can now terminate log shipping and restore the secondary database.

Step 20
Now its time to make the swap. Take the primary database offline. This can be done by using this command.

ALTER DATABASE AdventureWorks SET OFFLINE

Step 21
Recover the secondary database to complete the cutover. Issue the following script to bring the database online.

RESTORE DATABASE AdventureWorks WITH RECOVERY

Step 22
Now point all new connections to the new database server and your migration is now complete

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author John Sterrett John Sterrett is a DBA and Software Developer with expertise in data modeling, database design, administration and development.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 12, 2014 - 9:46:58 AM - Gbemi Back To Top

 

Very nice guide.  Thank you.

I would also script out the logins prior to starting using SP_HELP_REVLOGIN and Yes, we can achieve the same with DB Mirroring from SQL Server 2005 on.

Gbemi


Thursday, November 15, 2012 - 3:24:14 PM - Greg Robidoux Back To Top

@Salman - yes you would also have to deal with logins and users for the application to gain access to this migrated database.

You could either move your logins from your old server or you could create new logins and link them to the existing database users.

Take a look at these tips:

http://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/

http://support.microsoft.com/kb/246133


Thursday, November 15, 2012 - 2:41:48 PM - Salman Back To Top

Very good description. Just wanted to make sure that after step 21 do I have to create a login for the application and map it to the newly restored database?


Saturday, August 07, 2010 - 8:39:40 AM - Gethyn Ellis Back To Top
Depending on the SQL Sever version you could use mirroring to achieve the same result.


Wednesday, August 04, 2010 - 11:18:25 PM - SimMur Back To Top
Wouldn't DB Mirroring be better for this task?


Learn more about SQL Server tools