SQL Server Migrations & Upgrades

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

Updated
4 min read
Written by
Mark Varnas

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

Time for patching AlwaysOn

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.
AlwaysOn patching completed

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.

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Leave a Comment

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜
Explore All Services