Patching SQL Server on AlwaysOn Clusters
How do you apply Windows or SQL Server patches on a failover MS SQL Server cluster running AlwaysOn? Let’s walk through it step by step.
SQL Cluster Patching Best Practices
1. Apply patches to lower environments first. This means DEV, QA, UAT, etc. Use the lowest environment in your release cycle.
2. Let the new change “bake” for a while. That “while” varies by server environment. Some wait a week, some wait a month. How long it needs to bake depends on how thorough and active QA testing is on those lower environments. If there’s a ton of automated testing running every day, or an army of QA staff, a week might be enough.
3. Typically, server admins roll a change out to a test environment and wait two weeks. Then they roll the change out to the next environment and wait another two weeks. They keep doing this until they reach production.
4. Once you reach production, here’s how patches get rolled out on SQL Server AlwaysOn environments:
1. Restart the inactive node (the AlwaysOn secondary instance) — not required, but you can do it. This way you know the environment you’re about to patch is fresh.
2. Apply the SQL Server patch or Service Pack.
3. Restart the server. Restarting isn’t required. But since we’re about to point all SQL production workload to it, it makes sense to do it.
4. Fail over the SQL AlwaysOn service to the inactive server. (Wait out the failover time.)
5. Now do the same process on this node.
Need expert support for SQL Server?
Our senior database team supports SQL Server performance tuning, health checks, migrations, Remote DBA services and urgent operational issues.
6. Repeat until you’ve completed every server.
One warning. If you’re always using AlwaysOn correctly, all your applications should be pointed to use the AlwaysOn listener.
We’ve seen some environments where the hostname is used directly instead. In that case, you’ll run into outages or application-unreachable issues until you make your DNS changes or redeploy the connection strings.
How do you always fail over SQL properly? You have three options:
1. Using the SSMS Wizard.
2. Using T-SQL.
3. Using PowerShell.
That’s how you patch High Availability Windows Failover Clusters (WFC) running MS SQL Server with AlwaysOn.
Related Reading
SQL Server High Availability
Let’s Build the Right HA Architecture for Uninterrupted Access
Aryasoft designs and implements the high availability architecture best suited to your business among solutions like Always On, FCI, and log shipping.
