How to Upgrade or Patch Alwayson Availability Group Instances?
If you are planning to upgrade your SQL Server instance that hosts an Always on Availability Group (AOAG) to a latest version SQL Server 2017 or you are planning to patch Alwayson Availability Group instances, then you should plan it carefully to minimize downtime and any risk during these activities. I would suggest you read this article before upgrading or patching SQL Server instances that has Always on Availability Group configured.
NOTE: First Test this solution in Lower Life Cycle environment then deploy in Production.
Points to Remember
Below are important points you should always keep in mind while you are planning or upgrading SQL Server Instances that has Alwayson Availability Group configured.
- All steps given in this article are limited to upgrading or patching SQL Server Instances that has AOAG configuration. It does not cover operating system upgrade.
- Secondary replica will not be available for failover or for read-only operations during the upgrade process.
- A higher version of a SQL Server instance cannot be added as a new replica to an existing AOAG. For example, a SQL Server 2016 replica cannot be added to an existing SQL Server 2014 AG.
- Don’t upgrade primary replica instance before upgrading or updating any other secondary replica instance. An upgraded primary replica can no longer ship logs to any secondary replica. This may cause data loss as data movement to a secondary replica is suspended.
Upgrade or Patch SQL Server AOAG Instances – Prerequisites
Make sure to meet and follow below prerequisites during planning the upgrade or patching of SQL Server Availability Group Instances.
- Review the Hardware and software requirements for latest SQL Server version on which you are planning to upgrade.
- Make sure your application is supported on the latest version of SQL Server on which we are upgrading.
- Make sure you can directly upgrade to the latest version of SQL Server 2017. If you are running with SQL Server 2005, you cannot go directly to SQL Server 2017. You must upgrade SQL Server 2005 to SQL Server 2012, 14 or 2016 before moving to latest version SQL Server 2017.
- Choose Upgrade approach. As we know, there are three approaches to perform any SQL Server upgrade. In-place upgrade, Side by side upgrade and Rolling upgrade. As we are planning to upgrade AOAG instance so we will choose Rolling Upgrade method. Below are the details of other upgrade methods.
- In-Place Upgrade: SQL Server setup program upgrades the existing SQL Server installation by replacing the existing SQL Server bits with the new SQL Server bits and then upgrades each of the system and user databases. This method is frequently used in a development environment without a high-availability (HA) configuration or a non-mission critical production environment that can tolerate downtime and that is running on a recent hardware and software.
- Side by Side Upgrade: We build new machine and install all required software keeping source system running separately and then migrate all databases from source to this newly build machine. This method is effective in minimizing the downtime or in case if you need to roll back the upgrade.
- Rolling Upgrade: This method is used if your SQL Server Instances are required to upgrade in particular sequence. We use this approach if SQL Server is configured with Always on availability group, Database Mirroring, Log Shipping, Replication, Failover Cluster Instances, SQL Server Reporting Scale-out environment.
- Develop a rollback plan. Executing this plan will enable you to restore your original environment if you need to rollback.
- Plan to test the upgrade on Lower life cycle first before deploying on productions.
- Identify your databases if they are involved in Change Data Capture or Replication then we need to perform some addition activities. Have a look into last section of this article before performing the upgrades.
Upgrade or Patch AOAG Instances – Planning
Here, I am explaining how to plan SQL Server Availability Group instance patching or upgrade to the newer version. Have a look at below points.
- Check database integrity on every availability databases that we are going to upgrade. Run DBCC CHECKDB to make sure there is no corruption on databases.
- Run Full database backups for all your databases to secure a copy in case anything goes wrong we need it to restore databases.
- Make sure Always on Availability Group is working fine and in Healthy state. You can test a manual failover whether it is working fine or not. If any issue during failover or AOAG not in healthy state, you must fix it before going with the upgrade.
- Disable the backup job from the system that is going to upgrade (current secondary replica) and make sure to run it from the server that is online and acting as primary replica at the time upgrade will process. You can also change the Backup Preferences of AOAG to run backups always from primary replica during this upgrade.
- The sequence will be always upgrade the remote secondary replica instances first, then local secondary replica instances next, and the primary replica instance last. If you upgrade the primary replica instance without failing over the Always on availability Group to an upgraded instance with a secondary replica then client applications may suffer extended downtime during the upgrade on the primary replica instance. So better to follow the sequence to reduce downtime.
- Always failover the AOAG to a synchronous-commit secondary replica instance. If you fail over to an asynchronous-commit secondary replica instance, you might lose some of your data.
- Make sure that synchronization state of the failover target is SYNCHRONIZED during any failover of Availability Group.
Upgrading or Patching Always on Availability group Instances
Now you have planned and understand the rolling upgrade methodology and the upgrade process of AOAG configured SQL Server Instances. Here, i will explain upgrade or patching of availability group instances into two sections. One has multiple secondary replicas and another have only one secondary replica that also in remote location for DR purpose.
If Availability Group has Local and Remote Secondary Replicas
Here, I am giving a standard process to perform the upgrade on a configuration that has local secondary replicas as well as remote secondary replicas for DR solution. You can consider it as four replica environments where one replica is hosted on local datacenter and remaining other replicas are hosted on one or multiple remote sites for DR purpose. I have illustrated this configuration in below screenshot where we have one local replica and two remote secondary replicas.
Let’s start with upgrade process considering above scenario.
- Verify you have full database backup for all availability databases. If you don’t have full backups, make sure to run it before this upgrade to protect your data.
- Now, change automatic failover on synchronous-commit replicas to manual (between replica A and B) to prevent any accidently failovers during upgrade process.
- Test a manual failover and make sure that it is working fine.
- Now upgrade all remote secondary replica instances (Replica C and D) running with asynchronous-commit secondary replicas.
- Upgrade the all local replica secondary instances (Replica B) that are not currently running the primary replica.
- Once all secondary replicas have been upgraded and you left out only with primary replica to upgrade then manually fail over the AG to a local synchronous-commit secondary replica. Here, Replica B will be acting as primary replica now.
- Upgrade or update the local replica instance that formerly hosted the primary replica (Replica A).
- Verify SQL Server version on all replicas to make sure every instance is upgraded. Failback current primary replica from replica B to A that was primary replica initially.
- Now, configure automatic failover partners from manual failover (between replica A and B) as we did in step 2 to prevent unwanted failover during upgrade process.
- Perform a failover testing on upgraded instances to make sure everything is working fine.
If Availability Group has only One Secondary Replica for DR solution
If you have configured Availability group only to achieve DR solution having single remote secondary replica with asynchronous-commit mode then you need to follow little different approach for upgrade or apply patches.
You can follow below step by step method to upgrade or Patch your AOAG instances that has only one secondary replica that also on remote site for DR purpose.
- Verify you have full database backups to secure your data.
- Upgrade your remote secondary replica that is hosted for DR.
- Once remote secondary replica is upgraded or Patched, make sure to change the data transfer mode from asynchronous-commit to synchronous-commit to prevent any potential data loss during failover. Do not initiate failover until synchronization state will become SYNCHRONIZED.
- Initiate the failover once data is synchronized between both replicas. And once failover will be done again change the data transfer more to asynchronous-commit mode to avoid any data latency issue to application users.
- Upgrade the current secondary replica (Previous Primary Replica).
- Once both replicas will be upgraded, you can again change the data transfer mode from asynchronous-commit to synchronous-commit to prevent any potential data loss during failover. Do not initiate failover until synchronization state will become SYNCHRONIZED.
- Failover the AOAG to remote secondary replica and change the data transfer mode to asynchronous-commit.
- Verify details about SQL Server version.
Additional steps for CDC (change data capture) and Replication
I have described in point no 7 of Prerequisite section to identify databases that are participating in change data capture or in Replication. I have mentioned that because we need to take some additional steps for such databases post upgrade or patching activity.
- Upgrade each secondary replica as described in above sections.
- After all secondary replicas have been upgraded, fail over the AG to an upgraded instance.
- Execute below T-SQL statement on the instance that hosts the primary replica:
- Upgrade SQL Server instance that was originally configured as primary replica.
I hope you like this article. Please Like, Comment, Share & Subscribe to this website to get new articles directly into your inbox. You can also follow our Facebook page and Twitter handle to get latest updates.
- Fix SQL Error 18456: failed to open the explicitly specified database - September 18, 2021
- Fix Always ON Connection Timeout Error 35206 in SQL Server - July 23, 2021
- How to Enable Preview Features in Azure Data Studio - July 15, 2021