I'm planning to upgrade our SQL Server from 2016 Standard edition to 2022 Enterprise edition.
To prepare for potential issues, I want to have a rollback strategy in place. If, after the upgrade, our front-end applications experience database connectivity or compatibility problems, what's the best way to roll back to SQL Server 2016 Standard Edition while keeping both databases synchronized? I want to provide them with 48 hours rollback window. How can I keep databases on both servers in sync for 48 hours?
I've looked into database mirroring, log shipping, and transaction replication, but I'm concerned about compatibility issues between the versions.
What approach would you recommend?
-
First of all exclude mirroring and log shipping because you cannot apply logs on older version. Replication, triggers and manual sync are the only options.SergeyA– SergeyA2025年08月17日 20:23:49 +00:00Commented Aug 17 at 20:23
-
3Try and avoid synching the databases as it is a lot of work for anything other than simple schemas. It would be normal to test all the applications in the new environment before doing the production migration. Try to do both regression and stress tests.Aardvark– Aardvark2025年08月18日 10:55:23 +00:00Commented Aug 18 at 10:55
1 Answer 1
For SS 2022 EE - keep your compatibility on your user DB(s) set to 2016 which is 130 for the first week or so. Then change it on a weekend or off-peak day and monitor perf. Know your baseline perf before and after this change. Obviously know your baseline perf on SS 2026 before you upgrade to 2022 EE. So you're not able to test this upgrade in a TEST environment before you upgrade?