Patching an Availability Group * fully automated * is more complicated then you might think. During the patching process you will have to deal with:
- Failovers.
- AG Dashboards showing warnings.
- Interact with the SCCM client to install and filter the patches.
- Numerous heathchecks inside the process on levels like: Windows Failover Cluster, Always On Availability Groups and SQL Server.
- Sending alerts when things go wrong.
- (Multiple) Reboots during the patching process
- Retry mechanisms when something takes longer than expected.
- Returning to initial state when something goes wrong.
In this post I want to share my experiences during this process at a client, where I fully automated this patching process using Powershell. I will go through each step and share the code. Ultimately I will share the complete solution on my github.
This post will act as a wrapper to individual posts explaining every step in detail. In the end you will have a complete story and code you can use in your own processes.
The process describes the solution for:
- Windows Failover Clustering (2 nodes)
- A Single Availability Group (currently working on support for multiple AG’s)
Let’s get started!
Here is the flow :
(steps will turn into links to individual posts for more detail)
- Trigger the SCCM client to check for new updates
- Test if there is a SMO Connection possible to the server
- Test Windows Failover Cluster Health
- Get the details of the Availability Group (initial state)
- Test Availability Group Health
- Does the server have the primary role
- YES
- Test Availability Group health
- Set failover mode to “Manual” if initial state was “Automatic”
- Perform a failover
- Test Availability Group health
- Check for pending reboot
- Install the pending updates
- Check for pending reboot
- Test Availability Group health
- Set Failover mode back to initial state
- Test Availability Group health
- NO
- Test Availability Group health
- Set failover mode to “Manual” if initial state was “Automatic”
- Check for pending reboot
- Install Pending Updates
- Check for pending reboot
- Test Availability Group health
- Set Failover mode back to initial state
- Test Availability Group health
- YES
I hope my guide on Patching SQL Server AG using SCCM and Powershell was helpful—feel free to leave questions in the comments!
If the task still seems a bit daunting, book a call with Data Masterminds. We have the expertise to execute this and other SQL Server management and troubleshooting tasks to save you the headache.