Database Administrators Asked on December 19, 2020
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?
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.
Answered by Max Vernon on December 19, 2020
Get help from others!