Ansible By Example - How to Use Ansible Templates for SQL Server Configuration Files

Edition: Saturday, June 22nd, 2024

SQL Server's configuration files can be a little daunting at first glance.  There are over 50 options which can be set.  Usually, these are supplied when using the installation wizard.  But, what if you needed to automate the installation of SQL Server?  That's what I'm going to talk about today.  How to convert, or begin converting, the standard configuration.ini file to a j2 (Jinja2) template which can then be used with Ansible.

Let's jump in. 

What are Ansible Templates?

Templates are files that allow you to dynamically generate customized configurations or content.  They are written in the Jinja2 templating language, which is a powerful and flexible tool for creating templates in Python-based projects.  Ansible templates are used to create configuration files (in our case, configuration.ini files), scripts, or any other text-based files that need to be customized based on variables or other dynamic data.

Key Features:

1. Dynamic Content:
   • You can insert variables, conditionals, loops, and filters to customize the content.  This is what makes them so flexible and easy to use.
   • Variables can be defined in Ansible playbooks, vars files, inventory files, or other sources.
2. Template Files:
   • Template files usually have a `.j2` extension but not required.
   • They contain the base structure of the desired file with placeholders for dynamic content.
3. Usage in Playbooks: 
   • The `template` module in Ansible processes these template files and generates the final files on the target hosts.

How do you use templates?

Here's an example:

Config2022.j2 (snippet)

; The number of Database Engine TempDB files.
SQLTEMPDBFILECOUNT={{ rl_mssql_tempdbfilecount }}

playbook_installSQLServer.yml (snippet)

- name: Copy the configuration.ini file to target
ansible.builtin.template:
src: './config2022.j2'
dest: 'C:\temp\Configuration.ini'

While running the playbook, we can provide the value for `rl_mssql_tempdbfilecount`.  Ansible will then take that value, place it in the configuration file, and copy it to the target host.

ansible-playbook playbook_installSQLServer.yml --extra-vars "rl_mssql_tempdbfilecount = 8"

Result:

See this in action in the YouTube video below. (17 minutes).

Episode 4 - Ansible Templates and SQL Server Configuration Files (youtube.com)

You can grab the example playbooks and files at Ansible-for-SQL-Server-DBAs/Ansible-Templates at main · automatesql/Ansible-for-SQL-Server-DBAs (github.com)

Below is the lab environment I'm using, built using my automated sandbox framework.

Environment:

Here's how I have my lab configured.  If you build one to follow along in the examples, be sure to create snapshots first.  This way you can easily reset your environment for the next walkthrough.

• Hardware - Geekom A7 Mini PC Ryzen - 64 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 used to create the playbooks throughout this series.

Conclusion:

We've only scratched the surface of what's possible using templates.  Stay tuned for more to come using this powerful feature when working with Ansible, Windows, and SQL Server.

Ansible For SQL Server DBAs (join the wait list) - https://www.automatesql.com/ansible. 

Thanks for reading!  Have a great week!