1

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.

Bill Greer
  • 193
  • 1
  • 1
  • 8
  • 2
    how did it go in your practice run? – James May 15 '18 at 13:14
  • 1
    Logins? Configurations? – Evandro May 15 '18 at 13:15
  • 1
    I would run the upgrade checker against a backup restore at least, it's a pretty good test for compatibility. – Shaulinator May 15 '18 at 13:22
  • 1
    @James I am setting up for a practice run today. Before I execute I thought I'd reach out to the community to see if there were any obvious issues with my plan. – Bill Greer May 15 '18 at 13:22
  • @EvandroMuchinski Thank you. I will add check configurations and make sure I have all of the necessary logins on the new instance. – Bill Greer May 15 '18 at 13:22
  • It might be worth checking for hard coded server names in stored procedures and jobs. – James May 15 '18 at 13:24
  • @Shaulinator Just for clarification, you are recommending that I restore the backup to a 2014 testing instance and then run the upgrade against it? Maybe going the upgrade route altogether if it goes well.?.?.? – Bill Greer May 15 '18 at 13:26
  • 1
    @james I have added that to my list. Thanks. – Bill Greer May 15 '18 at 13:26
  • @BillGreer That would work, I really mean to run the upgrade compatibility checker against the database you want to upgrade. Preferably not the copy in use at the moment with your production DB as I can see that causing issues, but with a backup restored of it. https://msdn.microsoft.com/en-us/library/ms144256(v=sql.120).aspx – Shaulinator May 15 '18 at 13:33

3 Answers3

3

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
John K. N.
  • 14,660
  • 9
  • 40
  • 93
1

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.
Kin Shah
  • 60,908
  • 5
  • 111
  • 230
0

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.

Alen
  • 519
  • 3
  • 9