Update and migrate database from SQL Server 2014 to SQL Server 2019

Posted on

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:

  1. Download Microsoft® Data Migration Assistant and search for possible incompatibilities with SQL Server 2019.
  2. 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.
  3. 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.
  4. Repeat this on each database on your test environment.
  5. Apply the fix you have discovered on your test environment on production
  6. Upgrade production to SQL Server 2019 and change the compatibility level of each database to SQL Server 2019.

Leave a Reply

Your email address will not be published. Required fields are marked *