Are there any caveats in my SQL migration plan?

Question

I would like to move several databases from one Microsoft SQL Server instance (2014) to another instance (2016) while still in production. Are there any obvious caveats or holes in my plan?

1) Take a full back up of each database that I want to move

2) Restore databases to the new instance

3) Sometime in the middle of the night switch each source database to single user mode.

4) Take a last final transaction log backup

5) Restore transaction log backup to the new instance databases

6) Turn off old instance and point relevant resources at the new instance

Any advice, caveats, etc.

Answer 1

Additional considerations for:

1. Backup Databases

   • WITH COPY_ONLY in order to retain the backup sequence of any third-party tool or backup maintenance plan currently running on your old SQL Server.

2. Restore Databases

   • You might have to RESTORE DATABASE ... WITH NO_RECOVERY in order to be able to apply further transaction log backups to your new database.
   • Or after restoring the database backup run the RESTORE LOG ... WITH NO_RECOVERY on the new SQL Server so that further transaction log backups can be applied.

3. Single User Mode

   • (placeholder)

4. Final Transaction Log Backup

   • (placeholder)

5. Restore Transaction Log Backup

   • (placeholder)

6. Turn Off Old Instance

   • (placeholder)

And possible the most important point:

Test Procedure

• Test the procedure in your Development Environment

Answer 2

Depending on the amount of downtime you have, I would do the following :

1. follow the checklist/steps that I have listed here.

2. stage the application connection string to include failover partner. This way when you do cutover to new 2016, the first connection of app will fail since 2014 will not be available, but the second attempt will succeed since you have specified failoverpartner

   e.g. "Server=Partner_A; Failover_Partner=Partner_B; Database=AdventureWorks"

3. setup logshipping or mirroring from 2014 to 2016.

4. On the day of cutover, if using logshipping, take the tail log backup and restore on 2016 with recovery. if using mirroring, change to sync mode and failover.
5. Follow the post steps listed in step 1.
6. Ask the app team to test/sign off the application.

Answer 3

If you are running Enterprise Edition then you can simply set up AlwaysOn to the 2016 server and fail over once.

If you are running Standard i'm 99% sure mirroring is supported and you can do the same with mirroring.

I think the only requirement is that you need to be running either Enterprise or Standard on both ends.

This is how I did my 2005 to 2012 upgrade a few years ago. We set up mirroring a few days prior to the upgrade date from 2005 to 2012. On the night of the upgrade we failed over. When you do this across different versions you can only fail over once. This way you don't have to worry about taking log backups for the most recent data.