SQL Server AlwaysOn Cluster Patching Window OS: Step-by-Step Guide

How to apply Windows or SQL Server patches to failover MS SQL Server cluster with AlwaysOn - Step by Step

High-level SQL cluster patching: Best practices

1. Apply patches to lower environment servers first. This is DEV, QA, UA, etc. Use the lowest environment in the release cycle.
2. Let the new change "burn in" for a while. What a "while" means, will depend on your situation. Some wait a week, some wait a month. The duration of "burn-in" depends on how rigorous and active QA testing is on those lower environment servers. If there is a ton of automated testing running daily, or there is an army of Quality Assurance staff - then 1 week may be sufficient.
3. Often clients will deploy a change to DEV, wait two weeks. Then deploy the change to the next environment, wait two weeks. Do this until Production environment is reached.
4. Once Production environment is reached, the way patches are deployed on SQL Server AlwaysOn environments is this:
   1. Reboot inactive node (AlwaysOn failover cluster instance FCI) - not a must, but you can. That way you know the environment you about to patch is fresh.
   2. Apply SQL Server Cumulative Update or Service Pack to the SQL Server.
   3. Reboot the node again. Reboot is not a must. But since we about to point all SQL production workload to here, this steps makes a lot of sense.
   4. Failover SQL AlwaysOn workload to the inactive node (wait failover period).
   5. Now do same process on that node.
   6. Repeat until all nodes are complete.

One caveat. If you using AlwaysOn in SQL Server properly, you have pointed all apps to use AlwaysOn Listener.

We have seen some environments, where hostname, and not AlwaysOn Listener or ClusterName is continuous to be used. THIS IS BAD. You simply going to have some problems with outages or application being unreachable until you do your DNS changes or re-deploy connection strings. And you aren't using AlwaysOn it is supposed to be used.

How do you failover SQL AlwaysOn?

You have three choices:

1. SSMS Wizard
2. T-SQL
3. PowerShell

This is how you patch your High Availability Windows Fail-over clusters (WFC) running MS SQL Server with AlwaysOn.

Step-by-step guide

Now let's dive in and do this step by step:

1. Check the SQL Backups
   1. We need to have a backup available
   2. Ensure backup jobs have been running without issues
   3. Do extra DB backup for safety
2. Start with the passive node first. For this example, let's assume it’s Node2
3. Move all Roles/Resources out of Node2 (it should already be empty)

1. Pause Node2 on FCI

1. Install the SQL patches on Node2
2. Install the Win updates on Node2 (if applicable)
3. Reboot VM/Node2
4. Resume Node2 in FCI

1. Failback SQL instance to Node1 (If it was the primary node before the patching)
2. Double check all resources are Online
3. Check SQL Server that all databases are back Online, try running a few SELECT queries on any database to verify that they are all working
4. Pause Node1 on FCI

1. Install the SQL patches on Node1
2. Install the Win updates on Node1 (if applicable)
3. Reboot the VM/Node1
4. Resume Node1 in FCI

1. Failback SQL instance to Node1 (If it was the primary node before the patching)
2. Double check all resources are Online
3. Check SQL Server that all databases are back Online, try running a few SELECT queries on any database to verify that they are all working

That's how to do SQL Server failover cluster and AlwaysOn patching properly with the smallest outage window.

Patching windows like this are routine for Red9's SQL Server managed services team: we plan, patch, and verify AlwaysOn clusters on an approved schedule, so your databases stay current without the 2 a.m. scramble.