Applying SQL Server & Window patches to AlwaysOn clusters

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

time-for-patching-alwayson

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. The “while” depends per client. 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) – not a must, but you can. That way you know the environment you about to patch is fresh.
    2. Apply SQL patch or Service Pack in 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, that makes sense to do.
    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.

always-on-patching-completed.

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

We have seen some environments, where hostname continuous to be used. In that case, you going to have some problems with outages or app being unreachable until you do your DNS changes or re-deploy connection strings.

How do you failover SQL AlwaysOn?  you have three choices:

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

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

 

Agree? Disagree? Know how to make this better? Comment below.

 

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.