We would like to migrate from SQL Server 2016 to SQL Server 2019. The databases are part of an Availability Group.

I added a new replica using SQL Server 2019 on a new node and made that replica part of the AG and then failed over successfully to it.

Would a Distributed AG also provide me the same functionality? What is the advantage of using a Distributed AG versus using the above method to migrate to SQL Server 2019?.

I'm trying to understand if the procedure I followed is correct and supported? In what way is a Distributed AG useful?


Per the Microsoft Docs, a Distributed Availability Group is:

a special type of availability group that spans two separate availability groups. The availability groups that participate in a distributed availability group do not need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability group deployment. This includes cross-domain and even cross-platform - such as between an availability group hosted on Linux and one hosted on Windows. As long as two availability groups can communicate, you can configure a distributed availability group with them.

Typically, a Distributed Availability Group is used to physically distance two AGs for disaster recovery purposes. i.e if the primary AG goes down, you failover to the secondary AG, and return to normal operation.

The fact that you've added a SQL Server 2019 node to your existing AG is not a problem as long as you understand that you need to upgrade all the nodes in the AG to 2019 if you plan on ever failing back to those old nodes. Once a database has been opened in a newer version of SQL Server, the database upgrade process means that you can never open that upgraded database in an older version of SQL Server. Therefore, you won't be able to fail back to those old nodes if the new SQL Server 2019 node has a problem.

| improve this answer | |
  • Thanks Max for the reply. Surprisingly i was able to fail back to my SQL2016 (using force failover back to SQL2016) from SQL2019 but ofcourse the data was not synchronizing. I was thinking it will not even allow me to failback to SQL2016 from AG wizard. any comments on that please? – kevin Oct 22 at 18:05

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.