What we are performing today ?
To upgrade/migrate (side-side) SQL Server 2014 Availability group(s) running on Windows Server 2012 R2 to SQL Server 2019 running on Win 2016 with the least amount of downtime.
My Current Environment Setup :
I have a 2 node Windows Server 2012 R2 Failover cluster hosting SQL Server 2014 Always On Availability Group with synchronous commit mode. I have a listener configured for my applications to connect. These 2 replicas are running SQL 2014 with Latest builds.
Replica(Node) Details are below :
Above are my two replicas(Nodes) which are running with WindowsServer2012R2+SQL 2014.
Our main Agend is
I have SQL 2014 AG running on Windows Server 2012R2 which needs to be upgraded/migrated to SQL Server 2019 running on Windows Server 2016 with a very minimal downtime and no configuration changes for the Application teams.
High Level Implementation Plan :
. Get ready with 2 new builds of Windows Server 2016 (W16SQL2019A & W16SQL2019B)
. Install SQL Server 2019 on both win 2016 servers
. Add W16SQL2019A and W16SQL2019B nodes to the same windows cluster
. Enable Alwayson HADR Feature in SQL 2019
. Add the 2 nodes as replicas in SQL Server Alwayson Availability Group
. Join the Databases .
. Check the Dashboard of AG and make sure all looks healthy and Green
. At the change window of final cutover date/time, failover SQL Server 2014 to SQL Server 2019 and remove the old replicas( SQL Server 2014) from AG.
. And in Windows Server 2012R2 EVICT both nodes from the Cluster
Important things be a taken into Consideration after Successful configuration
Adding Win2016 nodes to existing Win2012R2 Same cluster is Leveraging MIXED MODE. Please don’t run this mixed mode WSFC for a long period. Microsoft may not support if you stay in mixed mode for more than 4 weeks.This blog is only to perform rolling upgrades to make your systems are really highly available. Try to wrap up the entire process in a day or two and make sure to be done with it.
Sometimes after adding the new replicas the Databases synchronization will not start automatically. you need to Join each databases Manually to get it SYNCHRONIZED.
Change the failover mode to manual for all replicas just to make sure cluster has no control over failing over my AG automatically.
At the final cut over before Failover the AG make sure to change Availability Mode to SYNCHRONOUS COMMIT mode and then proceed with your failover of AG from W12SQL2014A( Which is a Primary Replica) to W16SQL2019A.
At this point, W16SQL2019A took over the primary role of all your databases participating in your AG have been upgraded to SQL 2019 and the other SQL 2019(W16SQL2019B ) Instance will also be in sync
But the two SQL 2014 Instances will be in unhealthy state, In fact those databases will become inaccessible at this time, since Logs cannot be shipped from higher(2019) to lower(2014) version.
That mean the Databases will not be SYNCHRONIZED on SQL 2014 Instances
I did a Cleanup by removing both SQL 2014 Instances from AG as replicas and EVICTED the W12SQL2014 Nodes from WSFC.