Upgrade or Patch SQL Server Failover Cluster Instance Running with Availability Group

BY selventhiran· PUBLISHED JUNE 20, 2018 

I have explained step by step process to upgrade or patch SQL Server instance that hosts an Always on Availability Group (AOAG) to latest version in attached article. Here, I will explain how to upgrade or patch SQL Server failover cluster instance running with Always on Availability Group. We should plan and execute such upgrades carefully to avoid any risk during execution. The process to upgrade or patch SQL Server cluster instances running with AOAG is quite similar.

NOTE: Always test any solution in Lower life cycle environment before deploying it to Production.

Points to Remember

1. I have described steps to upgrade or patch SQL Server failover cluster instance having always on availability group. Do not use these steps while upgrading or patching Windows clustering or standalone SQL Server instances running with Always on Availability groups. If you have AOAG configured on standalone SQL Server instances then read attached article.
2. During the upgrade process, a secondary replica will not be available for failover or for read-only operations, and after the upgrade, it may take some time for the secondary replica to catch up with the primary replica node depending upon the volume of activity on the primary replica node (so expect high network traffic).
3. 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.
4. Always first upgrade secondary replica then primary replica because 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.

Planning 

We should be very careful while planning upgrade or Patching for your SQL Server Failover cluster instances and if your SQL Server cluster instances are running with availability groups then you need to be more careful considering its complex configuration. Here, i have given multiple steps that you need to plan during patching any AOAG cluster instance.

1. 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.
2. Identify your databases if they are involved in Change Data Capture or Replication then we need to perform some additional activities. These additional steps are given later in this article.
3. Run DBCC CHECKDB to make sure there is no corruption on databases hosted on these cluster instances.
4. You must run Full database backups for all your databases to secure a copy in case anything goes wrong and we need it to restore databases.
5. Make sure Always on Availability Group is working fine and in Healthy state. Fix the issues and make your AOAG configuration in healthy state before going with the upgrade.
6. 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.
7. No replicas are readable or available for backups during a version upgrade whereas during a non-version upgrade you can configure automated backups to run on secondary replicas prior to upgrading the primary replica.
8. 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. We will be using rolling upgrade process to apply patch or upgrade SQL Server cluster instances. This approach 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.
9. Make sure that synchronization state of the failover target is SYNCHRONIZED during any failover of Availability Group.
10. Develop a rollback plan. Executing this plan will enable you to restore your original environment if you need to rollback.
11. Plan to test the upgrade on Lower life cycle first before deploying on productions.

Upgrade or Patch SQL Server Failover Cluster Instance running with Availability Group

Here, I will explain how to upgrade or patch SQL Server failover cluster Instance running with Always on Availability group. Let me describe the cluster setup first for this upgrade.

• We have four database servers that are hosted in two data centers. Two nodes in each data centers.
• All four nodes are part of same windows cluster group. two set of storage have been shared between two nodes to install SQL Server failover cluster.
• Two SQL Server Failover Cluster Instances have been installed on these nodes of each data centers. One SQL Server failover cluster instance named FCIA is installed in data center 1 and another SQL Server cluster instance named FCIBis hosted in data center 2.
• We have configured Availability Group between these two SQL Server failover cluster instances.
• Each SQL Server cluster instance has two nodes. One active and one passive node in their respective data centers. Although active node in data center 2 is not active for connections but this will work as primary in case AOAG failover.
• Databases are hosted on shared storage that is connected to their respective nodes in their data centers. You can see this configuration depicted in below image.

Now we have to upgrade or patch this AOAG cluster instance configuration. Keep reading this article to understand step by step process to patch such complex AOAG configuration.

We can see above SQL Server cluster instances have Always on Availability Group so we will follow rolling upgrade process to perform upgrade or patch on these SQL Server cluster instances. The sequence to upgrade SQL Server cluster instances having AOAG configuration is to go from passive to active. We will upgrade the inactive nodes before upgrading the active nodes. Have a look at below step by step process.

1. First upgrade or patch passive node of SQL Server cluster instance FCIB i.e. Node 4.
2. Once you will update node 4 of above image configuration then next step is to failover second SQL Server cluster instance FCIB to upgraded instance Node 4 that is hosted in data center 2.
3. Once you performed failover for cluster instance FCIB to node 4 then next step is to patch node 3 that will be in passive state of this inactive SQL Server cluster instance. You can failback SQL Server cluster instance FCIB to node 3 again post patching. This failback is an optional step and not mandatory. Now you have applied latest patch level or upgraded your SQL Server cluster instance FCIB hosted in two cluster nodes in data center 2. Next, we will follow same process to apply patches in data center 1.
4. Apply patch on SQL Server cluster node 2 or upgrade SQL Server cluster node installed on node 2 machine as it’s passive node of SQL Server cluster instance FCIA.
5. Once you have upgraded or patched node 2 in data center 1 next we will failover SQL Server cluster instance FCIA to node 2. Now node 2 will run active instance of SQL Server cluster setup. But If you have enabled change data capture feature or replication configured on your SQL Server cluster instance then you should run few additional steps as described here. Once you upgraded or patched all secondary replicas then fail over the Availability Group to an upgraded instance. Now run below command on your primary replica.