We are in the middle of a project to upgrade nearly 30 servers from SQL 2008 R2 to SQL 2017, this project merged with another to upgrade Windows Server 2008 to 2016. We started later than planned (previous over-run) and are mid-testing our process / timings.
We have created new Win 2016 servers with SQL 2017 installed, clone disks to attach to the new servers, run scripts to attach DBs, create logins with the original GUI, etc. When we re-direct DNS entries our in house & off the shelf Apps work. All good so far.
The problem we have is with SQL Replication.
Is there any proven way to migrate Snapshot and Transactional Push Replication from one server to its upgraded self ?
We have tried following various sets of advice (Google being our best friend), but even if we think we have followed the same process a second time (re-runs for practice / confirmation of process / timings) we get different SQL Agent Jobs set up. Sometimes Log-Reader jobs attached to Snapshot Replication, sometimes duplicate Distribution Jobs where on the original server there was only one. Sometimes when we re-Initialise a publication from the Replication Monitor it fails to start the SQL Agent Job ( but if we start it manually it works OK ).
I suspect if we keep at it we will end up with all our Replication working, but in Production we have approaching 40 Publications some going to two Subscriptions and to “fix” those on the fly during a GO Live weekend is a daunting task ( some extract data for MI, but others are bi-directional updating data between the Core App and remote users via servers in a DMZ ).
However complicated .. is there a tried and trusted method of achieving this ?
Many thanks in anticipation