What are we intending to do today?
With the least amount of downtime, upgrade/migrate SQL Server 2014 Availability group(s) running on Windows Server 2012 R2 to SQL Server 2019 running on Windows 2016.
My current working environment is as follows:
I’m running SQL Server 2014 Always On Availability Group with synchronous commit mode on a two-node Windows Server 2012 R2 Failover cluster. For my applications to connect, I’ve set up a listener. These two replicas are using the most recent releases of SQL 2014.
The following are the replica(node) details:
My two replicas (Nodes) running WindowsServer2012R2+SQL 2014 are shown above.
Our main Agend is
SQL 2014 AG, which is operating on Windows Server 2012R2, it has to be upgraded/migrated to SQL Server 2019, which is running on Windows Server 2016, with little downtime and no configuration changes for the application teams.
Plan of Action at a High Level:
. Prepare with two new Windows Server 2016 releases (W16SQL2019A & W16SQL2019B)
. SQL Server 2019 should be installed on both Windows 2016 servers.
. W16SQL2019A and W16SQL2019B nodes should be added to the same windows cluster
. In SQL 2019, enable the Alwayson HADR feature
. In SQL Server Alwayson Availability Group, add the two nodes as replicas
. Join the Databases .
. Examine AG’s dashboard to ensure that everything appears to be healthy and green
. Failover SQL Server 2014 to SQL Server 2019 during the final cutover date/time change window, then delete the old replicas (SQL Server 2014) from AG
. EVICT both nodes from the Cluster in Windows Server 2012R2.
After a successful configuration, there are a few things to consider.
Adding Win2016 nodes to a Win2012R2 environment MIXED MODE is being used by the same cluster. Please don’t leave this mixed mode WSFC running for an extended period of time. If you continue in mixed mode for longer than 4 weeks, Microsoft may refuse to help you. This blog is solely dedicated to performing rolling updates in order to ensure that your systems are truly highly available. Make an effort to complete the process in a day or two and ensure that it is completed.
Database synchronization does not always start automatically after adding new replicas. To get it SYNCHRONIZED, you must manually join each database.
Change the failover mode for all replicas to manual only to make sure the cluster doesn’t have any control over my AG failing over automatically.
Before you proceed with your AG failover from W12SQL2014A (which is a Primary Replica) to W16SQL2019A, make sure to alter the Availability Mode to SYNCHRONOUS COMMIT mode at the final cutover.
At this time, W16SQL2019A has assumed main responsibility for all databases in your AG that have been upgraded to SQL 2019, and the other SQL 2019 (W16SQL2019B ) instances will be in sync as well.
However, the two SQL 2014 instances will be in a bad way; in fact, both databases will become unreachable at this time since logs cannot be transferred from a higher(2019) to a lower(2014) version.
This means that databases on SQL 2014 instances will not be SYNCHRONIZED.
Finally, I EVICTED the Nodes W12SQL2014 from WSFC and removed both SQL 2014 Instances from Availability Group as Replicas.