4

I have an old Availability Group made up of 2 SQLServer 2016 instances (2 Windows 2016 servers in WSFC) I also have 2 new SQLServer 2017 instances (2 Windows Server 2016) that I originally wanted to join to the 2016 AG.

This is a 0 downtime migration scenario and the 2016 servers should be dismissed once the DBs were aligned on the 2017 ones.

To my great disappointment, I discovered that it is not possible to join the 2017 instances to an existing 2016 AG, but I cannot afford the burden of stopping production, taking and restoring a backup, waiting for DBs synchronization, changing the name (and possibly the ip) of the new AG to match the original one unless as the very last resource...

Then I came across the new 2016 service called "Distributed AG Group" and I started thinking about using it for my migration scenario ... basically like this:

  1. Create a new AG with the SQL 2017 instances
  2. Create a Distributed AG between the original 2016 AG and the new 2017 AG (applications continue to connect to the 2016's listener)
  3. Wait for the DBs synchronization to take place in the 2017 AG
  4. Make the 2017 AG as the primary
  5. Remove the 2016 AG from the Distributed AG (short applications downtime)
  6. Change the name and the ip of the 2017's listener (applications are up again)
  7. Remove the distributed AG

It is feasible? Can I mix 2016 and 2017 AGs in a Distributed AG? Or there is (obviously!) something that I'm missing and there's a simpler or more proper way to make it?

Blablas
  • 43
  • 6

1 Answers1

7

It is feasible? Can I mix 2016 and 2017 AGs in a Distributed AG?

I have confirmed from Allan Hirt SQL Server MVP and HA/DR Guru that this is indeed possible and supported. So please go ahead, the confusion may arise because in BOL two different things are mentioned. I will highlight both

Quoting from Distributed Availability groups document

A distributed availability group spans multiple availability groups, each on its own underlying WSFC cluster, and a distributed availability group is a SQL Server-only construct. This means the WSFC clusters that house the individual availability groups can have different major versions of Windows Server. The major versions of SQL Server must be the same

So while you can have different Windows server version in DAG but you cannot have different SQL Server version in DAG. From here it seems like the versions should be same but if you again look at Upgrading Availability group instances it says

To migrate to a new version of the SQL Server instance using AGs, the only supported method is a distributed AG, which is in SQL Server 2016 Enterprise Edition or later.

Now you should believe this one not the the previous one.

Or there is (obviously!) something that I'm missing and there's a simpler or more proper way to make it?

Apart from distributed AG, I would consider 2 approaches here

  1. Side By side upgrade

I believe transaction logshipping should help here.

  • Create new WSFC with SQL Server 2017 installed--No downtime

  • Do not create AG as of now

  • Logship database from SQL Server 2016 to SQL Server 2017. --No downtime

  • During "cutover" stop logshipping and bring database on SQL Server 2017 online.--small downtime

  • Now point application to this new database.

  • Go ahead and configure AG on new SQL Server 2017.

I know there is bit of pain involved in configuring logshipping if you have big databases but this is best I see.

  1. In-place upgrade

Do the rolling upgrade of Availability groups. Upgrading Availability group instances

Shanky
  • 19,148
  • 4
  • 37
  • 58