Microsoft SQL Server

This role installs, configures, and starts Microsoft SQL Server.

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

The role currently works with SQL Server 2017 and 2019.

Requirements

SQL Server requires a machine with at least 2000 megabytes of memory.
You must configure the firewall to enable connections on the SQL Server TCP port that you set with the mssql_tcp_port variable. The default port is 1443.
Optional: If you want to input T-SQL statements and stored procedures to SQL Server, 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_standard_eula`

Set this variable to true to indicate that you accept EULA for using Microsoft SQL Server.

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_version`

The version of the SQL Server to configure. The role currently supports versions 2017 and 2019.

Default: 2019

Type: int

`mssql_upgrade`

If you want to upgrade your SQL Server 2017 to 2019, set the mssql_version variable to 2019 and this variable to true.

Note that the role does not support downgrading SQL Server.

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 SQL Server.

Default: null

Type: str

`mssql_edition`

The edition of SQL Server to install.

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

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 SQL Server listens on.

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

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

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

Default: null

Type: str

`mssql_ip_address`

The IP address that SQL Server listens on.

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

If you do not define this variable when installing SQL Server, the role configures SQL Server to listen on the SQL Server 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 SQL Server, the role does not change the IP address setting on SQL Server.

Default: null

Type: str

`mssql_input_sql_file`

You can use the role to input a file containing SQL statements or procedures into SQL Server. 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 SQL Server.

If you do not pass this variable, the role only configures the SQL 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.

When this variable is defined, mssql_debug is set to true to print the output of the sqlcmd command.

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

Default: null

Type: str

`mssql_debug`

Whether or not to print the output of sqlcmd commands. The role inputs SQL scripts with the sqlcmd command to configure SQL Server or to input users' SQL scripts when the mssql_input_sql_file variable is provided.

Default: true if mssql_input_sql_file is defined else false

Type: bool

`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_install_powershell`

Set this variable to true or false to install or remove the powershell package that provides PowerShell.

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 with the mssql_tls_ prefix to configure SQL Server to encrypt connections using TLS certificates.

You are responsible for creating and securing TLS certificate and private key files. It is assumed you have a CA that can issue these files. If not, you can use the openssl command to create these files.

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

When you use this variable, the role copies TLS cert and private key files to SQL Server and configures SQL Server 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:

Copies TLS certificate and private key files to SQL Server to the /etc/pki/tls/certs/ and /etc/pki/tls/private/ directories respectively
Configures SQL Server to encrypt connections using the copied TLS certificate and private key

When set to false, the role configures SQL Server 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 SQL Server.

Default: null

Type: str

`mssql_tls_private_key`

Path to the private key file to copy to SQL Server.

Default: null Type: str

`mssql_tls_remote_src`

Influence whether files provided with mssql_tls_cert and mssql_tls_private_key need to be transferred or already are present remotely.

If false, the role searches for mssql_tls_cert and mssql_tls_private_key files on the controller node.

If true, the role searches for mssql_tls_cert and mssql_tls_private_key on managed nodes.

Default: false

Type: bool

`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

`mssql_rpm_key`

The URL or path to the Microsoft rpm gpg keys.

Default: https://packages.microsoft.com/keys/microsoft.asc

Type: string

`mssql_server_repository`

The URL to the Microsoft SQL Server repository. See vars/ for default values based on operating system.

Default: {{ __mssql_server_repository }}

Type: string

`mssql_client_repository`

The URL to the Microsoft production repository. See vars/ for default values based on operating system.

Default: {{ __mssql_client_repository }}

Type: string

`mssql_ha_configure`

Use the variables with the mssql_ha_ prefix to configure an SQL Server Always On availability group to provide high availability.

Configuring for high availability is not supported on RHEL 7 because the System Roles ha_cluster role does not support RHEL 7.

Set to true to configure for high availability. Setting to false does not remove configuration for high availability.

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

Include the System Roles firewall role to configure firewall: 1.1. Open the firewall port set with the mssql_ha_listener_port variable. 1.2. Enable the high-availability service in firewall.
Configure SQL Server for high availability: 2.1. Enable AlwaysOn Health events. 2.2. Create certificate on the primary replica and distribute to other replicas. 2.3. Configure endpoint and availability group. 2.4. Configure the user provided with the mssql_ha_login variable for Pacemaker.
Optional: Include the System Roles ha_cluster role to configure Pacemaker. You must set the following variables to enable this:

Default: false

Type: bool

`mssql_ha_replica_type`

A host variable that specifies the type of the replica to be configured on this host.

See Setting Up SQL Server and Configuring for High Availability for an example inventory.

You must set the mssql_ha_replica_type variable to primary for exactly one host.

The available values are: primary, synchronous, witness.

Default: no default

Type: str

`mssql_ha_firewall_configure`

Whether to open ports in the Linux firewall for an Always On availability group.

The role uses the System Roles firewall role to manage the firewall, hence, only firewall implementations supported by the firewall role work.

If you set this variable to false, you must open the port defined with the mssql_ha_listener_port variable prior to running this role.

Default: true

Type: bool

`mssql_ha_listener_port`

The TCP port used to replicate data for an Always On availability group.

Default: 5022

Type: int

`mssql_ha_cert_name`

The name of the certificate used to secure transactions between members of an Always On availability group.

Default: null

Type: str

`mssql_ha_master_key_password`

The password to set for the master key used with the certificate.

Default: null

Type: str

`mssql_ha_private_key_password`

The password to set for the private key used with the certificate.

Default: null

Type: str

`mssql_ha_reset_cert`

Whether to reset certificates used by an Always On availability group or not.

Default: false

Type: bool

`mssql_ha_endpoint_name`

The name of the endpoint to be configured.

Default: null

Type: string

`mssql_ha_ag_name`

The name of the availability group to be configured.

Default: null

Type: string

`mssql_ha_db_name`

The name of the database to be replicated. This database must exist in SQL Server.

Default: null

Type: string

`mssql_ha_db_backup_path`

For SQL Server, any database participating in an Availability Group must be in a full recovery mode and have a valid log backup. The role uses this path to backup the database provided with mssql_ha_db_name prior to initiating replication within an Always On availability group.

The role backs up the database provided with mssql_ha_db_backup_path if no back up newer than 3 hours exists.

Default: /var/opt/mssql/data/{{ mssql_ha_db_name }}.bak

Type: string

`mssql_ha_login`

The user created for Pacemaker in SQL Server. This user is used by the SQL Server Pacemaker resource agent to connect to SQL Server to perform regular database health checks and manage state transitions from replica to primary when needed.

Default: null

Type: string

`mssql_ha_login_password`

The password for the mssql_ha_login user in SQL Server.

Default: null

Type: string

`mssql_ha_cluster_run_role`

Whether to run the ha_cluster role from this role.

Note that the ha_cluster role has the following limitation: The role replaces the configuration of HA Cluster on specified nodes. Any settings not specified in the role variables will be lost.

This means that running the microsoft.sql.server role re-writes existing Pacemaker configuration. You must verify that you want to run the ha_cluster role by setting mssql_ha_cluster_run_role: true.

If you want to run the ha_cluster role independently of the microsoft.sql.server role, or run provide custom variables for the ha_cluster role, you can work around this limitation. To do this, run the microsoft.sql.server role with mssql_ha_cluster_print_vars:true to print planned ha_cluster variables. Then you can merge the printed variables with your custom ha_cluster variables and specify the resulting set of variables with the microsoft.sql.server role invocation.

Default: false

Type: string

`mssql_ha_cluster_print_vars`

Use this variable to print ha_cluster_* variables that the microsoft.sql.server role sets when running the ha_cluster role to configure Pacemaker.

After ha_cluster_* are printed the microsoft.sql.server role exits without executing any tasks.

To print variables in a more readable manner you can configure a debug stdout callback plug-in or pipe the playbook output to | sed 's|\\n|\n|g'.

Default: false

Type: bool

`mssql_ha_cluster_password`

The password for the cluster to be created in Pacemaker.

Default: null

Type: string

`mssql_ha_cluster_virtual_ip`

A floating virtual IP address for accessing the primary SQL Server node in an Always On availability group to be created in Pacemaker.

In the event of a node failure, a synchronous replica is automatically promoted to primary and the address is assigned to this new node.

Default: null

Type: string

`mssql_ha_cluster_stonith_resources`

With this variable, define Pacemaker stonith resource to be configured by the ha_cluster role. microsoft.sql.server role feeds this as a list to the ha_cluster_resource_primitives variable.

The items are as follows:

id (mandatory) - ID of a resource.
agent (mandatory) - Name of a resource or stonith agent, for example ocf:pacemaker:Dummy or stonith:fence_xvm. It is mandatory to use stonith: for stonith agents. For resource agents, it is possible to use a short name, such as Dummy instead of ocf:pacemaker:Dummy. However, if several agents with the same short name are installed, the role will fail as it will be unable to decide which agent should be used. Therefore, it is recommended to use full names.
instance_attrs (optional) - List of sets of the resource’s instance attributes. Currently, only one set is supported. The exact names and values of attributes, as well as whether they are mandatory or not, depends on the resource or stonith agent.
meta_attrs (optional) - List of sets of the resource’s meta attributes. Currently, only one set is supported.
operations (optional) - List of the resource’s operations.
- action (mandatory) - Operation action as defined by Pacemaker and the resource or stonith agent.
- attrs (mandatory) - Operation options, at least one option must be specified.

Example of setting this variable to confiture a stonith:fence_apc_snmp agent:

mssql_ha_cluster_stonith_resources:
  id: myapc
  agent: stonith:fence_apc_snmp
  instance_attrs:
    - attrs:
        - name: ipaddr
          value: apc-switch.example.com
        - name: pcmk_host_map
          value: rhel8-node1.example.com:1;rhel8-node2.example.com:2
        - name: login
          value: apclogin
        - name: passwd
          value: apcpassword

Default: []

Type: list

Example Playbooks

This section outlines example playbooks that you can use as a reference.

Setting up SQL Server

This example shows how to use the role to set up SQL Server with the minimum required variables.

- 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: "p@55w0rD"
    mssql_edition: Evaluation
  roles:
    - microsoft.sql.server

Setting up SQL Server with Custom Network Parameters

This example shows how to use the role to set up SQL Server and configure it to use custom IP address and TCP port.

- 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: "p@55w0rD"
    mssql_edition: Evaluation
    mssql_tcp_port: 1433
    mssql_ip_address: 0.0.0.0
  roles:
    - microsoft.sql.server

Setting Up SQL Server and Enabling Additional Functionality

This example shows how to use the role to set up SQL Server and enable the following additional functionality:

Enable the SQL Agent
Install FTS
Install PowerShell
Configure SQL Server for FUA capability
After SQL Server is set up, input an SQL file to SQL Server

- 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: "p@55w0rD"
    mssql_edition: Evaluation
    mssql_enable_sql_agent: true
    mssql_install_fts: true
    mssql_install_powershell: true
    mssql_tune_for_fua_storage: true
    mssql_input_sql_file: mydatabase.sql
  roles:
    - microsoft.sql.server

Setting Up SQL Server with TLS Encryption

This example shows how to use the role to set up SQL Server and configure it to use TLS encryption.

- 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: "p@55w0rD"
    mssql_edition: Evaluation
    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:
    - microsoft.sql.server

Setting Up SQL Server and Configuring for High Availability

This example shows how to use the role to set up SQL Server and configure it for high availability.

You must set the mssql_ha_replica_type variable for each host that you want to configure.

Example inventory file with mssql_ha_replica_type set for each host:

all:
  hosts:
    host1:
      mssql_ha_replica_type: primary
    host2:
      mssql_ha_replica_type: synchronous
    host3:
      mssql_ha_replica_type: witness

When all required variables are set, you can execute a playbook.

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_standard_eula: true
    mssql_password: "p@55w0rD"
    mssql_edition: Evaluation
    mssql_ha_configure: true
    mssql_ha_firewall_configure: true
    mssql_ha_listener_port: 5022
    mssql_ha_cert_name: mssql_cert
    mssql_ha_master_key_password: "p@55w0rD1"
    mssql_ha_private_key_password: "p@55w0rD2"
    mssql_ha_reset_cert: false
    mssql_ha_endpoint_name: hadr_endpoint
    mssql_ha_ag_name: ag1
    mssql_ha_db_name: ExampleDB
    mssql_ha_db_backup_path: /var/opt/mssql/data/{{ mssql_ha_db_name }}.bak
    mssql_ha_login: pacemakerLogin
    mssql_ha_login_password: "p@55w0rD3"
    mssql_ha_cluster_run_role: false
    mssql_ha_cluster_password: "p@55w0rD4"
    mssql_ha_cluster_virtual_ip: 192.168.122.10
    mssql_ha_stonith_resources:
      id: myapc
      agent: stonith:fence_apc_snmp
      instance_attrs:
        - attrs:
            - name: ipaddr
              value: apc-switch.example.com
            - name: pcmk_host_map
              value: rhel8-node1.example.com:1;rhel8-node2.example.com:2
            - name: login
              value: apclogin
            - name: passwd
              value: apcpassword
  roles:
    - microsoft.sql.server

License

MIT