Patching SQL Server AG using SCCM and Powershell

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

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.