My Early Challenges with Ansible on Windows

When I was getting started with Ansible back in 2017, I ran into issue after issue.  Coming from a Windows background, trying to use a tool that was primarily used on Linux meant there was little documentation to reference.  You might be saying, "Why didn't you just use PowerShell and DBATOOLS instead?" 

Fair question, and up to that point that's what I had been using.  However, the organization I worked for was implementing Ansible Tower (known as Ansible Automation Platform today).  Our engineering team wanted (needed) a method to provide self-service capabilities to development teams and a way to scale.  We saw the potential of using Ansible for SQL Server builds, database deployments, patching, etc. 

Ansible would allow us to use the same tools everyone else in the org was using instead of being siloed.  Plus, it's easy to read and understand and enabled us to configure not only SQL Server but also the operating system.  This didn't mean we were going to throw away PowerShell and DBATOOLS (no way), but it did mean we needed to venture outside of our comfort zone as DBAs.

I'd find references to "modules" but knew little about them.  So I did what any newb would do, set up an ansible control node, and then start trying different modules that I thought would work with Windows. 

For example, one of my first tasks was ensuring I could connect to Windows, so I found the ansible.builtin.ping module and a few examples online of how to use it with Ansible's ad-hoc command.  After hours of trial and error, I finally realized this module only worked with Linux-managed nodes, and what I needed was ansible.windows.win_ping.  

One of the most critical concepts of Ansible to learn is modules.  It is essential to know what they are and which ones can be used when managing SQL Server and Windows.

If you're just venturing into or thinking about using Ansible to manage your Windows and SQL Server environments, I'd like to help you avoid the mistakes I've made.  You'll learn to reference Windows's modules using the correct namespaces and collections.  I'll then show you the top 15 modules I use for managing Windows machines.

Let's jump in.

Understanding Ansible Windows Modules Basics

Modules are reusable, standalone scripts that Ansible executes on managed nodes.  These scripts are executed by the managed nodes on Windows machines (which is why Ansible scales really well).  They are building blocks in Ansible playbooks and perform specific actions like installing packages, managing services, or handling files.  Windows modules are primarily written in PowerShell , while Linux modules are primarily written in Python.

Ansible Windows Modules: Namespaces and Collections Explained

What Is a Namespace?

Namespaces are top-level identifiers for a collection of Ansible content.  Think of it as the organization or bucket for related content.  For example:

• ansible.windows - Provided by the Ansible team (the "ansible" namespace).
• community.windows - Provided by the community (the "community" namespace).
• myorg.mssql  - Provided by my organization (the "myorg" namespace.

The namespace keeps all content under a single "parent" group.  This prevents conflicts like having two different ansible.windows.win_copy modules in the same environment because each would live under its own namespace. 

Looking at namespaces from a SQL Server DBA perspective, ansible would be your database (it contains multiple schemas), windows would be the schema (contains multiple objects), and win_copy would be the stored procedure (the module does the work).

What Is a Collection?

A collection is a structured bundle of Ansible content containing:

• Modules - The individual pieces of functionality (e.g., win_file, win_service, win_dsc).
• Roles - Prepacked tasks and variables following a standard folder structure ( e.g., defaults, files, meta, tasks, templates).
• Plugins - Such as inventory plugins, lookup plugins, filter plugins, and so on.
• Documentation - Sometimes includes README files, examples, or reference material.

Collections can also include playbooks, test data, etc. 

The collection name typically follows the format <namespace>.<collection_name>.  For instance:

• ansible.windows - The "windows" collections under the "ansible" namespace.
• community.windows - The collection of "windows" modules under the "community" namespace.

How Namespaces, Collections, and Modules Work Together

Think of the namespace as the "publisher" or "organization" part (e.g., ansible, community, automatesql, or your own org).  The collection is the repository of content within that namespace (windows, general, custommodules).

So the full identifier should look like this when used in your playbooks:

namespace.collection_name.<plugin_or_module>

Examples:

• ansible.windows.win_dsc
• community.windows.win_format
• myorg.dbamodules.my_awesome_modules  

Now that you know what namespaces, collections, and modules are, let's jump into the top 15 modules that I've used to manage Windows environments.  These will span the Windows collection in both the Ansible and Community namespaces.

My Top 15 Essential Ansible Modules for Windows Management

Now, this list depends on what role you play in your organization.  I manage SQL Server, so that's what these modules are going to revolve around.  However, these are not specific to SQL Server.

1. Disk Management 

[community.windows.win_disk_facts]

This module can be used to retrieve and output detailed information about the attached disks of the target Windows node and its volumes and partitions.  Use it for tasks like verifying the disks specified for a specific application (such as SQL Server) were formatted using the correct:

• Block size (65536 bytes generally for SQL Server)
• Partition type (NTFS or REFS)
• Disk size

Verifying your disks have been correctly configured prior to your application installation can save a lot of headaches.  

Example (this module combined with Ansible's ansible.builtin.debug module)- Loop over all disks and return information to the console in JSON format):

- name: Get disk facts from Windows hosts
community.windows.win_disk_facts:
tags: diskfacts

- name: Return disk facts
ansible.builtin.debug:
msg: "{{ item }}"
loop: "{{ ansible_facts.disks }}"
loop_control:
label: "{{ item.unique_id }}"
tags: diskfacts

For an example playbook showing how to use it in combination with community.windows.win_initialize_disk, community.windows.windows.win_partition, and community.windows.win_format modules, see this blog post.

2. Copying Files

[ansible.windows.win_copy]

Most of us have files that we need to copy to new servers.  These can be anything, from scripts to installers to configuration files, etc.

For example, suppose you have a new SQL Server you need to install.  At a minimum, you'll need to copy the ISO file (or install folder if you've extracted it from the ISO) to the remote server.  I typically don't reference installation files over the network and prefer to keep these local (to avoid network interruptions during an installation).

Example - Copies the SQL Server 2022 Developer Edition ISO from the ansible control node to the C:\temp directory on the remote Windows server.

- name: Win copy demo
ansible.windows.win_copy:
src: ./iso/enu_sql_server_2022_developer_edition_x64_dvd_7cacf733.iso
dest: C:\temp\
tags: wincopy demo

The file being copied can reside on a remote file share instead of the ansible control node.  

3. Mounting ISOs

[community.windows.win_disk_image]

Now that you've copied your ISO, how do you mount it?  Fortunately, we can utilize the win_disk_image module.  It allows you to mount and unmount ISOs on a Windows host.

Example - Mounts the SQL Server 2022 Developer Edition ISO.

- name: Mount the iso
community.windows.win_disk_image:
image_path: "C:\temp\enu_sql_server_2022_developer_edition_x64_dvd_7cacf733.iso"
state: present

To dismount, change the state from "present" to "absent."

4. Server Reboots

[ansible.windows.win_reboot]

Windows ❤️ reboots.  Especially when you're first getting a server ready.  Patching, application installation, settings changes, etc.  The server needs to be restarted (sometimes a lot).  Ansible handles this process quite well (albeit I'd argue that this module is best used as a handler vs a standard task).  This module will reboot a Windows machine, wait for it to restart, and then respond to commands.

Example - Restarts the remote Windows machine.

- name: Win reboot demo
ansible.windows.win_reboot:
tags: winreboot demo

5. Gathering Facts

[ansible.windows.setup]

In the first example, you saw how to gather information about disks (or facts).  Ansible will automatically gather tons more information about your Windows hosts.  It does this by automatically using the setup module behind the scenes (if your playbook doesn't have gather_facts = false, that is).  However, you can also use this module manually to gather subsets of facts.

Example

- name: Windows facts
ansible.windows.setup
tags: winsetup demo

You should know how to set your own facts as well.  In this post, you'll learn how. 

6. Windows Patching

[ansible.windows.win_updates]

Applying Windows and SQL Server updates is a common task we admins must perform regularly.  You can search for, download, and install Windows updates using this module and the Windows Update client.  It can work with either Microsoft's Windows Update catalog or Windows Server Update Services (WSUS). There's mention of "similar corporate update servers," but I haven't personally used any other type except the two mentioned here.

Example - I've covered this one in depth here.

7. Installing Features

[ansible.windows.win_feature]

Occasionally, you'll need to install additional features on your Windows hosts.  Features include failover clusters, IIS, ISCSI target servers, file services, etc.  The win_feature module makes this a piece of cake.  Suppose you need to build a new failover cluster, add shared disks, format and partition those disks, and then install SQL Server at the push of a button.  Using Ansible, you can make this happen so you don't want to overlook this module.

Example - Install the ISCSI target feature (you may want to use this feature when setting up your own virtual test environment, but you don't have your own SAN to use).

- name: Install ISCSI target feature
ansible.windows.win_feature:
name: FS-iSCSITarget-Server
include_management_tools: yes
state: present
register: iscsi

8. Software Installation

[ansible.windows.win_package]

There are numerous modules you could utilize for software installation.  I've used both the ansible.windows.win_command and the win_package module.  In my course Ansible for SQL Server DBAs: Level 1, we build a role that installs SQL Server using win_command, but win_package shouldn't be overlooked.

win_package can be used to install or uninstall software packages on Windows hosts.  The packages can be hosted on the local file system, network file share, or even a URL.

Example - Install SQL Server Management Studio using win_package.  FYI, this example uses variables {{}} instead of hardcoding those values in the task.

- name: "Install SQL Server Management Studio"
ansible.windows.win_package:
path: '{{ rl_mssql_temp_folder }}\{{ item.version }}\{{ item.name }}'
product_id: "{{ item.product_id }}"
arguments: "/install {{ item.arguments }} /Passive DoNotInstallAzureDataStudio=1"
state: present
with_items:
- "{{ rl_mssql_ssms_source }}"

9. Creating and Removing Directories

[ansible.windows.win_file]

Use the win_file module to perform tasks like:

• Create empty files
• Update file modification stamps of existing files
• Create or remove directories

Remember the reference to copying installation files and scripts to the Windows hosts above?  Well, you wouldn't want to just place those anywhere.  Using win_file we can create a directory before we start copying over our files to keep things tidy.  Once complete, the directory could then be removed.

Example - Create the C:\temp directory.

- name: Win file demo
ansible.windows.win_file:
path: C:\temp
state: directory
tags: winfile demo

Remove it when the directory and files within it are no longer needed.

- name: Win file demo
ansible.windows.win_file:
path: C:\temp
state: absent
tags: winfile demo

10. Running Commands

[ansible.windows.win_command]

[ansbile.windows.win_shell]

[ansible.windows.win_powershell]

Just like with installing software, you have multiple choices when running ad-hoc commands or scripts.  There are some key differences between each of these.

ansible.windows.win_command

• Executes commands directly on the Windows host without invoking a shell.
• No Shell Environment - Because it bypasses the shell, you can't use shell operators like >, |, or &&, nor rely on environment variable expansions (%VAR%) or built-in shell commands.
• Ideal for single exe - If you just need to run an installer with basic arguments and no piping or redirection, win_command is sufficient.

Example - Runs the whomai cmd on the remote host.

- name: Win command demo
ansible.windows.win_command:
cmd: whoami /all
register: whoami_out
tags: wincommand demo

ansible.windows.win_shell

• Executes commands through the default shell (typically PowerShell), allowing shell operations like redirection, piping, and variable expansion.  The default shell can be controlled with the ansible_shell_type variable.
• Supports operators such as | (pipe), > (redirection), and && (conditional execution) plus %ENV_VAR% expansions.
• Less secure / Less Idempotent - As with any shell-based command in Ansible, it's more prone to side effects.  You also need to be cautious about quoting and escaping.

Example - Verify disks were formatted correctly.

- name: Verify disks are formatted with 64kb allocation unit sizes
ansible.windows.win_shell: >-
get-volume | ?{$_.DriveType -eq "Fixed" -and $_.DriveLetter -notin ("C","D") -and $_.DriveLetter -ne $null -and $_.AllocationUnitSize -ne 65536} | select DriveLetter,
AllocationUnitSize
register: diskAllocationError

ansible.windows.win_powershell

• Executes PowerShell code on the Windows host, allowing you to use PowerShell cmdlets, functions, and advanced scripting features directly.  
• Accepts multi-line PowerShell scripts.
• You can leverage.NET classes, use loops, conditionals, error handling etc.  However, I'd recommend creating your own Ansible module if you're performing more advanced functions (or look for an Ansible module that might already be available).

Example - Similar to the example above, we're checking that the disks have been formatted correctly.  However, we now have access to Ansible's built-in variables ($Ansible.Changed and $Ansible.Failed).

- name: Win powershell demo
ansible.windows.win_powershell:
script: |
if ( get-volume | ?{$_.DriveType -eq "Fixed" -and $_.DriveLetter -notin ("C","D") -and $_.DriveLetter -ne $null -and $_.AllocationUnitSize -ne 65536} | select DriveLetter, AllocationUnitSize){
$Ansible.Failed = $true
}
else {
$Ansible.Changed = $false
}
tags: winpowershell demo

11. Service Management

[ansible.windows.win_service]

Have you ever needed to restart the SQL Server or SQL Server Agent service after a change (or someone ran a query that pretty much killed the server)?  Maybe you've needed to change a service account or service account password.

With win_service, you can access a whole suite of tools for managing Windows services.  Use it in a handler to restart services as required.  

Example - Ensure the print spooler service is running.

- name: Win service demo
ansible.windows.win_service:
name: spooler
state: started
tags: winservice demo

12. Firewall Management

[community.windows.win_firewall_rule]

The win_firewall_rule module is great for when you need to manage firewall rules for your Windows hosts.  These can include rules to allow or block traffic. 

Example - When installing SQL Server, you'll need to create a firewall rule to allow traffic over port 1433 (or whatever port you've specified for your instance to use).  This module makes this process easy and can be included as a task in your overall SQL Server buildout.

- name: Firewall rule to allow SQL Server access
community.windows.win_firewall_rule:
name: "All SQL Server traffic"
localport: "1433"
action: allow
direction: in
protocol: tcp
state: present
enabled: true

13. Power Plan Management

[community.windows.win_power_plan]

If you're hosting an application on Windows Server, chances are you wouldn't want that server to go to sleep.  And you wouldn't want it throttling the CPU speed.  Both can occur if the server uses the wrong power plan.

Example - Set the Windows power plan to high performance to avoid the issues above.

- name: Set power plan to high performance
community.windows.win_power_plan:
name: high performance

14. Desired State Configuration (DSC)

[ansible.windows.win_dsc]

This module allows you to take advantage of PowerShell DSC.  Combine it with a large number of available resources, and you can configure almost anything.  One caveat.  If Ansible has an overlapping module that performs the same task, use it vs the DSC resource.  If one can't be found, then leverage the DSC resource. 

• ComputerManagementDsc - 15+ resources for managing the computer state.
• ActiveDirectoryDsc - 20+ resources for managing Active Directory.  
• SqlServerDsc - 20+ resources for managing SQL Server.  From building and configuring single instances to creating Always On Availability Groups.  We use this one extensively in the Ansible for SQL Server DBAs: Level 1 course.
• FailoverClusterDsc - Need to build a Windows Server Failover Cluster?  Leverage the resources contained here to manage or create the cluster itself.

You can find additional resources here.

Example - During the build of a new SQL Server instance, you may need to configure settings like maximum degree of parallelism, set the port, or even run t-sql scripts.  You can leverage the win_dsc module to perform these tasks so that when your playbook finishes SQL Server has been installed and configured to your specifications.

- name: Set SQL Server instance maxdop
ansible.windows.win_dsc:
resource_name: SQLMaxDop
DynamicAlloc: true
ServerName: "localhost"
InstanceName: "{{ rl_mssql_instance_name }}"

- name: Set SQL Server instance port
ansible.windows.win_dsc:
resource_name: SqlProtocolTcpIp
IpAddressGroup: "IPAll"
Enabled: true
SuppressRestart: false
TCPPort: "{{ rl_mssql_instance_port }}"
ServerName: "localhost"
InstanceName: "{{ rl_mssql_instance_name }}"

- name: Set SQL Server instance number of error logs to 30
ansible.windows.win_dsc:
resource_name: SqlScript
SetFilePath: "{{ rl_mssql_temp_folder }}\\Set-NumErrorLogs.sql"
GetFilePath: "{{ rl_mssql_temp_folder }}\\Get-NumErrorLogs.sql"
TestFilePath: "{{ rl_mssql_temp_folder }}\\Test-NumErrorLogs.sql"
QueryTimeout: 600
ServerName: "localhost"
InstanceName: "{{ rl_mssql_instance_name }}"
Encrypt: "Optional"

15. Local Security Group Management

 [ansible.windows.win_group_membership]

The win_group_membership allows the addition and removal of local, service and domain users, and domain groups from a local group.  Need to add your SQL Server service account to the local admin group?  You can do that using this module (just kidding.  Don't do that. SQL Server doesn't need local admin rights).  Let's say you're on a DBA team, and this team needs local admin rights.  This module makes this easy to do.

Example - The playbook below prompts the user for the username to be added to the local administrators group. Then it adds it to all Windows managed nodes contained in the sqlservers group (found in the provided inventory file at runtime).

---
- name: Add user to local admin group
hosts: sqlservers
gather_facts: true

vars_prompt:
- name: username
prompt: Please provide the domain user you wish to add. Use the DomainName\Username format.
private: false

tasks:
- name: Add domain user to local admin group
ansible.windows.win_group_membership:
name: Administrators
members: "{{ username }}"
state: present

Conclusion

Ok, that was a few more than 15.  I could keep going because there are so many useful modules contained in the ansible.windows and community.windows collections.  However, looking back through the roles and playbooks I've built over the past several years, I see these specific modules as the most prevalent. 

By now, you've seen how understanding Ansible modules, namespaces, and collections is crucial when managing Windows and SQL Server environments.  From copying files and mounting ISOs to automating entire server builds with DSC, these modules can save you countless hours and headaches—especially if you're doing these tasks manually today.

If you're ready to dive deeper into hands-on, step-by-step training, I invite you to join my course, Ansible for SQL Server DBAS: Level 1.  It walks you through automating SQL Server deployments, ensuring you can put these modules and techniques into practice immediately.  It includes access to the AutomateSQL Insiders Community.  You'll connect with like-minded professionals, share insights, troubleshoot challenges together, and join the monthly live Q&A session.

Thanks for stopping by!