Steps to Upgrade SQL Server 2012 Always On Availability Groups to SQL Server 2019

By:   |   Updated: 2022-11-21   |   Comments (3)   |   Related: > Availability Groups


I have a 2-node SQL 2012 Always On Availability Group running on Windows Server 2016 Standard edition. Can you demonstrate the steps necessary to upgrade the SQL 2012 AG to SQL 2019 AG with little to no downtime?


SQL Server upgrades are often required in organizations. There are many ways to do a standalone SQL Server upgrade, but upgrading a multi-node AG environment is tricky and a bit complex.

I recently received a requirement from a client to upgrade a 2 Node SQL 2012 Always On Availability Group to SQL 2019 Always ON (AG) with minimal or no downtime. The SQL 2012 environment was running on Windows Server 2016 Standard edition, and the new nodes for SQL 2019 were to use Windows Server 2016 Standard.

To accomplish this task, we can follow one of two methods:

  1. Upgrade the replicas one-by-one starting from the Asynchronous Secondary replica followed by the Synchronous Secondary replica and upgrading the Primary replica as the last upgrade having the same SQL Server version on all the replicas.
  2. Add one or more new SQL replicas (higher SQL version, for example, SQL 2019) to the current AG (which is on SQL 2012) and then failover the lower version Primary replica (SQL 2012) to a higher version (SQL 2019) Synchronous secondary replica. Both methods can be done by following the rolling upgrade process.

This tip will focus on the second method, discussing all the steps required to achieve this task.

Note: Mixing the different versions of SQL Server in the same AG is only supported when performing the AG upgrade through the rolling upgrade method. This activity should be done quickly as it is not recommended to mix different SQL version in the same AG and keep it in the same state for a long time.


Before beginning the AG upgrade process, check the supported versions and edition upgrades document from Microsoft to ensure that the SQL version you want to upgrade to is supported. In my case, I intended to upgrade from SQL 2012 AG to SQL 2019 AG, and the supported version for this upgrade requires SQL Server 2012 SP4, which is my current SQL environment.

Current Environment

In my current environment, I have a Windows Server Failover Cluster (WSFC) with 2 nodes using Windows Server 2016 Standard Edition OS with the following details:

  • WSFC Name: SQLCLS1
  • Node 1: SQL-DB-P01
  • Node 2: SQL-DB-P02
  • Operating System: Windows Server 2016 Standard
2 Node Windows Server Failover Cluster (WSFC)

And we have two SQL 2012 instances participating in the Always On Availability Group with the following configuration:

  • SQL Replica 1: SQL-DB-P01\SQL2012
  • SQL Replica 2: SQL-DB-P02\SQL2012
  • AG Name: SQLAG01
  • Listener Name: SQLAG01LSNR
  • Listener Port: 1422
  • SQL version: SQL Server 2012 SP4
  • Availability Mode: Synchronous Commit
2 Node SQL Server 2012 Always On Availability Group

And we aim to upgrade/migrate from SQL 2012 AG to SQL 2019 AG, as depicted in the following diagram.

Steps to SQL Server AG upgrade from 2012 to 2019

Major Steps

We will perform the following significant steps during this tip:

  1. Add 2 new nodes to the WSFC
  2. Install SQL 2019 on both new nodes
  3. Take a full backup before initiating the AG upgrade process
  4. Add a new SQL 2019 replica to the existing Always On Availability group (SQL 2012 version)
  5. Change the availability mode to Synchronous Commit for the first SQL 2019 replica
  6. Failover the AG to the first SQL 2019 replica to become the Primary role
  7. Remove both SQL 2012 replicas from the AG
  8. Change the availability mode to Synchronous Commit for the second SQL 2019 replica
  9. Evict both the old nodes from WSFC

Let’s jump into the above steps one by one in more detail.

Step 1: Add New Nodes to Windows Failover Cluster (WSFC)

We first need to add two new nodes, SQL-DB-P03 and SQL-DB-P04, to the existing WSFC to install SQL Server 2019.

Add node in WSFC

Perform the following steps for adding new nodes to WSFC SQLCLS1:

  1. Connect to WSFC SQLCLS1 from the failover cluster manager on SQL-DB-P01 (Node 1).
  2. Right-click on the nodes and select Add Node to open the wizard.
  3. Click Next. Enter the new nodes as shown in the above screenshot.
  4. Click Next. A success message will appear if no issues are found, as shown below.
Add node in WSFC

Click on the nodes in the failover cluster manager, and you will see the newly added nodes to the cluster. The newly added nodes also run on Microsoft Windows Server 2016 Standard edition.

Windows Server Failover Cluster

Step 2: Install SQL 2019

The next step is to install SQL Server 2019 on the new nodes (SQL-DB-P03 and SQL-DB-P04). Enable the Always On Availability Group feature by right-clicking on the SQL engine services for a new instance. Click properties and select the check box as shown in the screenshot below:

Enable Always On Availability group

Step 3: Take Full Backup

We have one database, SQLDB1, for this activity.

Note: Taking a full backup is highly recommended at this point, as in the mixed SQL version AG upgrade method. Once the failover is done from the lower version (SQL 2012) Primary to the higher version (SQL 2019) Secondary replica, the synchronization will be stopped to the lower version replica. Failback to the lower version will not be possible, and any mishap can cause the databases to be inaccessible. So it is better to have a fresh copy of the backup for all the databases participating in the AG upgrade process.

Step 4: Add New Replicas to AG

After the SQL 2019 installation on nodes SQL-DB-P03 and SQL-DB-P04, we need to add these two replicas to the existing AG, which is running on SQL 2012. Once the SQL 2019 instances are added to AG, we get the following picture:

SQL Server AG Dashboard

You may see the warning sign for the new replicas in the dashboard. This is probably because the different higher version replicas (2019) are added to SQL 2012 AG. The warning message will show that the replica needs to join the availability group but that replicas are already part of the AG.

Note: At this point, the newly added replicas of SQL 2019 cannot be read, and the databases will be in the state of (Synchronizing / In Recovery) as shown below:

SQL Server management Studio

Step 5: Change Availability Mode

This processes most important step is performing the manual failover to one of the SQL 2019 replicas. Among the two SQL 2019 replicas, I chose SQL-DB-03\SQL2019 to do the failover and become the next primary replica. Before the failover, we need to change the availability mode for SQL-DB-P03\SQL2019 to Synchronous, as shown below.

AG properties

Step 6: Perform Failover

Currently, SQL-DB-P01\SQL2012 is the primary replica. I ran the failover wizard to failover the AG SQLAG01 from SQL-DB-P01\SQL2012 to SQL-DB-P03\SQL2019.

During the failover process, all the databases participating in the AG will be upgraded to SQL Server 2019. This process will take some time, depending on the number of databases. This will be the only downtime required for this activity unless you plan to perform a second failover to the second replica of the SQL 2019 version. In this case, the failover will be quick as there will be no upgrade because both the new replicas will have the same SQL version and be in sync.

After the failover from SQL-DB-P01\SQL2012 to SQL-DB-P03\SQL2019 is done successfully, the AG dashboard looks like this:

SQL Server AG Dashboard

As you can see from the above screenshot, SQL-DB-P03\SQL2019 is now running in the primary role. At this point, the second replica of SQL 2019 version SQL-DB-P04\SQL2019 will be in sync with the new Primary replica SQL-DB-P03\SQL2019, and as mentioned earlier, synching to the lower version will be stopped after failover to the higher version, so now the syncing is stopped to SQL 2012 replicas, and its status is showing unhealthy in the dashboard.

Step 7: Remove Old Replicas

Now we will remove the SQL 2012 replicas SQL-DB-P01\SQL2012 and SQL-DB-P02\SQL2012 from the AG as it is no use now. We will get the following image of the AG dashboard after removing SQL 2012 replicas.

SQL Server AG Dashboard

The Primary replica still shows a warning sign, which was showing earlier after adding it to the SQL 2012 AG. We need to complete a failover to the secondary replica SQL-DB-P04\SQL2019 (the same version), which will make the warning sign disappear.

Step 8: Change Availability Mode

We changed the Availability mode to Synchronous for SQL-DB-P04\SQL2019, performed the failover to it, and got the following image for the AG dashboard:

SQL Server AG Dashboard

Great! Now all the replicas are green.

We can perform the failover to the SQL-DB-P03\SQL2019 replica if we want to keep it as Primary. For now, we will leave SQL-DB-P04\SQL2019 as our primary replica.

The compatibility level is not selected for the upgraded databases at this stage. We can change the database compatibility level according to the requirements and compatibility of the application. Go to database properties. We can see all the compatibility levels applicable up to SQL Server 2019, as shown below.

SSMS Database Properties

Check if the database is accessible and working fine after the AG upgrade. We created one simple table, dbo.EmpInfo, in the SQLDB1 database before the upgrade, which was populated with dummy records.

We executed a simple SELECT query against that table to check if the data was intact and accessible. Below is the screenshot of the query result.

SSMS query result

Great! Everything is good at this stage.

Step 9: Evict Old Nodes

Now, as the last step, we can evict both SQL 2012 nodes, SQL-DB-P01 and SQL-DB-P02, from the WSFC. After the removal of the above-mentioned nodes, we get the following screenshot:

Windows Server failover cluster


This tip includes all the necessary steps to upgrade an AG environment from SQL 2012 to SQL 2019 as a rolling upgrade process. These steps can be more complex depending on the number of AGs, the number of databases in the AG, and the size of those databases. These steps can also be applied to other SQL versions depending on the supported versions for upgrades. I hope this article is helpful for many SQL DBAs and developers.

Next Steps
  • As mentioned, this is one method of upgrading an AG from a lower to a higher version. I suggest using this method for smaller databases to take less time during the upgrade (failover).
  • This method of upgrading SQL Server AG can also be applied to other SQL versions.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Muhammad Kamran Muhammad Kamran is a Senior SQL DBA with many years of experience in providing SQL Server high-availability solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-11-21

Comments For This Article

Thursday, April 11, 2024 - 1:29:16 AM - Davood Taherkhani Back To Top (92173)
Very efficient.

Monday, April 10, 2023 - 10:20:48 AM - Muhammad Kamran Back To Top (91092)
Thank you for liking my article.
About your question, Yes it is possible, you can add different version of OS. Well, I did not test this scenario yet but soon i will test this scenario and probably will write an article on it.
consider the same article, you can add two nodes having Windows Server 2019 installed on it (at Step 1), add it to WSFC and install SQL 2019 on it, and follow remaining steps. It is important to remove old replicas from AG after successful failover to the newly added replicas and also evict old nodes from WSFC.

Monday, April 10, 2023 - 8:50:03 AM - Ameena Lalani Back To Top (91089)
Great article! I have one question about Step 1: Add New Nodes to Windows Failover Cluster (WSFC). Usually during these SQL upgrades OS upgrades are also required. Can we add different OS nodes in WSFC?

get free sql tips
agree to terms