0

We are attempting our first ever side by side upgrade of a 2 node(multi-subnet, enterprise edition) AG from sql 2016 to 2019. I have some questions on how best to approach this:

  1. Do we create a brand new cluster and AG with sql2019 and use log shipping to populate and cut over to the new environment?
  2. Do we add 2 new 2019 nodes(1 in each existing subnet), and set them up as secondaries in the existing AG? I know normally you can't mix versions in an AG, but for an upgrade like this I've read it's done without issues as long as you don't try to fail back over to the older version nodes.
  3. How are AG listeners handled in #2? I think we should be able to use the existing listeners since the new nodes are in the same subnets as the old version of SQl nodes?

I can't find any step by step guides for this online when we are dealing with clusters and AG's

1 Answer 1

1

We are attempting our first ever side by side upgrade of a 2 node(multi-subnet, enterprise edition) AG from sql 2016 to 2019.

So all new OS Environments? Do you want to keep the same cluster? Move to a different cluster? What are the current OS versions?

Do we create a brand new cluster and AG with sql2019 and use log shipping to populate and cut over to the new environment?

You can't log ship to an AG, so you'd have to log ship to a stand alone and then bring it online, then either add or seed the other replicas.

Do we add 2 new 2019 nodes(1 in each existing subnet), and set them up as secondaries in the existing AG? I know normally you can't mix versions in an AG, but for an upgrade like this I've read it's done without issues as long as you don't try to fail back over to the older version nodes.

This is possible, the newer versions won't be readable and there will be a database upgrade upon failover. After the failover to the newer version the older version will cease to properly work in the AG and must be removed, but that's also the point.

How are AG listeners handled in #2? I think we should be able to use the existing listeners since the new nodes are in the same subnets as the old version of SQl nodes?

Since it's the same AG there will be no issues with the listener as it's part of the AG.

One last thing to point out is that it is possible to use a distributed availability group to migrate as well, hence my original questions about OSE.

2
  • current nodes are windows 2016, new are 2019. can you have different OS versions in the same cluster? if so, then using the existing cluster would be preferrable. new nodes not being readable is fine, and the old ones would be decomissioned after the failover to new version, so that is fine as well. I'll have to look into Dist. ag's, never used those before.
    – DBA Greg14
    Nov 8, 2023 at 20:11
  • Since you're on WS2016, you can have nodes Ver+1 in the cluster, however after adding them and removing the old nodes with the older versions of Windows, you'll need to exit compatibility mode by updating the cluster functional level (trivial powersherll command), after that you'll not be able to add any down level nodes. Nov 8, 2023 at 20:45

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.