sql server rolling upgrade with always on

Question

hi folks,
Currently we have 2 node SQL 2014 (Win 2012) AG setup. We have a upcoming project to upgrade sql server from 2014 to 2019. Is it possible to add sql server 2019 node to sql server 2014? if yes i m planning to follow below strategy.

1. Create 2 additional servers with OS 2016 and SQL 2019.
2. Add these 2 nodes to SQL 2014 Always on group.
3. Once all databases are synchronized across all 4 servers then start removing 1st node then 2nd and only keep SQL 2019 nodes.

Answer 1

Hi @SQLServerBro,

No, it is not possible. You may meet some problems when you add different version SQL server instance to an SQL AG .

Quote from MS document Prerequisites, Restrictions, and Recommendations for Always On availability groups;

>Each server instance must be running the same version of SQL Server to participate in an Always On Availability Group.

If you do not want to use in place upgrade. Suggest you using backup databases on SQL server 2014 then restore the databases to SQL server 2019. In addition, SQL Server 2019 (15.x) supports upgrade from SQL Server 2014 (12.x) SP2 or later. Refer to Upgrades from Earlier Versions to SQL Server 2019 (15.x).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

It is supported to have different versions in an AG, but only during the rolling upgrade phase. Note that once you failed over to the new version, you can't fail back to the older version. See this: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances?view=sql-server-ver15