Chapter 23. Configuring Microsoft SQL Server using the microsoft.sql.server Ansible role
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: 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 Install Ansible Core which is available in the RHEL 7.9 AppStream repository: Install the microsoft.sql.server Ansible role: 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 Create a file with the .yml extension. For example, mssql-server.yml . 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. Run the mssql-server.yml ansible playbook: 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 variable Description 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 variable Description 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 variable Description 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 Variable Description 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.
firewall-cmd --add-port= xxxx /tcp --permanent firewall-cmd --reload *yum install ansible-core* *yum install ansible-collection-microsoft-sql* --- - 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 *ansible-playbook mssql-server.yml*