SupportConsoleDevelopersStart a trialContact

Chapter 23. Configuring Microsoft SQL Server using the microsoft.sql.server Ansible role

As an administrator, you can use the microsoft.sql.server Ansible role to install, configure, and start Microsoft SQL Server (SQL Server). The microsoft.sql.server Ansible role optimizes your operating system to improve performance and throughput for the SQL Server. The role simplifies and automates the configuration of your RHEL host with recommended settings to run the SQL Server workloads.

23.1. Prerequisites

  • 2 GB of RAM
  • root access to the managed node where you want to configure SQL Server

  • Pre-configured firewall

    You can set the mssql_manage_firewall variable to true so that the role can manage firewall automatically.

    Alternatively, enable the connection on the SQL Server TCP port set with the mssql_tcp_port variable. If you do not define this variable, the role defaults to the TCP port number 1433.

    To add a new port, use: 

    # firewall-cmd --add-port=xxxx/tcp --permanent
# firewall-cmd --reload

    Replace xxxx with the TCP port number then reload the firewall rules.

  • Optional: Create a file with the .sql extension containing the SQL statements and procedures to input them to SQL Server.

23.2. Installing the microsoft.sql.server Ansible role

The microsoft.sql.server Ansible role is part of the ansible-collection-microsoft-sql package.

Prerequisites

  • root access

Procedure

  1. Install Ansible Core which is available in the RHEL 7.9 AppStream repository: 

    # *yum install ansible-core*

  2. Install the microsoft.sql.server Ansible role: 

    # *yum install ansible-collection-microsoft-sql*

23.3. Installing and configuring SQL server using the microsoft.sql.server Ansible role

You can use the microsoft.sql.server Ansible role to install and configure SQL server.

Prerequisites

  • The Ansible inventory is created

Procedure

  1. Create a file with the .yml extension. For example, mssql-server.yml.

  2. Add the following content to your .yml file: 

    ---
- hosts: all
  vars:
    mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula: true
    mssql_accept_microsoft_cli_utilities_for_sql_server_eula: true
    mssql_accept_microsoft_sql_server_standard_eula: true
    mssql_password: <password>
    mssql_edition: Developer
    mssql_tcp_port: 1433
  roles:
    - microsoft.sql.server

    Replace <password> with your SQL Server password.

  3. Run the mssql-server.yml ansible playbook: 

    # *ansible-playbook mssql-server.yml*

23.4. TLS variables

You can use the following variables to configure the Transport Level Security (TLS) protocol.

Table 23.1. TLS role variables
Role variableDescription

mssql_tls_enable

This variable enables or disables TLS encryption.

The microsoft.sql.server Ansible role performs following tasks when the variable is set to true:

  • Copies TLS certificate to /etc/pki/tls/certs/ on the SQL Server
  • Copies private key to /etc/pki/tls/private/ on the SQL Server
  • Configures SQL Server to use TLS certificate and private key to encrypt connections

When set to false, the TLS encryption is disabled. The role does not remove the existing certificate and private key files.

mssql_tls_cert

To define this variable, enter the path to the TLS certificate file.

mssql_tls_private_key

To define this variable, enter the path to the private key file.

mssql_tls_remote_src

Defines if the role searches for mssql_tls_cert and mssql_tls_private_key files remotely or on the control node.

When set to the default false, the role searches for mssql_tls_cert or mssql_tls_private_key files on the Ansible control node.

When set to true, the role searches for mssql_tls_cert or mssql_tls_private_key files on the Ansible managed node.

mssql_tls_version

Define this variable to select which TSL version to use.

The default is 1.2

mssql_tls_force

Set this variable to true to replace the certificate and private key files on the host. The files must exist under /etc/pki/tls/certs/ and /etc/pki/tls/private/ directories.

The default is false.

23.5. Accepting EULA for MLServices

You must accept all the EULA for the open-source distributions of Python and R packages to install the required SQL Server Machine Learning Services (MLServices).

See /usr/share/doc/mssql-server for the license terms.

Table 23.2. SQL Server Machine Learning Services EULA variables
Role variableDescription

mssql_accept_microsoft_sql_server_standard_eula

This variable determines whether to accept the terms and conditions for installing the mssql-conf package.

To accept the terms and conditions set this variable to true.

The default is false.

23.6. Accepting EULAs for Microsoft ODBC 17

You must accept all the EULAs to install the Microsoft Open Database Connectivity (ODBC) driver.

See /usr/share/doc/msodbcsql17/LICENSE.txt and /usr/share/doc/mssql-tools/LICENSE.txt for the license terms.

Table 23.3. Microsoft ODBC 17 EULA variables
Role variableDescription

mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula

This variable determines whether to accept the terms and conditions for installing the msodbcsql17 package.

To accept the terms and conditions set this variable to true.

The default is false.

mssql_accept_microsoft_cli_utilities_for_sql_server_eula

This variable determines whether to accept the terms and conditions for installing the mssql-tools package.

To accept the terms and conditions set this variable to true.

The default is false.

23.7. High availability variables

You can configure high availability for Microsoft SQL Server with the variables from the table below.

Table 23.4. High availability configuration variables
VariableDescription

mssql_ha_configure

The default value is false.

When it is set to true, performs the following actions:

  • Configures firewall by opening a port from the mssql_ha_listener_port variable and enables the high-availability service in firewall.

  • Configures SQL Server for high availability.

    • Enables Always On Health events.
    • Creates certificate on the primary replica and distributes it to other replicas.
    • Configures endpoint and availability group.
    • Configures the user from the mssql_ha_login variable for Pacemaker.
  • Optional: Includes the System Roles ha_cluster role to configure Pacemaker. You must set mssql_ha_cluster_run_role to true and provide all variables that the ha_cluster role requires for a Pacemaker cluster configuration.

mssql_ha_replica_type

This variable specifies which type of replica you can configure on the host. You can set this variable to primary, synchronous, and witness. You must set it to primary only on one host.

mssql_ha_listener_port

The default port is 5022.

The role uses this TCP port to replicate data for an Always On availability group.

mssql_ha_cert_name

You must define the name of the certificate to secure transactions between members of an Always On availability group.

mssql_ha_master_key_password

You must set the password for the master key to use with the certificate.

mssql_ha_private_key_password

You must set the password for the private key to use with the certificate.

mssql_ha_reset_cert

The default value is false.

If it is set to true, resets the certificate which an Always On availability group uses.

mssql_ha_endpoint_name

You must define the name of the endpoint to configure.

mssql_ha_ag_name

You must define the name of the availability group to configure.

mssql_ha_db_names

You can define a list of the databases to replicate, otherwise the role creates a cluster without replicating databases.

mssql_ha_login

The SQL Server Pacemaker resource agent utilizes this user to perform database health checks and manage state transitions from replica to primary server.

mssql_ha_login_password

The password for the mssql_ha_login user in SQL Server.

mssql_ha_cluster_run_role

The default value is false.

This variable defines if this role runs the ha_cluster role.

Note that the ha_cluster role replaces the configuration of the HA cluster on specified nodes, any variables currently configured for the HA cluster are erased and overwritten.

To work around this limitation, the microsoft.sql.server role does not set any variables for the ha_cluster role to ensure that it does not overwrite any existing Pacemaker configuration.

If you want the microsoft.sql.server to run the ha_cluster role, set this variable to true and provide variables for the ha_cluster role with the microsoft.sql.server role call.

Note, this role backs up the database to the /var/opt/mssql/data/ directory.

For examples on how to use high availability variables for Microsoft SQL Server:

  • If you install the role from Automation Hub, see the ~/.ansible/collections/ansible_collections/microsoft/sql/roles/server/README.md file on your server.
  • If you install the role from a package, open the /usr/share/microsoft/sql-server/README.html file in your browser.
Red Hat logoGithubRedditYoutubeTwitter

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

We help Red Hat users innovate and achieve their goals with our products and services with content they can trust.

Making open source more inclusive

Red Hat is committed to replacing problematic language in our code, documentation, and web properties. For more details, see the Red Hat Blog.

About Red Hat

We deliver hardened solutions that make it easier for enterprises to work across platforms and environments, from the core datacenter to the network edge.

© 2024 Red Hat, Inc.