Defining the prerequisites before you start patching is extremely important.  I'm not talking about making sure your Ansible control node is set up and testing access to your SQL Server AG replicas (very important as well, but a different subject).  

Nope, I'm talking about making sure that the exe file you're copying to each host is validated and it's the one you expect.  Also, making sure your replicas are compatible with the patch you're about to install.  These are the kinds of tasks we're going to perform using the prerequisites.yml file.  Now, you may have additional prerequisite tasks you'd like to automate (like making sure there are recent backups, integrity checks have all passed recently, etc), but I've limited the number of tasks to keep the example simple.  

Think of this as a template you can use and tweak later for your specific use case.

By the end of this post, you'll have built:

• defaults/main.yml
• tasks/main.yml - We'll start this one, but it won't be complete until the end.
• tasks/prerequisites.yml
• tasks/identify_primary.yml
• files/get_sqlversions.ps1

defaults/main.yml

The default.yml file will hold all of the default values for the role variables.  But remember, these can be overridden and are very low on Ansible's variable precedence order.  I use VS Code to open up the defaults/main.yml file to make things easier.  If you've installed Ansible on WSL, then connect to WSL using VS Code, open the sql_ag_patch folder, and then defaults/main.yml.  I've left a few example values in place for the defaults.

---
# defaults file for sql_ag_patch

# Where to get the patch.  I've used a file share.
sql_patch_source: "\\\\SRV1.SANDBOX.LOCAL\\SQL_Updates\\SQL2022\\"
sql_patch_filename: "SQLServer2022-KB5050771-x64.exe"
sql_patch_checksum: "A7C178021008D6C8A081BEBBE576F4EEB845896DD7220730B046CAE0619FC2B5"

# CU 18 "SQLServer2022-KB5050771-x64.exe" # "SQLServer2022-KB5048038-x64.exe" CU17
# "A7C178021008D6C8A081BEBBE576F4EEB845896DD7220730B046CAE0619FC2B5" #CU18 "1B3F57691F8A7B9950CB1F05EAF18E87B3275378FF6A0386E6A017E1543A7880" CU17 
# use powershell get-filehash to get the checksum

# Arguments to run with the patch installer (silent, accept license, etc.)  You can also specify which instance to update.
# sql_instance_name_to_patch is a fact which is set in the prerequisites.yml file.
sql_patch_args: "/quiet /IAcceptSQLServerLicenseTerms /Action=Patch /InstanceName={{ sql_instance_name_to_patch }}"

# The name of the Availability Group
ag_name: "SANDBOXAG"

# Optional: Whether to fail back to the original primary after patching
sql_ag_failback: true

# Optional: Remove SqlServer module.
remove_sqlserver_module: true

# Optional: Reboot secondary
reboot_secondary: true

# Time to wait for reboots (in seconds) - ignored if reboot_secondary is false
sql_reboot_timeout: 1800

# Temp folder to copy the update to on each host
sql_patch_temp_folder: "C:\\temp\\"

# Compare existing version to expected after applying the patch.
desired_sql_version: "16.0.4185" # "16.0.4175" # "16.0.4185"

tasks/main.yml

We'll go ahead and modify the tasks/main.yml file and add the first two tasks.  We'll add additional tasks throughout this series.

---
# tasks file for sql_ag_patch
- name: Prerequisites
  ansible.builtin.include_tasks: prerequisites.yml
  tags: prereq

- name: Identify Primary node
  ansible.builtin.include_tasks: identify_primary.yml
  tags: get_primary

tasks/prerequisites.yml

This file will contain all of the prerequisite tasks that need to be completed before installing the patch.  As you review, think about your unique environment and what you might add here.  Checking for backups, a successful integrity check within the past X days, looking for long-running jobs, pending reboots, etc.  We're going to leverage the get_sqlversions.ps1 file to retrieve the SQL Server version. 

This role will use this file multiple times, here and in the post-validation phase. Spend some time looking over this section and really understand which Ansible modules are being used and why.  Especially Ansible's assert module. 

We're grouping two tasks by leveraging Ansible's block functionality.  The first task in the block will use the win_stat module to get the checksum of the patch file.  Then, the checksum is compared with the checksum we set in the default.yml file.  Next, we'll use Ansible's rescue section and fail the play if the checksums do not match.  If the checksum does match, then the next task runs.

---
- name: Verify SHA256 checksum of exe
  block:
    - name: Get SHA256 checksum of exe
      ansible.windows.win_stat:
        path: "{{ sql_patch_source }}{{ sql_patch_filename }}"
        get_checksum: true
        checksum_algorithm: sha256
      register: sha256_checksum

    - name: Check if checksum matches
      ansible.builtin.assert:
        that:
          - sha256_checksum.stat.checksum | lower == sql_patch_checksum | lower
        fail_msg: "Checksum mismatch! Computed: {{ sha256_checksum.stat.checksum | lower }}, Expected: {{ sql_patch_checksum | lower }}"
  rescue:
    - name: Fail the playbook explicitly
      ansible.builtin.fail:
        msg: "Patch checksum verification failed. Aborting installation!"

- name: Copy SqlServer module to target
  ansible.windows.win_copy:
    src: "{{ item }}"
    dest: "C:\\Program Files\\WindowsPowerShell\\Modules\\"
    remote_src: false
  loop:
    - "../SqlServer"

# fact building.  Facts are used to ensure idemotency is enforced.
- name: Set the server_instance variable conditionally
  ansible.builtin.set_fact:
    server_instance: "{% if 'DEFAULT' in sql_instance_name %}{{ ansible_hostname }}{% else %}{{ ansible_hostname }}\\{{ sql_instance_name }}{% endif %}"

- name: Get current SQL Server version
  ansible.windows.win_powershell:
    script: "{{ lookup('file', 'get_sqlversion.ps1') }}"
    parameters:
      ServerInstance: "{{ server_instance }}"
  register: sql_version_info

- name: Set a fact for the version
  ansible.builtin.set_fact:
    current_sql_version: "{{ sql_version_info.output[0] }}"

- name: Compare SQL Server Versions
  ansible.builtin.set_fact:
    is_patch_needed: >-
      {{
        (current_sql_version.split('.')[0] == desired_sql_version.split('.')[0])
        and (current_sql_version.split('.') | map('int') | list < desired_sql_version.split('.') | map('int') | list)
      }}

- name: Return is patch needed fact
  ansible.builtin.debug:
    msg: "Is patch needed: {{ is_patch_needed }}"

- name: Assert SQL Server version is valid for patching
  ansible.builtin.assert:
    that:
      - current_sql_version.split('.') | map('int') | list <= desired_sql_version.split('.') | map('int') | list
    fail_msg: >-
      Patching is not possible. SQL Server version mismatch detected:
      - **Current Version:** {{ current_sql_version }}
      - **Desired Version:** {{ desired_sql_version }}
      {% if current_sql_version.split('.')[0] != desired_sql_version.split('.')[0] %}
      - **Major version mismatch!** The patch cannot be applied across major SQL Server versions.
      {% else %}
      - **Patch version too high!** The patch is designed for an older version than what is installed.
      {% endif %}
    success_msg: "SQL Server Major version check passed. Proceeding with patching."

- name: Initialize fact for new_primary with default
  ansible.builtin.set_fact:
    new_primary: "default_value"

# Most of the PowerShell commandlets used in this role use PowerShell paths which require DEFAULT as the instance name for default instances.
# However, the patch exe expects the instance name to be MSSQLSERVER if we're patching the default instance.  So we need another fact to handle this.
- name: Set a fact for the instance name to patch
  ansible.builtin.set_fact:
    sql_instance_name_to_patch: "{% if 'DEFAULT' in sql_instance_name %}MSSQLSERVER{% else %}{{ sql_instance_name }}{% endif %}"

# Only run this block if the patch is needed.
- name: Copy update to managed node
  when:
    - is_patch_needed
  block:
    - name: Create temp folder on target
      ansible.windows.win_file:
        path: "{{ sql_patch_temp_folder }}"
        state: directory

    - name: Create the updates folder in temp on target
      ansible.windows.win_file:
        path: "{{ sql_patch_temp_folder }}\\updates"
        state: directory

    - name: Copy patch
      ansible.windows.win_copy:
        src: "{{ sql_patch_source }}{{ sql_patch_filename }}"
        dest: "{{ sql_patch_temp_folder }}\\updates"
        remote_src: true

tasks/identify_primary.yml

I mentioned in the previous post that we're going to avoid hard-coding or even adding a variable to specify the primary replica.  Instead, we'll leverage this task file to determine, at run time, which of our replicas is the primary.  We're using the win_powershell module again in this file, but notice that the script is used directly instead of using Ansible's lookup plugin.  We could have moved this script to a separate .ps1 file, but I wanted to show the flexibility Ansible has when running PowerShell scripts.  Plus, this specific PowerShell script is only used once (vs the get_sqlversions.ps1 script you'll build next). You could move the script to its own file or leave it as is.  It's up to you.

---
# the SqlServer powershell module is needed on the managed machines
- name: Identify the current AG primary
  ansible.windows.win_powershell:
    script: |
      [CmdletBinding()]
      param(
        [Parameter(Mandatory=$true)]
        [string]$SqlInstanceName,

        [Parameter(Mandatory=$true)]
        [string]$AgName
      )
      $ErrorActionPreference = 'Stop'
      import-module sqlserver
      $AGPath = "SQLSERVER:\SQL\LOCALHOST\$SqlInstanceName\AvailabilityGroups\"
      dir -path $AGPath | Where-Object {$_.Name -eq "$AgName" } | select-object -ExpandProperty PrimaryReplicaServerName

      #this never changes a host only reads
      $Ansible.Changed = $false
    parameters:
      SqlInstanceName: "{{ sql_instance_name }}"
      AgName: "{{ ag_name }}"
  register: ag_primary_info

- name: Set a fact with the primary hostname
  ansible.builtin.set_fact:
    ag_current_primary: "{{ ag_primary_info.output[0] | trim }}"

files/get_sqlversions.ps1

Notice we've switched directories here.  Remember that the files directory contains static files to be deployed.  However, you'll see that these files don't actually get deployed.  We're using Ansible's lookup plugin to read them and then run the script instead of copying them to each replica.  Less to clean up in the end.

[CmdletBinding()]
      param(
        [Parameter(Mandatory=$true)]
        [string]$ServerInstance
      )
      import-module SqlServer
      (Get-SqlInstance -ServerInstance "$ServerInstance" -ErrorAction Stop ).Version.ToString()

      $Ansible.Changed = $false

Conclusion:

Between now and part 4, you have a bit of homework to do.  The purpose of this series is not to hand over code to automate a very common task for SQL Server DBAs, but to provide examples you can use as you learn how powerful Ansible is, and why it should be in your toolbox.  This example role goes further than using PowerShell or DBATOOLS alone; it'll show you how Ansible can be leveraged to orchestrate the entire process, including failovers, reboots, failbacks, validation, etc, with minimal code.  

If you have questions, drop those in the comments below.  Next up, we'll build the tasks that will perform validation, patching, and changing failover modes.  See you then!

Previous ← SQL Server AG Patching with Ansible: Creating Your Role Structure

Next → SQL Server AG Patching with Ansible: Tasks