How to apply Windows or SQL Server patches to failover MS SQL Server cluster with AlwaysOn – Step by step
SQL cluster patching best practices
- Apply patches to lower environment servers first. This is DEV, QA, UA, etc. Use the lowest environment in the release cycle .
- 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.
- 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.
- Once Production environment is reached, the way patches are deployed on SQL Server AlwaysOn environments is this:
- 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.
- Apply SQL patch or Service Pack in the SQL server.
- 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.
- Failover SQL AlwaysOn workload to the inactive node (wait failover period).
- Now do same process on that node.
- Repeat until all nodes are complete.
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:
- Using SSMS Wizard.
- Use T-SQL
- 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.
