Ansible By Examples - Simplify SQL Server Patching

Edition: Saturday, July 13th, 2024

On July 9th, 2024, Microsoft released a GDR update for multiple editions of SQL Server.  As a DBA, you may be planning how to install this update in your lower environments (DEV, QA, UAT, etc.), tested, and then deploy to production.

However, you may have also experienced that your organization's systems admins have already deployed this update without you being notified first (if using Microsoft Update).  This can happen if patches are both automatically downloaded and installed.

Today, I'm going to show you a very simple Ansible playbook that can be used for managing updates.  This solution works using Microsoft Update (not Windows Update) or Windows Server Update Services.  It could be modified to allow greater flexibility when managing patch deployment to your SQL Servers.  But, we won't cover that today.

Let's dive in.

Windows Update vs Microsoft Update

There's a difference between the standard Windows Update service and the Microsoft Update service.  To receive additional updates, for other Microsoft products, Microsoft Update must be used.  Let's take a quick look at the two options.

Windows Update:

• Scope: Windows Update is the default service that provides updates specifically for the Windows operating system.  This includes security patches, bug fixes, driver updates, and feature updates.
• Focus: Primarily focuses on the operating system itself.
• Default Setting: Enabled by default on Windows systems to keep the operating system up-to-date.

Microsoft Update:

• Scope: Microsoft Update is an extension of Windows Update.  It provides updates for not only the Windows operating system but also other Microsoft products such as Microsoft Office, SQL Server, Exchange, Visual Studio, and other Microsoft software.
• Focus: Broader scope covering a range of Microsoft products beyond just the operating system.
• Optional: Not enabled by default.  Users need to opt-in to receive updates for the additional Microsoft products.  This can be done using the local security policy or, usually this is the case, using a group policy that enables the Configure Automatic Updates policy.  This policy must enable the "Install updates for other Microsoft products" option.

How do you know which one is enabled?

I'm Glad you asked.  Open Windows Update (on your SQL Server host).  If what you see matches the screenshot below, click View Policies.

Under "Policies set on your device" check if you see "Get updates for other Microsoft products."  If it's there, Microsoft Update is being used.

What is the win_updates module?

The win_updates module is used to manage Windows updates (and Microsoft updates) on target Windows machines.  It allows you to automate checking for, downloading, installing, and managing updates.

Key Features:

• Check for Updates: You can check for available updates from Microsoft Update or other configured update sources.
• Install Updates: Install specific updates or all available updates that meet the specified criteria.  
• Reboot Management: Optionally reboot the system if required by the updates.
• Update Categories: Filter updates by categories such as critical updates, security updates, drivers, and more.

Common Parameters:

• category_names: List of categories to include or exclude when searching for updates (e.g., 'SecurityUpdates', 'CriticalUpdates'). Or you can specify '*' for all categories.  See here for more categories.
• state:  Desired state of the updates.  Can be searched, installed, or downloaded.
• reboot:  Whether to reboot the system if required by the updates.  Can be true or false (default).  This removes the need to use the win_reboot module.
• log_path: Path to a file to log update information.  This directory should exist on the managed host.
• server_selection:  By default, the Microsoft Windows Update catalog is searched.  However, if specified to use 'managed_server', and WSUS has been configured in the environment, a corporate update server may be used instead.  
• accept_list:  This can be a list of KB numbers, update titles, or regular expressions according to PowerShell regex rules.

Example - Installing SQL Server 2022 CU13: July 9, 2024

In this example, I have two SQL Server 2022 instances that have CU13 applied (Build 16.0.4125.3).  

Using the playbook below, we're going to install the latest updates to the two hosts (SRV1 and SRV2).  

Here's the command: ansible-playbook playbook_windowsPatching.yml -u [email protected] --ask-pass

Result:

SQL Server Version:

Environment:

Here's how I have my lab configured.  If you build one to follow along in the examples, be sure to create snapshots first.  This way you can easily reset your environment for the next walkthrough.

• Hardware - Geekom A7 Mini PC Ryzen - 64 GB RAM / 2 TB drive
• Hypervisor - VMware Workstation Pro 17.5 (get this for free if you're using it for personal use - VMware Workstation Pro: Now Available Free for Personal Use)
• Virtual Machines - All built using a single Windows Server 2022 Standard (eval) Hashicorp Packer image and managed with Vagrant.
  • DC1 - Domain Controller (HOMELAB.LOCAL)
  • SRV1 - SQL Server (eventually) - Contains the OS drive and 3 uninitialized NVMe disks.
  • SRV2 - SQL Server (eventually) - Contains the OS drive and 3 uninitialized NVMe disks.
  • SRV3 - Client machine - Windows Subsystem for Linux (Ubuntu) - Ansible Control Node.  Visual Studio Code is installed and used to create the playbooks throughout this series.

Conclusion:

Ansible has a large number of Windows modules available to help manage Windows servers.  Take advantage of these as you continue to build your automation toolbox.  This 15-line playbook can be built upon to handle tasks like:

• Deploying updates to failover clustered instances.
• Availability Groups
• Specific testing hosts
• etc  

You can grab the example code at Ansible-for-SQL-Server-DBAs/Ansible-Windows-Patching

Ansible For SQL Server DBAs (join the wait list) - https://www.automatesql.com/ansible. 

Thanks for reading!  Have a great week!