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.

Now lets dive in and do this Step by Step

1. Check the SQL Backups. 
   1. We need to have 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. Presume it’s Node2.
3. Move all Roles/Resources out of Node2 (Shall be already empty)
4. Pause Node2 on FCI
   
5. Install the SQL patches on Node2
6. Install the Win updates on Node2 (if applicable)
7. Reboot VM/Node2
8. Resume Node2 in FCI
   
9. Failover the Roles/Resources to Node2 – It will be the new Active now
10. Double check all resources are Online, check SQL server if all databases are back Online, try run a few selects on any database to verify all working.
11. Pause Node1 on FCI
    
12. Install the SQL patches on Node1
13. Install the Win updates on Node1  (if applicable)
14. Reboot the VM/Node1
15. Resume Node1 in FCI
    
16. Failback SQL instance to Node1 (If it was the primary node before the patching)
17. Double check all resources are Online, check SQL Server if all databases are back are Online, try running a few SELECT queries on any database to verify that they are all working.

Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.