Menu Close

Chapter 20. Configuring Microsoft SQL Server using 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.

20.1. Prerequisites

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

  • Pre-configured firewall

    You must 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 1443.

    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.

20.2. Installing 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 8 AppStream repository: 

    # dnf install ansible-core

  2. Install Microsoft SQL Server Ansible role: 

    # dnf install ansible-collection-microsoft-sql

20.3. Installing and configuring SQL server using 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: 1443
  roles:
    - microsoft.sql.server

    Replace <password> with your SQL Server password.

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

    # ansible-playbook mssql-server.yml

20.4. TLS variables

The following variables are available for configuring the Transport Level Security (TLS).

Table 20.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
Note

You must have the TLS certificate and private key on the Ansible control node.

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_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.

20.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 20.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.

20.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 20.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.