Upgrade/migrate SQL Server 2014 Availability group(s) running on Windows Server 2012 R2 to SQL Server 2019 running on Windows Server 2016

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.

Note : I’m not doing an in-place upgrade right now. This necessitates extra downtime. Let’s assume that in-place upgrades are not permitted.

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.

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: