Another relatively quick and easy 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 database mirroring. You have to have each user database in Full recovery model before you can set up a database mirroring partnership, and it needs to stay in full recovery model at all times. When you set up a database mirroring partnership, both the principal instance and the mirror instance need to be on the same edition of SQL Server (meaning Standard to Standard or Enterprise to Enterprise). It is possible to do a rolling edition upgrade with database mirroring, as I describe here.
Database mirroring was deprecated in SQL Server 2012, but it still works just fine in SQL Server 2016 and in SQL Server 2017. Early in the SQL Server 2017 development cycle, Microsoft had hinted that they were actually going to remove database mirroring from the product for SQL Server 2017, but they later changed their mind, and I think they may never actually remove it from the product.
Database mirroring is supported as an upgrade/migration method if the principal instance is running SQL Server 2008 SP3 or later, SQL Server 2008 R2 SP2 or later, SQL Server 2012 SP1 or later, or SQL Server 2014, and the secondary instance is running SQL Server 2016 or SQL Server 2017. The database failover is a one-time, one-way trip when you go from an older version of SQL Server to a newer version of SQL Server. Microsoft has some guidance about this upgrade process here.
Compared to log shipping, database mirroring is a little more complicated to get set up, and it has more restrictions. Database mirroring is easier to set up if both servers in the partnership are in the same Windows Domain, but if this is not the case, you can use certificates for your endpoint authentication.
Synchronous database mirroring lets you run a simple command to fail over each mirrored user database from the principal instance to the mirror instance (as long as each database is in a synchronized state). This fail over typically takes about 5-10 seconds.
Here are the basic steps for doing the final Production migration to a newer version of SQL Server:
- Stop the application(s) that are using the principal database(s) on the primary server
- Make sure each database is in a synchronized state and is using synchronous mirroring
- Fail over each mirrored user database, using a T-SQL script
- Remove the mirroring partnership, using a T-SQL script
- Redirect the applications to the new database server
Steps three and four can usually be done in less than a minute, depending on how many databases you have, how much transaction log activity has occurred, and your network and storage infrastructure performance.
Additional Resources
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.
Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017.
Here is a link to the complete series about upgrading SQL Server.