Upgrade SQL Server 2014 AG to SQL Server 2017 AG

Question

Is it possible to migrate SQL Server 2014 AG (Windows 2012, SQL Server 2014) to SQL Server 2017 AG (Windows 2016, SQL Server 2017) using rolling upgrade method?

What I mean is

1. Build 2 SQL Server 2017 instance.
2. Add the above nodes to the existing cluster.
3. Add the new nodes to the existing AG as an additional replica (Async)
4. Failover to 2017 server
5. Remove the 2014 nodes from Ag
6. Remove the 2014 nodes from the cluster.

Would this work or do we have to build everything from scratch?.

Answer 1

Please follow below steps to configure and do failover:-

1. Build 2 SQL Server 2017 instance.
2. Add the above nodes to the existing cluster.
3. Disable log backup job on existing alwayson.
4. Take full and log backup and restore it on newly created nodes.
5. Add the new nodes to the existing AG in synchronous mode and failover mode to automatic. Automatic failover should be configured between Existing Primary and newly created node(primary that you want to configure).
6. Open dashboard and wait for data sync.
7. Failover to 2017 server. After successfull failover ,set automatic failover between 2017 server nodes.
8. Remove the 2014 nodes from Ag
9. Remove the 2014 nodes from the cluster.

Alternate method is do by log shipping .But in that case you have to do some additional steps.

i hope this would help to steup AG

Answer 2

As per the msdn link here, the answer is very much or possibly yes. I have not tried this out so I cannot assure that it's 100 percent will work out. It suggest that if you have a test environment go ahead and setup scenario like this and give it a shot. I is Just like how mirroring works, it's expected to be same or similar to logshipping. But make sure that the secondary is not readable as the secondary replica will be in higher SQL Server version .