0

I know that SQL server Distributed availability groups can sync data between two different Major versions,

Lets say Global Primary - 2016

Secondary AG - 2017

This sync will work and I can do failover as well. But what Happens after the failover I want to revert back to 2016?

  1. Is this possible?
  2. All the delta data from 2017 will sync with 2016?
Improve this question

1 Answer 1

Reset to default
5

No.

Once you fail over to the higher version, you cannot go back down.

Using Distributed AGs for a mixed version topology only works when the writable main AG (specifically individual the read/write replica) is on the lower version. From the docs (bolding mine):

Distributed availability groups in SQL Server 2017 or later can mix major versions of SQL Server in the same distributed availability group. The AG containing read/write primary can be the same version or lower than the other AGs participating in the distributed AG. The other AGs can be the same version or higher. This scenario is targeted to upgrade and migration scenarios.

This is essentially the exact same restriction that is in place with using log shipping, mirroring, etc to do upgrades & migrations to new versions. The higher version knows how to read in data from the older version, but the older version may not be able to accept changes from the newer version (new features, etc).

Why do they do that?

When software engineers were building SQL Server 2016, they had no idea what changes might happen in 2017. Since they do not have a time machine to properly handle future changes & features, the older version must refuse those changes.

Additionally, 2016 can't just accept 2017 data until it hits something it doesn't recognize. The 2017 data stream is essentially an unknown data format. Since the older version may not be able to accept some data correctly, it must refuse all data. Accepting an unknown data format & attempting some sort of "best effort" would risk data corruption on the lower version, and raise a large number of scenarios that would have to be anticipated & supported by SQL Server. Protecting data integrity is a primary goal for any DBMS, so sending data to an older version is considered an unacceptable risk from that regard.

Improve this answer
2
  • 3
    And since adding an untested database upgrade to your DR plans is insane, this configuration is just for performing an upgrade and migration to different servers. For instance this is commonly used to migrate to Azure VMs.
    – David Browne - Microsoft
    Commented Sep 20, 2021 at 12:28
  • 1
    @DavidBrowne-Microsoft An untested database upgrade just became my disaster recovery (2016 -> 2019) lol, unfortunately. But all worked out, fortunately.
    – J.D.
    Commented Sep 20, 2021 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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