Over the past several blog posts, I've been talking about patching.  More specifically, patching SQL Server Availability Groups using the rolling update method.  We defined the inventory file, tasks, and built supporting PowerShell scripts along the way.  But, I'd like to back up a bit.  Why would you, as a DBA, ever want to do this?

I've worked numerous weekends, late nights, and holidays performing maintenance throughout my DBA career.  One thing you begin to count on is checklists.  They help to ensure you're on the right track, especially with a multi-step process like patching an Availability Group.  

But what if you miss a step?  What if you failover to the wrong node?  What if a reboot hangs and you don't notice for 20 minutes?  What if this takes hours longer than you planned, cutting even more into family time?

That manual checklist, while necessary, is a liability.  It's static and entirely dependent on your sustained, error-free focus.

What if instead of following 47+ manual steps, you could run:

ansible-playbook playbook_agPatch.yml

What if that single command could intelligently handle the entire rolling patch process?  

After 17+ years of DBA work and countless weekend patching sessions, I built this as an example to solve the exact problems we all face.  I've been there.  For years, I used a checklist and a collection of scripts to make this easier, but it was still a fragmented process.  

Today, I'm thrilled to introduce the sql_ag_patch role–a new component of the automatesql.mssql Ansible collection.

This isn't just a script.  It's your checklist brought to life and an example of real orchestration.  It's a way to automate the entire rolling patch process for Availability Groups.

The sql_ag_patch Role

Instead of just telling you about it, I want to show you.  I recorded a full walkthrough (39 minutes) where I take a four-node test Availability Group and patch it to SQL Server 2022 CU 20 from start to finish with a single Ansible command.  You'll see the playbook:

• Identify the primary and secondary replicas.
• Safely patch the secondaries, including reboots.  The demo performs these in parallel.  However, you can serialize this process by simply adding "serial: 1" to your playbook.
• Perform a controlled, automatic failover to a fully patched secondary.
• Patch the final node (the old primary).
• Verify the entire group is healthy and on the new version.

This role was designed with safety and reliability in mind.

1. It's Idempotent: You can run the same playbook again and again.  If an instance is already patched, Ansible knows to skip it.
2. It's Smart: The role automatically detects the primary and secondary replicas.  No manual checks needed.
3. It's Safe: It changes the failover mode to manual on secondaries before patching to prevent accidental failover.  
4. It's Flexible: You can control whether to fail back to the original primary or leave it as is.  You can skip patching the primary if you need to do that at a later time.

Under the Hood: Key Ansible & PowerShell Techniques

This role doesn't just run commands; it orchestrates a complex workflow using some powerful features of Ansible.  If you're looking to build your own advanced automation, here are a few of the key techniques this role leverages:

• Flexible PowerShell Execution ( ansible.windows.win_powershell ):  This role uses the win_powershell module to run targeted scripts using the SqlServer PowerShell module.  This gives us granular control to perform complex actions like checking AG health ( get_ag_health.ps1 ) or changing failover modes ( change_failover_mode.ps1 ) exactly how we want, capturing the output, and making decisions based on it.
• Built-in Safety Checks ( ansible.builtin.assert ):  How do we know the patch was actually successful?  We don't just hope, we verify.  The assert module is used to validate critical steps.  For example, after a patch and reboot, the role asserts that the new SQL Server version matches the desired_sql_version variable that you defined.  If it doesn't, the playbook stops, preventing it from proceeding with a failed update.  We use this same module to ensure the target instances have a major version which matches the desired_sql_version variable (so we don't attempt to apply a SQL Server 2019 patch to SQL Server 2022 or vice versa, if an incorrect host was added to the inventory file).  Next, assert is used to ensure the target minor version is less than our intended version (no need in trying to apply an older patch).  This module is extremely powerful and I've leveraged it throughout this role to perform various validations.
• Dynamic Orchestration ( set_fact & Host Targeting ): The role needs to perform certain actions only on the primary replica ( like getting the state of all secondaries ).  It does this by first identifying the primary, then saving its instance name with set_fact.  Later, tasks can be targeted to run only on that specific host, or all hosts except the primary, by using a when condition, ensuring the right command runs in the right place at the right time.
• Graceful Reboots ( ansible.windows.win_reboot ): Patching Windows / SQL Server almost always involves a reboot.  Ansible's win_reboot module handles this elegantly.  It initiates the reboot then waits patiently for the server to come back online and respond before moving to the next step, making the entire process seamless.

Conclusion: Next Steps

• See it in action
• See the Code:  The entire collection is open-source.  Check out the repository on GitHub.
• Get the Role: Head over to Ansible Galaxy and download the automatesql.mssql collection to get started.  Be sure to test in non-prod to get your inventory file and variables set correctly.  Understand what it does before running it.

I plan to update this role with new features over time.  If you have any suggestions, let me know.