Ansible By Example: Simplifying SQL Server Disk Preparation on Windows Server

Edition: Saturday, May 25th, 2024

Over the years, I've completed a lot of SQL Server installations.  Some of those resulted in having to move database files around afterward because I failed to ensure the disks were properly formatted and aligned.  I had to move files to a temporary location, reformat the disks, and then move the files back afterward.  Painful and slowed down the progress.

I eventually learned my lesson and scripted a quick verification using get-volume in PowerShell (now you can use dbatools).  This helped but didn't allow for scaling as much as I would have liked.

Today, I'm starting a series on how to use Ansible to avoid repeating these errors and become more scalable as a DBA.  We'll start with building a simple playbook that will:

• Retrieve information about uninitialized disks on remote Windows Server 2022 servers.
• Initialize those disks and assign a drive letter.
• Partition the disks.
• Finally, format the disks, using 64kb block sizes.

If you're completely new to Ansible, check out the following resources:

• Getting Started With Ansible: Key Concepts (automatesql.com)
• Getting Started With Ansible: Installing the Control Node (automatesql.com)
• Getting Started With Ansible: Setting Up a Windows Managed Node (automatesql.com)
• Getting Started With Ansible: Creating Your First Inventory File (automatesql.com)

Let's jump in. 

Environment:

My lab environment is set up like this:

• Hardware - Geekom A7 Mini PC Ryzen - 32 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 - VMware Workstation Zealot)
• Virtual Machines - All built using a single Windows Server 2022 Standard (eval) Hashicorp Packer image and managed with Vagrant.
  • DC1 - Domain Controller (HOMELAB.LOCAL)
  • CA1 - Certificate Authority
  • 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 is used to create the playbooks throughout this series.

Scenario:

You've just been given 2 virtual machines that need to be configured for an SQL Server installation.  The system admin has attached 3 disks that you'll configure as the following:

• Disk 1 - Tempdb
• Disk 2 - Data
• Disk 3 - Log 

Both VMs have been joined to the HOMELAB.LOCAL domain and WinRM has been configured to allow remote management. Your Ansible control node has already been configured to use Kerberos. 

Your goal is to define a playbook that will bring these disks online, formatted, and ready for an upcoming SQL Server installation. This playbook will become part of your standard operating procedure when building future instances.

Step 1: Connect to Ubuntu (WSL) Using Visual Studio Code

Check out the "Installing the Control Node" post for instructions on how to install the WSL extension for VS code.  Once connected, create a folder named "myPlaybooks."  

Step 2: Building the Inventory File

The Ansible inventory file is a configuration file that lists and categorizes the hosts Ansible will manage.  Create a file named hosts.ini within the myPlaybooks folder.

Copy and Paste the following (don't forget to save it):

[windbservers]
SRV1.HOMELAB.LOCAL
SRV2.HOMELAB.LOCAL

[windbservers: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

Explanation:

Host Group Definition: [windbservers] - defines a group of hosts.  SRV1 and SRV2 are grouped together since they will have the same purpose.

Group Variables: [windbservers:vars] - defines variables that apply to all hosts within the windbservers group.

• ansible_connection=winrm - Specifies that Ansible should use WinRM (Windows Remote Management) to connect to the hosts.
• ansible_winrm_server_cert_validation=ignore - Instructs Ansible to ignore server certificate validation.  You're using a self-signed certificate for now.
• ansible_port=5986 - Specifies the port to use for WinRM connections.  Port5986 is the default for WinRM over HTTPS.
• ansible_winrm_transport=kerberos - Specifies that Kerberos authentication should be used for the WinRM transport.  
• ansible_winrm_kerberos_delegation=true - Enables Kerberos delegation, allowing the credentials to be forwarded to other services as needed.
• ansible_winrm_operation_timeout_sec=60 - Sets the operation timeout for WInRM to 60 seconds.  This is the maximum time Ansible will wait for a response to a single operation.
• ansible_winrm_read_timeout_sec=90 - Sets the read timeout for WinRM to 90 seconds.  This is the maximum time Ansible will wait to read a response from the host.
• ansible_winrm_kinit_mode=managed - Specifies that Ansible should manage Kerberos ticket initialization automatically.
• ansible_winrm_kinit_cmd=kinit - Specifies the command to use for Kerberos ticket initialization.  kinit is the command-line tool for initializing a Kerberos ticket.

Step 2: Verify Connectivity Using win_ping

 Before you get too far down the path of creating a playbook, you need to verify Ansible can connect successfully to SRV1 and SRV2.  Let's do that now using a module named win_ping.  This module is specifically designed for Windows hosts.  It serves as a simple way to check if Ansible can successfully communicate with a Windows machine.  If the connection is successful, win_ping will return a pong response.

1. Open Ubuntu on your Ansible Control Node (SRV3).

2. Change the directory to myPlaybooks.

3. Run the following Ansible ad-hoc command (replacing the username with yours). You'll be prompted for your domain password.

ansible all -i hosts.ini -m win_ping -u [email protected] --ask-pass

 4. If all is well, you'll see the following.

Step 3: Create the playbook_initializeDisks.yml playbook

Now you're ready to create your playbook.  Jump back into VS Code and create a file named playbook_initializeDisks.yml inside the myPlaybooks folder.

Copy the code below and paste it into your new playbook and save.

Quick Tip - To get an explanation of what this playbook does, use the ChatGPT prompt below and paste the code.  "I'm using Ansible.  Explain what the following playbook does."

Step 4: Run the Playbook 

Next, run the playbook using the command below from within your Ansible control node.

ansible-playbook -i hosts.ini playbook_initializeSQLDisks.yml -u [email protected] --ask-pass

You should see each task run, whether or not a change was made, and then a play recap at the end.

Once the playbook runs, connect to SRV1 and SRV2 to verify the disks have been formatted and available in Windows File Explorer.

Conclusion:

Ansible is a powerful tool that's often overlooked by Windows administrators and SQL Server DBAs.  It's not a replacement for PowerShell, but, as you'll see in upcoming posts, it's quite capable of helping you manage servers built on Windows and Linux.

Thanks for reading!  Have a great week!