Mastering SQL Server Orchestration via Ansible: From Linux Deployment to Enterprise Configuration

The convergence of Microsoft SQL Server and Linux has fundamentally altered the landscape of database administration, shifting the paradigm from proprietary silos toward flexible, open-source orchestration. Leveraging Ansible for the deployment and management of SQL Server on Linux transforms a traditionally manual, error-prone installation process into a repeatable, version-controlled pipeline. By treating a database engine as a configurable service—similar to any other Linux daemon—administrators can achieve absolute consistency across development, staging, and production environments. This transition to Infrastructure as Code (IaC) ensures that every parameter, from the initial Enterprise License Agreement (EULA) acceptance to the fine-tuning of kernel memory limits, is documented and enforceable.

The operational efficiency gained through Ansible is not merely about speed but about the elimination of "configuration drift." When SQL Server is deployed via playbooks, the state of the server is defined in YAML, providing a human-readable blueprint that allows teams to scale their database infrastructure without increasing the administrative overhead linearly. Whether deploying on physical hardware, on-premises virtual machines, or Azure Virtual Machines, the use of Ansible allows for a unified management layer that abstracts the underlying complexity of the operating system.

Foundational Requirements and Infrastructure Specifications

Before initiating the automation of SQL Server, the target environment must meet specific hardware and software benchmarks to ensure stability and performance. The prerequisites are not merely suggestions but critical thresholds for the engine to operate without catastrophic failure.

The following table details the mandatory specifications for the target nodes:

The technical requirement for 4GB of RAM is a practical necessity because, while the SQL Server engine may technically start with 2GB, the overhead of the operating system and the memory demands of the mssql-server process during high-load operations can lead to system instability. From an impact perspective, failing to meet these requirements results in failed installation tasks or erratic service restarts, which disrupts the deployment pipeline.

Architectural Design of the Ansible Inventory

The inventory file serves as the source of truth for the deployment, defining which nodes are targets and what specific configurations apply to them. Using a structured inventory allows the administrator to separate the logic of the playbook from the data of the environment.

In a typical inventory/sqlserver.ini configuration, the nodes are grouped under [sqlserver_nodes], with specific variables assigned to that group. This hierarchical approach enables the use of different versions or editions across different environments (e.g., Developer edition for staging and Enterprise for production).

The following variables are critical for the deployment:

• ansible_user: The remote user (e.g., ubuntu) used for SSH connectivity.
• mssql_version: The specific version of SQL Server (e.g., 2022) to be installed.
• mssql_edition: The product ID (e.g., Developer, Standard, Enterprise).
• mssql_tcp_port: The network port for SQL traffic, typically 1433.
• mssql_data_dir: The absolute path for database files (/var/opt/mssql/data).
• mssql_log_dir: The path for transaction logs (/var/opt/mssql/log).
• mssql_backup_dir: The destination for database backups (/var/opt/mssql/backup).

By defining these in the inventory, the playbook remains generic and reusable. If the organization decides to move the data directory to a separate high-speed NVMe mount, only the inventory variable needs to be updated, not the underlying code.

Deep Dive: Installing SQL Server on Ubuntu

The installation process on Ubuntu follows a strict sequence of dependency resolution, trust establishment, and package deployment.

The first phase involves the installation of prerequisite packages. The ansible.builtin.apt module is used to ensure that curl, gnupg, and software-properties-common are present. These tools are essential for downloading the Microsoft GPG key and managing the software repositories.

The second phase is the establishment of trust through the Microsoft GPG key. This is achieved using the ansible.builtin.apt_key module, pointing to https://packages.microsoft.com/keys/microsoft.asc. This ensures that the packages downloaded are authentic and have not been tampered with.

The third phase is the addition of the specific SQL Server repository. The playbook uses the ansible.builtin.apt_repository module with a dynamic URL:

yaml - name: Add SQL Server repository ansible.builtin.apt_repository: repo: "deb https://packages.microsoft.com/ubuntu/{{ ansible_distribution_version }}/mssql-server-{{ mssql_version }} {{ ansible_distribution_release }} main" state: present filename: mssql-server

This logic utilizes Ansible facts (ansible_distribution_version and ansible_distribution_release) to ensure the repository matches the exact version of Ubuntu running on the host.

The final installation phase employs the ansible.builtin.apt module to install the mssql-server package. The update_cache: true parameter is critical here to ensure the package manager recognizes the newly added repository. The result of this task is registered in mssql_install to trigger the subsequent configuration step only if the package was actually installed or changed.

Post-Installation Configuration and the mssql-conf Setup

Installing the binaries is only the first step; the engine must be initialized. The mssql-conf setup command is the primary mechanism for accepting the End User License Agreement (EULA) and defining the System Administrator (SA) password.

Because this command is interactive, Ansible must pass the required variables via the environment keyword. This prevents the playbook from hanging while waiting for user input.

yaml - name: Run mssql-conf setup to accept EULA and set SA password ansible.builtin.command: cmd: /opt/mssql/bin/mssql-conf setup environment: ACCEPT_EULA: "Y" MSSQL_SA_PASSWORD: "{{ vault_mssql_sa_password }}" MSSQL_PID: "{{ mssql_edition }}" when: mssql_install.changed changed_when: true no_log: true

The use of no_log: true is a security imperative. Since the MSSQL_SA_PASSWORD is passed as an environment variable, it would otherwise be printed in plain text to the Ansible logs or console. By leveraging vault_mssql_sa_password, the password remains encrypted at rest and is only decrypted during runtime.

Following setup, the ansible.builtin.systemd module ensures the mssql-server service is started and enabled to persist across reboots.

Deployment of SQL Server Command-Line Tools

The core engine does not include the tools required for administrative interaction. Tools such as sqlcmd and bcp (Bulk Copy Program) are distributed in a separate package.

The process involves adding a different repository (prod) and installing the mssql-tools18 and unixodbc-dev packages. The unixodbc-dev package is a critical dependency for the ODBC driver, which allows the tools to communicate with the SQL Server instance.

The installation is performed as follows:

yaml - name: Install SQL Server tools and ODBC driver ansible.builtin.apt: name: - mssql-tools18 - unixodbc-dev state: present update_cache: true environment: ACCEPT_EULA: "Y"

Once installed, the tools are located in /opt/mssql-tools18/bin/. These tools are then used by Ansible to verify the installation by querying the database version.

Verifying the Installation and Database State

To ensure the database is not just running but accessible, Ansible can execute SQL queries directly using the sqlcmd utility. This provides a mechanism for health checks within the CI/CD pipeline.

The following logic captures the SQL Server version:

yaml - name: Query SQL Server version ansible.builtin.command: cmd: > /opt/mssql-tools18/bin/sqlcmd -S localhost,{{ mssql_tcp_port }} -U SA -P "{{ vault_mssql_sa_password }}" -C -Q "SELECT @@VERSION AS version;" -h -1 register: mssql_version changed_when: false no_log: true

The -C flag is used to trust the server certificate, and the -Q flag allows for the execution of a query followed by an immediate exit. By registering the output in mssql_version, the administrator can use the ansible.builtin.debug module to print the actual version of the running engine, providing confirmation that the deployment was successful.

Similarly, the list of databases can be retrieved by querying sys.databases, allowing the administrator to verify that necessary system or user databases have been initialized.

RHEL and CentOS Deployment Logic

For Red Hat Enterprise Linux (RHEL) environments, the process shifts from apt to yum. The core logic remains identical—repository addition, package installation, and configuration—but the modules change.

The ansible.builtin.yum_repository module is used to define the repository. The baseurl is dynamically constructed using the ansible_distribution_major_version to ensure the correct RHEL version is targeted.

yaml - name: Add SQL Server repository for RHEL ansible.builtin.yum_repository: name: mssql-server description: "SQL Server {{ mssql_version }}" baseurl: "https://packages.microsoft.com/rhel/{{ ansible_distribution_major_version }}/mssql-server-{{ mssql_version }}/" gpgcheck: true gpgkey: https://packages.microsoft.com/keys/microsoft.asc enabled: true

After the repository is established, the ansible.builtin.yum module is used to install the mssql-server package, mirroring the Ubuntu flow.

Enterprise Performance Tuning and System Optimization

Running SQL Server in a production environment requires more than just installation; it requires tuning the Linux kernel to handle the heavy I/O and memory demands of a database engine.

Memory and Network Tuning

SQL Server on Linux can be sensitive to how the kernel manages memory and network buffers. The following system settings are often required for high-performance workloads:

• net.core.somaxconn: Increased to handle more simultaneous connections.
• net.core.wmem_default: Set to 262144 to optimize window memory.
• net.core.wmem_max: Set to 1048576 to allow larger network buffers.

Disabling Transparent Huge Pages (THP)

Transparent Huge Pages can cause performance degradation and memory fragmentation for SQL Server. The recommended practice is to disable THP. This is achieved by creating a systemd service that echoes never into the kernel parameters.

yaml - name: Disable Transparent Huge Pages ansible.builtin.copy: dest: /etc/systemd/system/disable-thp.service content: | [Unit] Description=Disable Transparent Huge Pages for SQL Server Before=mssql-server.service [Service] Type=oneshot ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/defrag' [Install] WantedBy=multi-user.target mode: "0644"

Resource Limit Configuration

The mssql user requires specific resource limits to prevent the operating system from killing the process during peak loads. The /etc/security/limits.d/mssql.conf file must be configured to ensure the following:

• nofile: Set to 65535 (both soft and hard) to allow a high number of open files.
• nproc: Set to 65535 (both soft and hard) to allow sufficient processes/threads.
• memlock: Set to unlimited to prevent memory from being swapped to disk.

Orchestration Flow and Lifecycle Management

The deployment of SQL Server is not a single event but a lifecycle. The management flow follows a strict sequence to ensure that no step is missed:

1. Install SQL Server Package: The binary installation.
2. Run mssql-conf Setup: Acceptance of EULA and SA password definition.
3. Install Command-Line Tools: Deployment of sqlcmd and bcp.
4. Configure Server Settings: Application of database-specific parameters.
5. Apply System Tuning: Kernel and resource limit optimizations.
6. Create Databases: Initializing the data structures.
7. Create Users and Roles: Establishing security and access control.
8. Configure Backups: Automating data protection.
9. Set Up Monitoring: Implementing health checks and alerts.
10. Verify Installation: Final validation via SQL queries.

This flow ensures that the system is fully optimized before any actual data is loaded into the database.

Understanding Ansible Collections for SQL Server Admins

For those transitioning from traditional Windows DBA roles, understanding the structure of Ansible is paramount. Ansible uses "Collections" to organize functionality.

A collection is a bundle containing:

• Modules: The smallest unit of work (e.g., win_service for managing Windows services).
• Roles: Pre-packaged tasks and variables with a standard folder structure (defaults, files, tasks, templates).
• Plugins: Extensions for inventory or lookup functionality.
• Documentation: Reference materials and examples.

The naming convention follows the <namespace>.<collection_name> format. For example, community.windows is a collection where community is the namespace and windows is the collection.

From a DBA perspective, this can be visualized as a database hierarchy:
- Namespace = Database (The top-level container).
- Collection = Schema (A group of related objects).
- Module = Stored Procedure (The actual code that performs the work).

This modularity allows organizations to create their own internal collections, such as myorg.mssql, to encapsulate company-specific standards for backup schedules or security hardening.

Azure Virtual Machine Integration

When deploying SQL Server on Azure, the process can be further automated using the Azure CLI and Ansible Core. In this architecture, a "Controller Node" (where Ansible Core is installed) manages "Managed Nodes" (the Azure VMs where SQL Server is deployed).

On RHEL 8.x Azure VMs, the ansible-core package can be installed directly from the preconfigured AppStream repository. This allows for a seamless integration between the Azure cloud fabric and the configuration management layer, enabling the rapid spinning up of SQL Server clusters.

Conclusion: The Impact of Automation on Database Administration

The shift toward using Ansible for SQL Server management represents a fundamental evolution in the role of the DBA. By removing the manual elements of installation and configuration, the risk of human error is virtually eliminated. The technical ability to define the entire environment—from the kernel's Transparent Huge Pages setting to the ODBC driver version—in a single YAML file allows for a level of precision that is impossible with manual checklists.

The real-world consequence of this approach is the ability to perform "blue-green" deployments or rapid disaster recovery. If a server fails, a new instance can be provisioned, configured, and tuned in minutes rather than hours. Furthermore, the integration of Ansible Vault ensures that sensitive credentials, such as the SA password, are handled with enterprise-grade security, satisfying compliance requirements for data protection.

Ultimately, the combination of SQL Server on Linux and Ansible creates a highly scalable, portable, and maintainable data tier. It allows the database to be managed with the same rigor as the application code, bridging the gap between Database Administration and DevOps (DataOps).

Sources

1. OneUptime: Ansible Manage SQL Server Linux
2. Microsoft Learn: Deploy SQL Server on Linux with Ansible
3. AutomateSQL: Essential Ansible Windows Modules