{"id":43534,"date":"2026-03-23T18:50:46","date_gmt":"2026-03-23T17:50:46","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=43534"},"modified":"2026-03-23T18:50:48","modified_gmt":"2026-03-23T17:50:48","slug":"how-to-standardize-sql-server-disks-on-vms-using-ansible","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-to-standardize-sql-server-disks-on-vms-using-ansible\/","title":{"rendered":"How to Standardize SQL Server Disks on VMs using Ansible"},"content":{"rendered":"\n
INTRODUCTION<\/h2>\n\n\n\n
Today, the benefits of automation no longer need much explanation: saving time, reducing human error, and ensuring every environment remains aligned with internal standards. What is less obvious, however, is how using an Ansible Playbook can provide advantages that more traditional scripting approaches \u2014 such as large PowerShell scripts \u2014 struggle to offer. That is exactly what I want to explore here.<\/p>\n\n\n\n
When you complete an automated deployment of a SQL Server environment on Windows Server, there is a real sense of achievement. You have invested time and effort, and you expect that investment to pay off thanks to the reliability and repeatability of automation.<\/p>\n\n\n\n
But everything changes when the next Windows Server upgrade or SQL Server version arrives\u2026 or when corporate standards evolve. Suddenly, you need to reopen a multi-thousand\u2011line PowerShell script and:<\/p>\n\n\n\n
\n
Integrate the required changes while keeping execution stable,<\/li>\n\n\n\n
Avoid subtle but potentially critical regressions,<\/li>\n\n\n\n
Maintain clear and usable logging,<\/li>\n\n\n\n
Retest the entire automation workflow,<\/li>\n\n\n\n
Troubleshoot new issues introduced by the modifications.<\/li>\n<\/ul>\n\n\n\n
This is precisely the type of situation where Ansible becomes a far better long\u2011term investment. Its architecture and philosophy offer several advantages:<\/p>\n\n\n\n
\n
Native idempotence, ensuring the same result even after multiple runs,<\/li>\n\n\n\n
A declarative YAML approach, focusing on the desired end state rather than the execution steps,<\/li>\n\n\n\n
Windows Server and SQL Server modules, providing built\u2011in idempotence and saving significant time,<\/li>\n\n\n\n
Agentless connectivity, simplifying deployment on new machines,<\/li>\n\n\n\n
A modular structure (roles, modules, variables), making adaptation and reuse of your automation much easier.<\/li>\n<\/ul>\n\n\n\n
In this article, I will give you a concrete overview by walking you through how to configure the disks required for SQL Server using Ansible.<\/p>\n\n\n\n
1-Map iSCSI controllers to disk numbers<\/h2>\n\n\n\n
When developing an Ansible Playbook, one fundamental principle is to design for idempotence from the very start\u2014not just rely on idempotent modules.<\/p>\n\n\n\n
On Windows, disk numbering is not guaranteed: it depends on several factors \u2013 how disks are detected at startup, the firmware, and so on. As a result, disk numbers may change from one reboot to another.<\/p>\n\n\n\n
To ensure consistent and reliable execution of your deployment, this behavior must be accounted for directly in the design of your Playbook. Otherwise, it may introduce wrong behaviors, and lead to:<\/p>\n\n\n\n
\n
formatting the wrong disk,<\/li>\n\n\n\n
mounting volumes on incorrect devices,<\/li>\n\n\n\n
completely breaking the SQL Server provisioning workflow.<\/li>\n<\/ul>\n\n\n\n
In other words, idempotence is no longer guaranteed.<\/p>\n\n\n\n
To ensure stable and predictable executions, you must determine dynamically the correct disk numbering at each execution. <\/p>\n\n\n\n
You can use Get-Disk PowerShell command to achieve your goal, by searching iSCSI controller number and LUN position from Location property.<\/p>\n\n\n
We have done our mapping between VM specifications and Windows disk numbers.<\/p>\n\n\n\n
2-Loop SQL Server disks<\/h2>\n\n\n\n
Since we often have several disks to configure \u2014 Data, Logs, TempDB \u2014 we need to perform the same actions repeatedly on each disk:<\/p>\n\n\n\n
\n
dynamically determine the disk number,<\/li>\n\n\n\n
initialize it in GPT,<\/li>\n\n\n\n
create the partition and format the volume in NTFS with a 64\u202fKB allocation unit size,<\/li>\n\n\n\n
assign an access path (drive letter or mountpoint),<\/li>\n\n\n\n
apply certain specific configuration settings, such as disabling indexing,<\/li>\n\n\n\n
verify the compliance of the disk configuration.<\/li>\n<\/ul>\n\n\n\n
As these actions are identical for all disks, the best approach is to factorize the tasks. The Ansible pattern, for such scenario, is to loop that call in a dedicated Task File.<\/p>\n\n\n
3- Implement SQL Server disk configuration<\/h2>\n\n\n\n
Since we performed our loop in the previous section on the disks_properties.yml file, we can now implement the configuration actions inside this file. First, we will retrieve the disk number and then begin configuring the disk according to best practices and our internal standards.<\/p>\n\n\n\n
To guarantee idempotence, we will mark this step as not changed: this is only a Get action:<\/p>\n\n\n
Then, we will register the disk number as an Ansible Fact for all this task file execution call.<\/p>\n\n\n
\n- name: Set fact for {{ disk.name }} disk number\n ansible.builtin.set_fact:\n "disk_number_{{ disk.name }}": "{{ disk_num.stdout | trim | int }}"\n<\/pre><\/div>\n\n\n
We can now initialize the disk using community.windows<\/em> module. Of course, use Ansible module if possible.<\/p>\n\n\n\n
The parameter disk_bps.partition_style<\/em> is a variable of my Ansible Role, to guarantee GPT will be used. <\/p>\n\n\n
From there, we can create our partition: <\/p>\n\n\n
\n- name: Create partition with letter {{ disk.letter }} for disk {{ disk.name }}\n community.windows.win_partition:\n drive_letter: "{{ disk.letter }}"\n partition_size: "-1"\n disk_number: "{{ lookup('vars', 'disk_number_' + disk.name) }}"\n<\/pre><\/div>\n\n\n
And now format our volume with allocation unit size 64KB:<\/p>\n\n\n
\n- name: Create a partition letter {{ disk.letter }} on disk {{ disk.name }} with label {{ disk.label }}\n community.windows.win_format:\n drive_letter: "{{ disk.letter }}"\n allocation_unit_size: "{{ disk_bps.allocation_unit_size_bytes }}"\n new_label: "{{ disk.label }}"\n\n...\n<\/pre><\/div>\n\n\n
As I mentioned earlier in previous section, we can also add tasks relative to some specific standards or a tasks to guarantee disk compliance.<\/p>\n\n\n\n
4- Execute the Playbook<\/h2>\n\n\n\n
Now that our Ansible Role windows_disks<\/em> is ready, we can call it through a Playbook. Of course, we must adjust the reality of the iSCSI configuration of the Virtual Machine.<\/p>\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/03\/Ansible_disks-1024x623.png\")