MIGRATE SQL 2014 to 2019 (not UPGRADE)

Question

MIGRATE SQL 2014 to 2019 (not UPGRADE)

ARPRINCE

SSC-Addicted

Points: 466
More actions
March 2, 2020 at 5:34 pm

#3730251
Anyone had experience migrating a SQL Server to the latest version?
We have 2 servers, SQL 2014 SP3-CU4 + Windows Server 2012 R2 and would want to migrate both server to SQL 2019 + Windows Server 2016/2019.
Have to point out the following:
- The servers has a transactional replication running.
- The subscriber server is also a SSRS server.
- Agent Jobs running on servers
Plan is to build 2 new servers and move everything.
Any tips?
TIA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Haven't done this specifically, but what I've learned in the past is to test, test, test. If you want to move things, the backwards support is pretty good for 2017 for previous tech. What I would do is install in the new server, most the dbs and jobs, and re-run queries to verify things work as expected. Jobs should be OK, though you need to ensure names of instances are changed if this is applicable.

Security is always a challenge as it seems harder to migrate as something is always missing. I'd ensure every single job, linked server, etc. that you have runs on the new system. You say an SSRS server is a subscriber and you have replication, but y0u don't explain what the roles are? Is one instance a publisher/distributor and one a subscriber?

Make sure you know the rules for upgrading replication and the order. If you are migrating both, I'd think you can install the new servers, set up replication, restore databases, and test. For SSRS, there are some changes in 2019 for licensing and how this works, so check those and be sure it works.

Migration is often easier because you can move databases and data and test without actually moving clients. When it's working, then shut down and re-copy the current databases over. Replication complicates things, but you should be able to script and re-set this up easily.

ARPRINCE SSC-Addicted Points: 466 More actions · Answer 2

SVR_A - Distributor/Publisher

SVR_B - Subscriber + SSRS

Yes I plan to do a lot of testing prior to.

for the Sql Agent Job, just copy msdb?
for SSRS, copy [ReportServer] and ReportServerTempDB?
Any replication issues, I can just probably rebuild it.

Other good points to consider on your tips - thanks!

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Agent Jobs - script, search and replace instance names, security accounts if needed

SSRS - backup restore dbs.

Repl - script and be ready to rebuild. Once you install, migrate, get running, script again on the new systems. Then after restoring the latest dbs, you have a script that should work if you have issues.