Edition: Saturday, June 15th, 2024
No one wants to build a new SQL Server instance, release it to production, and immediately request a maintenance window to fix a configuration issue.
Has this ever happened to you before? I've experienced it a few times, and it doesn't reflect well on the DBA team.
For example, virtual machines have made provisioning new SQL Servers much easier. However, they're easy to configure incorrectly—especially virtual CPUs.
Finding out that SQL Server (standard) isn't using all available cores after the instance is in production requires an outage to correct. With SQL Server 2022 Standard the lesser of 4 sockets or 24 cores can be used. This means if your system admin allocated 24 cores but specified these as 24 CPUs, only 4 of those will be visible to SQL Server.
Which brings us to today's topic. How to use ansible facts to avoid the scenario altogether.
Let's jump in.
What are ansible facts?
Facts are data related to your remote systems, including operating systems, IP addresses, attached filesystems, and more. The "and more" includes processor information on your remote Windows systems where you're deploying SQL Server.
Using facts about the processor, such as core and processor count, you can determine if SQL Server can utilize all cores assigned to the VM before installation.
Pretty cool? There's one catch though. There isn't a fact directly for processor count. So we need to leverage Ansible's "set_fact" module and dynamic expression to create our custom fact; which will give us the number of sockets or processors.
Here's a code snippet of that but you can find the complete playbook in the GitHub repository.
# Let's create a custom fact for the number of sockets - name: Custom fact for number of sockets ansible.builtin.set_fact: processor_sockets: "{{ (ansible_facts.processor_count) // (ansible_facts.processor_cores) }}" tags: SetFact
In the example above, our processor_sockets fact can be used to either stop the play, if greater than 4 or continue. If stopped, you can contact the systems admin to have the VM reconfigured correctly before wasting time with the installation.
This is a powerful component of Ansible. One that we'll take advantage of as we continue the series.
You can find the full example on YouTube.
Episode 3 - Ansible By Example - Episode 3 - How to use Ansible facts for Windows (youtube.com)
Below is the lab environment I'm using, built using my automated sandbox framework.
Environment:
My lab environment is set up like this:
- 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:
Ansible enables you to build prerequisites directly in your playbooks helping you avoid unnecessary outages. If you'd like to get more information regarding facts, check out the Ansible docs here.
Ansible For SQL Server DBAs (join the wait list) - https://www.automatesql.com/ansible.
Thanks for reading! Have a blessed week!