Sql-server – Cross Cluster Migration 2012 to 2016

sql serversql-server-2012sql-server-2016

I've tried researching cross-cluster migrations but other than the one white paper Microsoft put out I can't find any information. Can this be used to upgrade from SQL 2012 to SQL 2016 and if so would there be any issues with replication? If somebody has experience doing this can you provide any tips?

Going from SQL Server 2012 (Windows 2008 R2) to SQL Server 2016 (Windows Server 2012 R2)

Cross-Cluster Migration

Best Answer

I have a largely automated process to upgrade versions and replicate data back to the old version using transaction replication without a snapshot init. I need minimal downtime in this environment so we don't have time to make the databases generate a snapshot and send it back just in case we need to roll back. You might allow yourself downtime but in that case if you will use a snapshot make sure to script out the 'create database' section with all the proper filegroups and options. You'll need to snapshot back to a database. It can create it for you but you're better off doing it in advance.

Be careful of PK constraints if you are not going to init. If you get even 1 row in your old table your PKs which doesn't match, you will get PK conflicts and errors.

Have a backup plan handy so script out snapshot replication or something else like Red Gate Data Compare just in case things go sour.

Test this process repeatedly in staging and automate as much of it as you can. That way it's consistent and easy in the day off. Preferably check your code in.

Ensure you make the databases not writable and that all of your data is fully synced up, while ensuring nothing can write to the old db before you cut over clusters.

You can also look at switching your cluster IP address if you have tons of clients that you can't keep track of, but if you can, it's better to get a new IP. If you change IPs make sure to automate that as well and ensure you update DNS/RDNS and add a CNAME if you need to the new machine with the old name just in case clients connect by IP.

If you have enough downtime capability you can roll back at your leisure. Good luck!