One easy and quick way to do a Production migration of data from one place to another or from an older version of SQL Server to a newer version of SQL Server is to use log shipping. Log shipping may not be new and shiny like availability groups, but it has very few blockers for implementation, and it does not add any additional workload to your legacy databases after you get it running. You do have to have each database in Full or bulk-logged recovery model before you can use log shipping. If you have a database in simple recovery model, and you try to enable log shipping, you will get a message like what you see in Figure 1.
Figure 1: Log Shipping Error Message
Log shipping does not have automatic fail over out of the the box, but you can pretty easily write some T-SQL to run the proper SQL Server Agent jobs on the primary and secondary server, check the status of the secondary database, and then recover the secondary database and bring it online on the new server. Doing all of this can semi-automate your fail over process.
Here are the basic steps for doing the final Production migration:
- Stop the application(s) that are using the primary database(s) on the primary server
- Run the LSBackup Agent job on the primary server for each log shipped database
- EXEC msdb.dbo.sp_start_job N’LSBackup_AdventureWorks2014′;
- Run the LSCopy Agent job on the secondary server for each log shipped database
- EXEC msdb.dbo.sp_start_job N’LSCopy_LABDB03_AdventureWorks2014′;
- Run the LSRestore Agent job on the secondary server for each log shipped database
- EXEC msdb.dbo.sp_start_job N’LSRestore_LABDB03_AdventureWorks2014′;
- Check the recent history of the log shipped databases on the secondary server
- Query the log_shipping_monitor_history_detail table in msdb
- Restore each log shipped database on the secondary WITH RECOVERY
- RESTORE DATABASE AdventureWorks2014 WITH RECOVERY;
- Redirect the applications to the new database server
Steps two through six can usually be done in just a few minutes, depending on how many databases you have, how much transaction log activity has occurred, and your network and storage infrastructure performance.
My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.
Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.
Here is a link to the complete series about upgrading SQL Server.