3

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?

  • I'm not sure what you mean here because you can join a SQL server 2017 instance to a 2016 AG. – Sean Gallardy Dec 15 '18 at 2:49
5

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

  • 1
    This made me think it'd be possible: Upgrading Always On Availability Group Replica Instances "...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." Anyway, your side by side approach sounds quite good to me. Thanks! – Blablas Sep 10 '18 at 10:20
  • This, I believe is confusing 2 official documents stating 2 different thing, or are we understanding it incorrectly ? I have asked for clarification, will revert when I get one.Meanwhile if you have resources you can test – Shanky Sep 10 '18 at 12:26
  • @EmilianoGiovannetti Sorry for incorrect response, I confirmed and indeed the scenario you are asking is possible. I will update my answer. This all confusion arose because of different things mentioned in BOL. – Shanky Sep 11 '18 at 4:03

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.