Ansible By Example: SQL Server Installation on Windows

Edition: Saturday, June 8th, 2024

Are you familiar with the terms DevOps and Platform Engineering?  DevOps is the process of consistently and reliably deploying applications into production environments.  Platform Engineering focuses on building and maintaining the foundational infrastructure and tooling that development teams use to build, deploy, and run applications.  It aims to create a self-service, reusable, and standardized platform that abstracts away the complexities of the underlying infrastructure.

What role do DBAs play in platform engineering?

DBAs play a critical role, particularly in environments where database performance, reliability, and scalability are crucial.  Here's how:

• Infrastructure as Code (IaC):
  
  • DBAs can contribute to IaC practices by using tools like Ansible (our focus today), Terraform, Bicep (Azure), and ARM templates to automate the deployment and configuration of SQL Server instances.  This aligns with platform engineering goals of consistent and repeatable infrastructure management.
• Database Automation:
  •  DBAs can develop scripts and automation workflows for tasks such as backups, restores, index maintenance, and performance tuning.  This reduces manual intervention and increases operational efficiency, fitting into the broader automation strategy of platform engineering.
• Monitoring and Observability:
  
  •  Implementing robust monitoring solutions for SQL Server instances is a key responsibility. DBAs can leverage built-in and third-party solutions to ensure databases are performing optimally and to provide insights into potential issues.
• Performance Optimization:
  
  • DBAs are essential in identifying and resolving performance bottlenecks, ensuring databases can handle high traffic and large datasets efficiently.  This supports platform engineering's focus on building resilient and high-performance systems.
• Security and Compliance:
  • Ensuring that SQL Server environments adhere to security best practices and compliance requirements is critical.  DBAs work on configuring encryption, access controls, and auditing to safeguard data, which is a core aspect of platform engineering.
• Collaboration with Development Teams:
  • Collaborate closely with developers to optimize database interactions, design efficient schemas, and implement stored procedures.  This collaboration is crucial for creating a seamless and performant platform.
• Cloud Integration:
  
  •  DBAs need to be proficient in managing SQL Server on cloud services like Azure SQL Database or AWS RDS, and running SQL Server on Azure and AWS virtual machines.  Understanding cloud-native features and integrating them into the platform is a significant aspect of platform engineering.
• High Availability and Disaster Recovery:
  
  • Designing and implementing high availability (HA) and disaster recovery (DR) solutions ensure that the database platform is resilient.  DBAs use technology like Always On Availability Groups, failover clustering, and log shipping to meet these requirements.
• Data Management and Governance:
  • Ensuring data quality, consistency, and governance is a vital part of managing a database platform.  DBAs implement policies and procedures for data management, which is essential for maintaining the integrity of the platform.

Today, I want to show you how you can add Ansible to your toolset and meet some of the objectives above.  Specifically around IaC and database automation.  We'll perform a simple install of SQL Server 2022 Developer edition on two Windows Server 2022 VMs. 

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 - 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 is used to create the playbooks throughout this series.

I'm trying something new this week.  Instead of posting a lot of screenshots of the process here, I created a YouTube video of the walkthrough. Also, you can find all of the associated examples in the Github repository below.

Github - https://github.com/automatesql/Ansible-for-SQL-Server-DBAs 

Youtube - https://youtu.be/aJXh_dEgMMw 

Conclusion:

I believe Platform Engineering and Automation skills will become ever-present in the DBA role.  To that end, I'm considering developing a course on Ansible for SQL Server DBAs.  But before I get too far, I'd like to gauge interest. Find out more at - https://www.automatesql.com/ansible. 

Also, let me know if you prefer the walkthroughs in this series to be text-based / screenshots or if video works best.

Thanks for reading!  Have a blessed week!