Microsoft SQL Server

This role installs, configures, and starts Microsoft SQL Server (MSSQL).

The role also optimizes the operating system to improve performance and throughput for MSSQL by applying the mssql Tuned profile.

The role currently uses MSSQL version 2019 only.

Requirements

• MSSQL requires a machine with at least 2000 megabytes of memory.
• You must configure the firewall to enable connections on the MSSQL TCP port that you set with the mssql_tcp_port variable. The default port is 1443.
• Optional: If you want to input SQL statements and stored procedures to MSSQL, you must create a file with the .sql extension containing these SQL statements and procedures.

Role Variables

mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula

Set this variable to true to indicate that you accept EULA for installing the msodbcsql17 package.

The license terms for this product can be downloaded from https://aka.ms/odbc17eula and found in /usr/share/doc/msodbcsql17/LICENSE.txt.

Default: false

Type: bool

mssql_accept_microsoft_cli_utilities_for_sql_server_eula

Set this variable to true to indicate that you accept EULA for installing the mssql-tools package.

The license terms for this product can be downloaded from http://go.microsoft.com/fwlink/?LinkId=746949 and found in /usr/share/doc/mssql-tools/LICENSE.txt.

Default: false

Type: bool

mssql_accept_microsoft_sql_server_2019_standard_eula

Set this variable to true to indicate that you accept EULA for using the mssql-conf utility.

The license terms for this product can be found in /usr/share/doc/mssql-server or downloaded from https://go.microsoft.com/fwlink/?LinkId=2104078&clcid=0x409. The privacy statement can be viewed at https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409.

Default: false

Type: bool

mssql_password

The password for the database sa user. The password must have a minimum length of 8 characters, include uppercase and lowercase letters, base 10 digits or non-alphanumeric symbols. Do not use single quotes ('), double quotes ("), and spaces in the password because sqlcmd cannot authorize when the password includes those symbols.

This variable is required when you run the role to install MSSQL.

When running this role on a host that has MSSQL installed, the mssql_password variable overwrites the existing sa user password to the one that you specified.

Default: null

Type: str

mssql_edition

The edition of MSSQL to install.

This variable is required when you run the role to install MSSQL.

Use one of the following values:

• Enterprise
• Standard
• Web
• Developer
• Express
• Evaluation
• A product key in the form #####-#####-#####-#####-#####, where # is a number or a letter. For more information, see https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables?view=sql-server-ver15.

Default: null

Type: str

mssql_tcp_port

The port that MSSQL listens on.

If you define this variable, the role configures MSSQL with the defined TCP port.

If you do not define this variable when installing MSSQL, the role configures MSSQL to listen on the MSSQL default TCP port 1443.

If you do not define this variable when configuring running MSSQL, the role does not change the TCP port setting on MSSQL.

Default: null

Type: str

mssql_ip_address

The IP address that MSSQL listens on.

If you define this variable, the role configures MSSQL with the defined IP address.

If you do not define this variable when installing MSSQL, the role configures MSSQL to listen on the MSSQL default IP address 0.0.0.0, that is, to listen on every available network interface.

If you do not define this variable when configuring running MSSQL, the role does not change the IP address setting on MSSQL.

Default: null

Type: str

mssql_input_sql_file

You can use the role to input a file containing SQL statements or procedures into MSSQL. With this variable, enter the path to the SQL file containing the database configuration.

When specifying this variable, you must also specify the mssql_password variable because authentication is required to input an SQL file to MSSQL.

If you do not pass this variable, the role only configures the MSSQL Server and does not input any SQL file.

Note that this task is not idempotent, the role always inputs an SQL file if this variable is defined.

You can find an example of the SQL file at tests/sql_script.sql.

Default: null

Type: str

mssql_enable_sql_agent

Set this variable to true or false to enable or disable the SQL agent.

Default: null

Type: bool

mssql_install_fts

Set this variable to true or false to install or remove the mssql-server-fts package that provides full-text search.

Default: null

Type: bool

mssql_enable_ha

Set this variable to true or false to install or remove the mssql-server-ha package and enable or disable the hadrenabled setting.

Default: null

Type: bool

mssql_tune_for_fua_storage

Set this variable to true or false to enable or disable settings that improve performance on hosts that support Forced Unit Access (FUA) capability.

Only set this variable to true if your hosts are configured for FUA capability.

When set to true, the role applies the following settings:

• Set the traceflag 3979 on setting to enable trace flag 3979 as a startup parameter
• Set the control.alternatewritethrough setting to 0
• Set the control.writethrough setting to 1

When set to false, the role applies the following settings:

• Set the traceflag 3982 off parameter to disable trace flag 3979 as a startup parameter
• Set the control.alternatewritethrough setting to its default value 0
• Set the control.writethrough setting to its default value 0

For more details, see SQL Server and Forced Unit Access (FUA) I/O subsystem capability at https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-performance-best-practices?view=sql-server-ver15.

Default: null

Type: bool

mssql_tls_enable

Use the variables starting with mssql_tls to configure MSSQL to encrypt connections using TLS certificates. You must have the TLS certificate and private key on the Ansible control node.

When you use this variable, the role copies TLS cert and private key files to MSSQL and configures MSSQL to use these files to encrypt connections.

Set to true or false to enable or disable TLS encryption.

When set to true, the role performs the following tasks:

1. Copies TLS certificate and private key files to MSSQL to the /etc/pki/tls/certs/ and /etc/pki/tls/private/ directories respectively
2. Configures MSSQL to encrypt connections using the copied TLS certificate and private key

When set to false, the role configures MSSQL to not use TLS encryption. The role does not remove the existing certificate and private key files if this variable is set to false.

Default: null

Type: bool

mssql_tls_cert

Path to the certificate file to copy to MSSQL.

Default: null

Type: str

mssql_tls_private_key

Path to the private key file to copy to MSSQL.

Default: null Type: str

mssql_tls_version

TLS version to use.

Default: 1.2

Type: str

mssql_tls_force

Set to true to replace the existing certificate and private key files on host if they exist at /etc/pki/tls/certs/ and /etc/pki/tls/private/ respectively.

Default: false

Type: bool

Example Playbook

- 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_2019_standard_eula: true
    mssql_password: "p@55w0rD"
    mssql_edition: Evaluation
    mssql_tcp_port: 1433
    mssql_ip_address: 0.0.0.0
    mssql_input_sql_file: mydatabase.sql
    mssql_enable_sql_agent: true
    mssql_install_fts: true
    mssql_tune_for_fua_storage: true
    mssql_tls_enable: true
    mssql_tls_cert: mycert.pem
    mssql_tls_private_key: mykey.key
    mssql_tls_version: 1.2
    mssql_tls_force: false
  roles:
    - linux-system-roles.mssql

License

MIT