Windows patching is usually a topic left for Windows administrators.  But what if, as a DBA, you're tasked with patching both the OS and SQL Server?  That scenario is common in smaller or cross-functional IT teams where boundaries between roles aren't so clear.  DBAs can't ignore OS patching for servers that host critical SQL Server instances—ignoring those updates can lead to security vulnerabilities, performance degradation, and potential non-compliance with organizational policies.

So, how do you automate Windows patching and your SQL Server updates?  The key is to select and leverage the right tools—both to handle Windows patches and to coordinate the downtime or failover processes that keep your environment stable.

The Challenge of Patching

Before we jump into tools, let's acknowledge why patching can be a challenge for DBAs:

1. Database Uptime Requirements: SQL Server databases often run mission-critical applications.  Scheduling downtime or even a failover can be tricky.
2. Cluster Awareness: Many SQL Server instances run on clustered environments or include Always On Availability Groups.  Properly orchestrating these failovers and ensuring minimal disruption to applications can be intricate.
3. Dependency on Other Services: End-user applications and web front-ends typically rely on the databases that you support.  Stopping and starting these services before and after patching is critical to avoid data corruption or user disruption.
4. Performance Impact: Even if a patch installation is quick, post-patch reboots or instance restarts can degrade performance temporarily, so you need to plan accordingly (the fewer restarts and failovers, the better).

Let's compare two tools that can aid with Windows and SQL Server patching.

WSUS vs. Ansible

WSUS (Windows Server Update Services)

• What it Does Well:
  • WSUS is Microsoft's native patch management solution that allows administrators to manage the distribution of updates released through Microsoft Update.
  • Offers a straightforward way to approve or decline specific patches and updates.
  • Excellent for environments heavily based on Windows, providing a central repository for Windows updates.
• Where it Falls Short for DBAs:
  • WSUS alone doesn't inherently automate the steps needed to handle application downtime or cluster failovers.  It focuses on Windows updates themselves, not the orchestration of application-level tasks.  
  • Lacks the built-in capability to manage configurations across *nix systems or to integrate easily with DevOps workflows where you might also be spinning up containers, ephemeral environments, etc.
  • Scheduling or chaining tasks—like stopping a web application, running the patch, verifying status, and then restarting services often require writing additional scripts or using other tools.  It's up to you to stop/start dependent services or notify users when maintenance is starting.  Needed to put up a maintenance page?  WSUS can't do that for you.

Ansible

• What it Does Well:
  • Agentless: Ansible connects over SSH (or WinRM for Windows) without requiring agents on each host, making it simpler to get started.
  • Configuration Management and Orchestration: You can use playbooks to declare not only the desired configuration state of a system but also the workflow for rolling out patches and orchestrating application downtime.
  • Scalability and Flexibility: Ansible can simultaneously handle tasks across Linux, Windows, network devices, and more.  This cross-platform approach is invaluable if you're managing a hybrid environment.
  • Idempotency: If you run an Ansible playbook multiple times, it will bring systems into the desired state without redoing actions unnecessarily.
• Potential Challenges:
  •  Requires learning YAML and Ansible's playbook structure (you can learn about this in the Ansible for SQL Server DBA's: Level 1 course).
  • Some complexity in writing custom playbooks for patching workflows if you're new to automation or DevOps practices.
  • Must configure WinRM (or SSH for Windows Server 2025), which can be a barrier if you're more accustomed to GUI-driven tools like WSUS.  However, Ansible Automation Platform does provide a Web-UI which can be leveraged for Ansible automation.

Using Ansible for Windows Patching 

We could certainly dive into a more elaborate playbook or role and see how to handle Failover Cluster Instance (FCI) or Always On Availability Group failover while patching.  However, I want to keep this post simple, especially if you're just getting started with Ansible. 

Let's begin with the basics: identifying which modules to use to patch Windows and the variables we need to specify.

Basic Windows Patching

Below is a sample playbook you could use to patch Windows hosts.  We'll break down what each task does, the modules involved, and how to run it.

---
- name: Patch Windows
  hosts: all
  gather_facts: true

  tasks:
    - name: Check var_category variable
      ansible.builtin.assert:
        that:
          - var_category in ["SecurityUpdates", "CriticalUpdates", "UpdateRollups", "*"]
        fail_msg: >-
          var_category must be one of 'SecurityUpdates', 'CriticalUpdates', 'UpdateRollups' or '*.'
          It is set to {{ var_category }}.
        success_msg: "patch category is set to {{ var_category }}."

    - name: Check var_state variable
      ansible.builtin.assert:
        that:
          - var_state in ["installed", "searched", "downloaded"]
        fail_msg: >-
          var_state must be one of 'installed', 'searched', or 'downloaded.'
          It is set to {{ var_state }}.
        success_msg: "patch state is set to {{ var_state }}."

    - name: Patch Windows hosts
      ansible.windows.win_updates:
        category_names: "{{ var_category }}"
        log_path: "{{ log_dir }}"
        skip_optional: true
        state: "{{ var_state }}"
        reboot: true

Breaking it down:

• hosts: all
  • This playbook is set to run against all hosts in your inventory that are part of the group you select at runtime.  You can refine this to a specific group (e.g., windows_servers) or even a single host if you are just testing.
• gather_facts: true
  • This ensures Ansible gathers information (facts) about the target Windows systems—OS Version, memory, etc.  While not strictly required for patching, it can be helpful for logging or conditional logic later on.
• ansible.builtin.assert Tasks
  • The first two tasks use the assert module from Ansible's built-in collection to validate variables before proceeding.
  • var_category: Must be one of SecurityUpdates, CriticalUpdates, UpdateRollups, or *.  * would install updates from all categories.  
  • var_state: Must be one of installed, searched, or downloaded.
  • These checks help you avoid unintended or incorrect values when scheduling or running this playbook (making the workflow more robust).
• ansible.windows.win_updates
  
  • The primary mdoule used to patch Windows hosts.
  • category_names: Specifies which category of updates to install.  You can limit to security, critical, or rollup updates—or use * to include all updates.
  • log_path: Points to the location where logs of the patching process will be stored on the target machine.
  • state: Defines what action to take.  Install, search, or download updates.
  • reboot: If set to true, the module will reboot the system automatically if the updates or system state require it.

Not all parameters of win_updates are used in this playbook.  For example, you have the option to reject or explicitly accept specific updates.  This module also allows you to specify your Windows Update source catalog using the server_selection parameter.  Server selection could be set to use an internal WSUS server where you could then control the approved list of updates without having to define those in your playbook (a combined approach of WSUS and Ansible).

Running This Playbook

You can run this playbook from the command line (on your Ansible control node) with something like:

ansible-playbook playbook_windowsPatching.yml -i inventory_file \
--extra-vars "var_category=* var_state=installed log_dir=C:/temp/ansible_wu.txt" \
-u YourUserName --ask-pass

• -i inventory_file:  Points to your Ansible inventory.
• --extra-vars: Pass the variables from the command line for var_category, var_state, and var_log_dir.

Combining WSUS and Ansible

Even if you already use WSUS for patch management, Ansible can still play a significant role in the overall update process.  In fact, many organizations choose to approve specific patches in WSUS, but then use Ansible to orchestrate the actual installation.  This dual approach leverages the strengths of both tools:

Patch Approval in WSUS

The system administrator can review and approve Windows patches in WSUS. 

This ensures that only authorized updates are available for download by client machines, fulfilling internal compliance and change management policies.

Orchestration in Ansible

Ansible can direct Windows servers to install the approved updates from WSUS rather than reaching out to Microsoft Update or other external sources.  This eliminates the manual step of logging into each machine to trigger downloads and installations from WSUS.

The win_updates module, for example, can be configured to look for patches via WSUS.  You just need to ensure the WSUS settings (GPO or registry-based) are properly configured on the target hosts so that they point to your WSUS server.

Coordinating Downtime, Services, and Failovers

While WSUS ensures patch availability and compliance, it doesn't orchestrate the sequence of stopping applications, failing over a SQL Server cluster, or verifying system health post-update.  With Ansible, you can build a playbook or role to handle these steps before and after the WSUS-driven updates are installed.

Centralized Logging and Reporting

WSUS offers patch approval and distribution reports, while Ansible can provide detailed logs of each step in the orchestration process.  Helpful for auditing.  

This combined log trail gives a full picture of both patch approval (WSUS) and application (Ansible) status.

Overall, this WSUS + Ansible strategy gives you the best of both worlds: a controlled patch repository (WSUS) plus powerful automation and orchestration (Ansible).  It's a flexible model that can easily scale as you incorporate more advanced workflows like cluster failover or multi-application coordination without losing the granular patch selection and approval process provided by WSUS.

Where to Go From Here

Advanced Orchestration

Once you're comfortable with basic patching, you can enhance your playbook to coordinate cluster failovers (for SQL Server FCI or AG), ensure minimal downtime for critical databases, or stop/start services that rely on SQL Server.  Better yet, create a role that contains these reusable tasks and simplify patch orchestration even further.

Application Aware

If your SQL Server is hosting databases for a critical application, you might add tasks that notify users (through email, slack, maintenance page, etc.), pause the web front-end and middle application tiers, or lock out new sessions before applying patches.  Need to verify the latest backups were successful before proceeding?  You could do that as well using Ansible.

Windows + Linux Hybrid

Ansible is cross-platform, so you can also manage Linux system patching within the same framework—handy if your environment is a mix of Windows and Linux hosts.

Add Testing/Validation Steps

Consider adding tasks after the patch to run simple checks—like verifying the SQL Server instance is back up, running a quick connectivity test, etc.

Conclusion

The simple playbook example demonstrates how Ansible can handle basic Windows patching with just a few tasks.  Even if you're primarily a DBA, leveraging Ansible for OS-level tasks lets you maintain more control and consistency in your environment (using one tool).  This ensures that both Windows and SQL Server patches are applied seamlessly and safely.

If you have any questions or want to share how you're using Ansible for patching in your environment, leave a comment below or reach out.  I'd love to hear how it's going for you!