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.
mssql_tcp_port
variable. The default port is 1443..sql
extension containing these SQLmssql_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
#####-#####-#####-#####-#####
, where #
is aDefault: 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:
traceflag 3979 on
setting to enable trace flag 3979 as a startupcontrol.alternatewritethrough
setting to 0
control.writethrough
setting to 1
When set to false
, the role applies the following settings:
traceflag 3982 off
parameter to disable trace flag 3979 as acontrol.alternatewritethrough
setting to its default value 0
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:
/etc/pki/tls/certs/
and /etc/pki/tls/private/
directories respectivelyWhen 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:
mssql_ha_listener_port
variable.high-availability
service in firewall.mssql_ha_login
variable forha_cluster
role to configure Pacemaker.mssql_ha_cluster_run_role
to true
mssql_ha_cluster_password
mssql_ha_cluster_virtual_ip
mssql_ha_cluster_stonith_resources
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 exampleocf:pacemaker:Dummy
or stonith:fence_xvm
. It is mandatory to usestonith:
for stonith agents. For resource agents, it is possible to use aDummy
instead of ocf:pacemaker:Dummy
. However, ifinstance_attrs
(optional) - List of sets of the resource’s instancemeta_attrs
(optional) - List of sets of the resource’s meta attributes.operations
(optional) - List of the resource’s operations.
action
(mandatory) - Operation action as defined by Pacemaker and theattrs
(mandatory) - Operation options, at least one option must beExample 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
This section outlines example playbooks that you can use as a reference.
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
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
This example shows how to use the role to set up SQL Server and enable the
following additional functionality:
- 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
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
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
MIT