Skip to content

Tags and Optional Variables

This page documents the tags exposed by the microsoft_sql role and the optional variables that can be used to customize a deployment. For required variables, the database object structure, and full sample variables, see the Usage Guide.

Tags

Each stage of the role is wrapped in an included task file with its own tag. Use --tags to run only specific stages or --skip-tags to run everything except the listed stages.

Tag Stage
install_dependencies Install required PowerShell modules and Windows features (e.g. WAS).
install_sql Download/mount the SQL media and install Microsoft SQL Server.
configure_firewall Open the firewall for SQL Server (TCP 1433-1434).
install_ssms Download and install SQL Server Management Studio (SSMS).
sql_users Create SQL logins (local SQL and domain users).
sql_databases Create databases and configure their data/log file sizes.
sql_permissions Add users to databases and assign database roles.

Tag behavior

The tasks inside each stage are tagged always, so once a stage is selected its tasks all run. This means selecting a single tag runs that stage end-to-end while skipping the others.

ansible-playbook --limit=epic-msql-sapph.sapphire.dev \
  playbooks/deploy-microsoft-sql.yml --tags install_sql

ansible-playbook --limit=epic-msql-sapph.sapphire.dev \
  playbooks/deploy-microsoft-sql.yml --tags "sql_users,sql_databases,sql_permissions"

ansible-playbook --limit=epic-msql-sapph.sapphire.dev \
  playbooks/deploy-microsoft-sql.yml --skip-tags install_ssms

Optional Variables

All variables below are optional and fall back to the defaults shown. Override them in host_vars, group_vars, or an Ansible Vault as needed.

Installation Source

Variable Default Description
microsoft_sql_install_source_dir {{ shared_files_directory }} Directory or URL the SQL media is retrieved from. A URL is downloaded on the host; a path is uploaded from the control node.
microsoft_sql_install_source_file (none — required) Name of the SQL install file (e.g. SQLServer2022-x64-ENU-Dev.iso). ISO files are mounted; other files are extracted.
microsoft_sql_temp_dir C:/Windows/Temp/sql_install Working directory on the host for downloaded/extracted install media.
microsoft_sql_force_download false Re-download the install media even if it already exists in microsoft_sql_temp_dir.
microsoft_sql_delete_temp_dir true Delete microsoft_sql_temp_dir after the install/SSMS stages complete.

Install Behavior

Variable Default Description
microsoft_sql_storage_check true Verify at least two partitions exist before installing. Set to false to skip the storage check.
microsoft_sql_install_psmodules [SQLServerDsc, SqlServer, StorageDsc, ServerManager, dbatools, xNetworking] PowerShell modules installed during the install_dependencies stage.

SQL Server (DSC) Configuration

These map to the SqlSetup DSC resource.

Variable Default Description
microsoft_sql_dsc_resource_name SQLSetup Name of the DSC resource used to install SQL Server.
microsoft_sql_dsc_action Install DSC setup action.
microsoft_sql_dsc_instancename MSSQLSERVER SQL Server instance name.
microsoft_sql_dsc_features SQLENGINE Comma-separated SQL Server features to install.
microsoft_sql_dsc_securitymode SQL Authentication mode for the instance.
microsoft_sql_dsc_sqlsysadminaccounts (omitted) List of accounts granted sysadmin. SQL setup requires at least one.
microsoft_sql_dsc_installsqldatadir (omitted) Root data directory for the instance.
microsoft_sql_dsc_sqluserdbdir (omitted) Default directory for user database data files.
microsoft_sql_dsc_sqluserdblogdir (omitted) Default directory for user database log files.
microsoft_sql_dsc_sqltempdbdir T:\TempDB Directory for tempdb data files.
microsoft_sql_dsc_sqltempdblogdir T:\TempDB Directory for tempdb log files.
microsoft_sql_dsc_sqlbackupdir (omitted) Default backup directory.

SQL Server Management Studio (SSMS)

Variable Default Description
microsoft_sql_install_smss true Install SSMS as part of the run. Set to false to skip the SSMS stage.
microsoft_sql_smss_install_source_dir https://aka.ms/ssms/22/release Directory or URL the SSMS installer is retrieved from.
microsoft_sql_smss_install_source_file vs_SSMS.exe Name of the SSMS installer file.

Skipping the SSMS install

Set microsoft_sql_install_smss: false to skip SSMS for a host, or run the playbook with --skip-tags install_ssms for a one-off run.

Authentication and Licensing

The sa password and per-login passwords may each be supplied as an Ansible variable or an environment variable. When both are set, the Ansible variable takes precedence and the environment variable is used as a fallback.

Ansible Variable Environment Variable Description
microsoft_sql_sa_password MICROSOFT_SQL_SA_PASSWORD Password for the SQL sa account. Required.
microsoft_sql_<user>_password MICROSOFT_SQL_<USER>_PASSWORD Password for each local SQL login (e.g. microsoft_sql_spadmin_password). Alternatively set a password attribute on the user.
microsoft_sql_license_key MICROSOFT_SQL_LICENSE_KEY Product key for licensed editions. Optional — omitted when unset.

Password precedence for SQL logins

For local SQL logins the resolution order is: the password attribute on the user object, then the microsoft_sql_<user>_password Ansible variable, then the MICROSOFT_SQL_<USER>_PASSWORD environment variable. Store secrets in an Ansible Vault rather than committing them.