October 26, 2018 at 6:34 am
I am preparing to upgrade from SERVER 2018 e/w 2012 Sql Instances. The instances are in a WSFC and we use AlwaysOn. We will be moving to a cluster usein Server 2012 and SQL 2016. Will I have to manually move the old 2012 Databases to the 2016 Instances as .bak and and restore them as 2016 Databases? Before I restore the databases do I need to go in to options on the instance and set the option manually to 2016 or will it automatically sense that I am wanting to upgrade those databases to 2016 format. I have the same question is I were upgrading from 2008. Then I will have to manually set up my availability groups or will the wizard work?
October 26, 2018 at 7:09 pm
When moving to a new cluster, you will have to transfer the databases, logins, jobs, AGs, etc. to the new cluster and instances. If you plan to use Automatic Seeding when setting up the AG, then you only need to restore the Primary replica of each database. You will also need to update all of the clients with the new connection strings.
You can move the databases with backup/restore+log shipping. Take the last log backup on the original system WITH NORECOVERY to ensure no more transactions occur there. After restoring the last log backup to the new system WITH RECOVERY, the upgrade will begin. After it completes and the database is brought online, it will remain in a compatibility mode equal to the setting it had on the previous system. If you wish to run the database in 2016 mode, you will have to set that yourself after the upgrade. (There are performance differences in SQL 2016 quite different than SQL2012. Some things get better, somethings get worse. Be ready to put it back if things get weird.
Eddie Wuerch
MCM: SQL
October 29, 2018 at 11:51 am
Weird things happen and in the past it seems I always end up moving things manually for a myriad of reasons. I live in fear of that new primary instances not being set to the correct version and recovery model. Thanks for your response I was probably venting to reassure myself not to forget to do something. Over and out!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply