Question :
I want to update and migrate database from SQL Server 2014 to SQL Server 2019 so that if there is any failure at SQL Server 2019 I will come back at SQL Server 2014 and get all the transactions back at SQL Server 2014 which held at SQL Server 2019.
Although I thought about merge replication. But want to know if the SQL Server 2019 fails so in this case will merge replications process occur. Will I get data of SQL Server 2019 in SQL Server 2014.
Please suggest a possible solution.
Answer :
My checklist would be:
- Download Microsoft® Data Migration Assistant and search for possible incompatibilities with SQL Server 2019.
- On a test environment upgrade to SQL Server 2019. At this point everything should still be the same because your compatibility level is still set to SQL Server 2014.
- Change the compatibility level of 1 database to SQL Server 2019 and check if everything is alright. At this point 99% of your queries go faster, but 1% go slower. Use Query Store to tune those queries.
- Repeat this on each database on your test environment.
- Apply the fix you have discovered on your test environment on production
- Upgrade production to SQL Server 2019 and change the compatibility level of each database to SQL Server 2019.