-1

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.

2
  • 1
    Please see this Microsoft Books Online about Replication Backwards Compatibility but I think in order to use Merge Replication, you'll need to make your 2019 instance the Publisher and your 2014 instance the Subscriber. This means you'll need a separate way to initially move the data over to your 2019 server so it is in sync at the start, such as with a BACPAC export – J.D. May 3 at 12:10
  • 1
    If you do this, I wouldn't use Merge. It adds triggers and tracking tables. Use Transactional Replication instead. But as @Fransesco points out, this is an unusual process. Normally you just test and upgrade. If something goes wrong and you have to go back, you'll loose data or manually recover it. – David Browne - Microsoft May 3 at 14:12
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.