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)

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.

3 thoughts to “Patching SQL Server AG using SCCM and Powershell”

  1. Hi,

    I have been looking for way to find out “Does the server have the primary role” using powershell. Can you kindly guide how can we find out using powershell which is the active node and passive in the windows failover cluster?

Leave a Reply

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