In my previous post, we explored the concept of rolling updates for SQL Server Availability Groups and how Ansible can transform this complex process into a streamlined, repeatable operation. We discussed the benefits of automation and outlined a high-level strategy for implementing this approach.

Today, I'm diving deeper into the foundation of any Ansible automation: the inventory file. This critical component tells Ansible which servers to manage and stores information about those servers. For our SQL Server AG patching automation, getting this right is essential.

Understanding Ansible Inventory

At its core, an Ansible inventory is simply a list of managed hosts (your Windows servers hosting SQL Server) and the groups they belong to. Think of it as your "address book" that tells Ansible where to connect and what infrastructure it's working with.

Ansible supports two main formats for inventory files: INI and YAML. For simplicity, we'll focus on the INI format in this post, which is more than sufficient for our needs.

Here's a basic example of what an INI inventory might look like for our SQL AG nodes

[sqlservers]
SQL1.SANDBOX.LOCAL
SQL2.SANDBOX.LOCAL
SQL3.SANDBOX.LOCAL
SQL4.SANDBOX.LOCAL

In this example, we've defined a group called sqlservers containing four SQL Server hosts. Simple, right? But we need more than just hostnames to effectively manage our SQL Server environment. 

Where to Define Variables in Ansible

Variables are what make your Ansible playbooks flexible and reusable across different environments. When it comes to SQL Server AG patching, we need to define variables like SQL instance names, port numbers, and perhaps AG configurations.

Ansible offers several locations for defining these variables, each with its own advantages and disadvantages. Let's explore the options:

A. Inventory File (Host and Group Vars Directly)

What it is: Variables defined directly within the inventory file, either on the host line or in dedicated [group:vars] sections.

[sqlservers]
SQL1.SANDBOX.LOCAL sql_instance_name="DEFAULT" sql_port=1433
SQL2.SANDBOX.LOCAL sql_instance_name="DEFAULT" sql_port=1433
SQL3.SANDBOX.LOCAL sql_instance_name="DEFAULT" sql_port=1433
SQL4.SANDBOX.LOCAL sql_instance_name="INSTA" sql_port=1433

[sqlservers:vars]
ansible_connection=winrm
ansible_winrm_server_cert_validation=ignore
ansible_port=5986
ansible_winrm_transport=kerberos
ansible_winrm_kerberos_delegation=true
ansible_winrm_operation_timeout_sec=60
ansible_winrm_read_timeout_sec=90
ansible_winrm_kinit_mode=managed
ansible_winrm_kinit_cmd=kinit
# availability group name
ag_name="SANDBOXAG"

Advantages:

• Everything is in one place - easy to see host-to-variable relationships
• No additional files to create or manage
• Quick to set up and understand for smaller environments

Disadvantages:

• Can become unwieldy as your variable count increases
• Limited structure for complex data types (lists, dictionaries)
• Not ideal for larger environments with many hosts

B. host_vars Directory

What it is: A directory structure containing YAML files named after your hosts, where each file contains variables specific to that host.   The ".yml" extension is optional by I recommend adding it for intellisense in VS Code.

project/
├── hosts.ini
└── host_vars/
    ├── SQL1.SANDBOX.LOCAL.yml
    ├── SQL2.SANDBOX.LOCAL.yml
    ├── SQL3.SANDBOX.LOCAL.yml
    └── SQL4.SANDBOX.LOCAL.yml

With YAML content like:

# host_vars/SQL1.SANDBOX.LOCAL  
sql_instance_name: DEFAULT
sql_port: 1433

Advantages:

• Excellent organization - each host has its own dedicated file
• Better readability and structure for complex data types
• Works well with version control systems
• Scales well to many hosts with unique configurations

Disadvantages:

• Requires more files to manage
• Slightly more complex initial setup
• Variables are spread across multiple locations

C. group_vars Directory

What it is: Similar to host_vars, but containing files named after your groups instead of individual hosts.

project/
├── hosts.ini
└── group_vars/
    └── sqlservers.yml

With a YAML file like:

# group_vars/sqlservers
ansible_connection: winrm
ansible_winrm_server_cert_validation: ignore
ansible_port: 5986
ansible_winrm_transport: kerberos
ansible_winrm_kerberos_delegation: true
ansible_winrm_operation_timeout_sec: 60
ansible_winrm_read_timeout_sec: 90
ansible_winrm_kinit_mode: managed
ansible_winrm_kinit_cmd: kinit

# availability group name
ag_name: "SANDBOXAG"

Advantages:

• Perfect for variables that apply to entire groups
• Reduces repetition across multiple hosts
• Centralizes common configuration elements

Disadvantages:

• Not ideal for host-specific variables
• Requires thoughtful grouping of hosts

D. Role Defaults (roles/sql_ag_patch/defaults/main.yml)

What it is: Default values defined within an Ansible role structure.  Here are a few of my defaults for this new role.  We'll cover them in detail later on in this series.

# roles/sql_ag_patch/defaults/main.yml

# Where to get the patch
sql_patch_source: "\\\\SRV1.SANDBOX.LOCAL\\SQL_Updates\\SQL2022\\"
sql_patch_filename: "SQLServer2022-KB5050771-x64.exe"
sql_patch_checksum: "A7C178021008D6C8A081BEBBE576F4EEB845896DD7220730B046CAE0619FC2B5"

# 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 }}"

# 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"

Advantages:

• Provides sensible defaults that work out of the box
• Makes the role self-contained and reusable
• Documents expected variables for the role

Disadvantages:

• Lowest precedence (easily overridden - quick note here.  Grab the "SQL Server Automation: Your First Steps with Ansible" guide below, and I'll include the "Ansible Variable Precedence: The Complete Guide" as well.  Both are free.) 
• Not meant for host-specific configurations
• Separated from inventory information

E. Role Vars (roles/sql_ag_patch/vars/main.yml)

What it is: Role-specific variables with higher precedence than defaults.  Role-specific variables have not been used for this particular role (not yet, anyway, but you should know about these).

# roles/sql_ag_patch/vars/main.yml
required_powershell_modules:
  - SqlServer

Advantages:

• Higher precedence than defaults
• Good for variables tightly coupled to role functionality
• Centralizes role-specific configuration

Disadvantages:

• Still not ideal for host-specific values
• Can be overridden by extra vars, but not by inventory vars

Why We're Using Inventory File Variables for This Example

For our SQL Server AG patching blog series, I've chosen to define the host-specific variables directly in the inventory file. Here's why:

1. Simplicity: For demonstration purposes, having everything in one file makes it easier to follow along.
2. Limited Scope: We're only working with 4 hosts and a handful of variables.
3. Self-contained: New readers can see the complete configuration without jumping between multiple files.
4. Focus: It keeps the focus on the patching logic rather than Ansible's variable management.

Here's the complete inventory file we'll be using for our examples:

[sqlserver]
SQL1.SANDBOX.LOCAL sql_instance_name="DEFAULT" sql_port=1433
SQL2.SANDBOX.LOCAL sql_instance_name="DEFAULT" sql_port=1433
SQL3.SANDBOX.LOCAL sql_instance_name="DEFAULT" sql_port=1433
SQL4.SANDBOX.LOCAL sql_instance_name="INSTA" sql_port=1433

[sqlserver:vars]
ansible_connection=winrm
ansible_winrm_server_cert_validation=ignore
ansible_port=5986
ansible_winrm_transport=kerberos
ansible_winrm_kerberos_delegation=true
ansible_winrm_operation_timeout_sec=60
ansible_winrm_read_timeout_sec=90
ansible_winrm_kinit_mode=managed
ansible_winrm_kinit_cmd=kinit

In this configuration, we've defined:

• Four SQL Server hosts in our availability group
• Instance names and port numbers specific to each host
• Common WinRM connection parameters that apply to all hosts in the group

It's worth noting that for larger environments or more complex configurations, I would recommend using the host_vars and group_vars approach. As your automation grows, organizing variables into dedicated files becomes increasingly valuable for maintainability.

What About Sensitive Information?

You might be wondering, "What about sensitive information like passwords?" This is where Ansible Vault comes in. While we're not covering vault usage in detail in this post, it's important to know that any sensitive variables should be encrypted using Ansible Vault rather than stored in plain text.

For SQL Server environments, this typically includes:

• SQL authentication passwords
• Domain service account credentials
• Remote connection strings with embedded credentials

We'll explore secure credential management in a future post (separate series).

Next Steps

With our inventory file defined, we now have a solid foundation for our SQL Server AG patching automation. In the next post, we'll start building the actual Ansible role and tasks that will perform the patching process we outlined earlier.

We'll focus on how to:

• Validate the patch file integrity
• Identify primary and secondary replicas
• Handle failover modes during patching
• Execute the update process in the correct sequence
• Post-validation

What About Your Environment?

I'm curious - how do you organize your Ansible inventory? Do you prefer keeping variables in the inventory file for simplicity, or do you use the host_vars and group_vars structure for better organization? 

Previous ← Beyond Manual Patching: Automating SQL Server AG Updates with Ansible

Next → SQL Server AG Patching with Ansible: Creating Your Role Structure