Skip to content

Microsoft SQL Role Usage Guide

Requirements

  1. Outbound access to cdn.powershellgallery.com and www.powershellgallery.com to download required PowerShell modules. If outbound access is not available, the required modules can be manually installed. The required modules are:
    • SqlServer
    • SqlServerDsc
    • ServerManager
    • StorageDsc
    • dbatools
    • xNetworking

Prerequisites

  1. Storage must be provisioned on the host(s) Microsoft SQL will be installed on. This can be done using the storage roles. If ephemeral storage is being used for tempdb, it must be manually configured using AWS/Azure best practices.
  2. Required AD accounts (e.g. GMSAs) must be created. The create-gmsa.yml playbook can be used to create GMSAs in Active Directory.
  3. Either a customer provided ISO or a copy of Microsoft SQL Server Developer Edition (e.g. SQLServer2022-x64-ENU-Dev.iso).
  4. The following variables must be defined. Use a secure password generator to create strong passwords. The variables should be all uppercase when defined as environment variables, but all lowercase when defined in host_vars or group_vars files or an ansible vault.
# REQUIRED VARIABLES
export MICROSOFT_SQL_SPADMIN_PASSWORD='USE_PASSWORD_GENERATOR'
export MICROSOFT_SQL_SPUSER_PASSWORD='USE_PASSWORD_GENERATOR'
export MICROSOFT_SQL_SA_PASSWORD='USE_PASSWORD_GENERATOR'

# OPTIONAL VARIABLES
export MICROSOFT_SQL_LICENSE_KEY='XXXXX-XXXXX-XXXXX-XXXXX-XXXXX'

# REQUIRED VARIABLES
microsoft_sql_spadmin_password: 'USE_PASSWORD_GENERATOR'
microsoft_sql_spuser_password: 'USE_PASSWORD_GENERATOR'
microsoft_sql_sa_password: 'USE_PASSWORD_GENERATOR'

# OPTIONAL VARIABLES
microsoft_sql_license_key: 'XXXXX-XXXXX-XXXXX-XXXXX-XXXXX'

Usage

  1. Create a file in host_vars for each host Microsoft SQL will be installed on if it doesn't already exist. The file should be named after the host's inventory name (e.g. host_vars/abcmsql01.yml).
  2. A SQL ISO is required to install Microsoft SQL Server. The playbook supports delivering it via HTTP(S) (e.g. storage account, S3 bucket, caddy file server, etc.) or by uploading it from the Ansible control node.
    • When uploading files from the Ansible control node, place the ISO in a "shared files" directory on the ansible control node (usually a location on the container's persistent storage in /home/ansible/source/files) and set shared_files_directory in group_vars to the path of the directory. If the microsoft_sql_install_source_dir variable is defined in host_vars, it will override shared_files_directory for that host.
    • If the value of shared_files_directory or microsoft_sql_install_source_dir is a URL, the playbook will attempt to download the ISO from that location. If it's a local file path, the playbook will attempt to upload the ISO from the Ansible control node. microsoft_sql_install_source_file should be set to the name of the ISO file (e.g. 'SQLServer2019.iso').

  3. Update each hosts host_vars with the necessary variables using the sample variables below or navigate to templates to see more examples.

    SQL Template Variables

    If you are deploying a multipurpose SQL server for Kuiper/System Pulse, ensure variables in the template are configured to grant database permissions to both the user account running the Kuiper installation Ansible role and the Kuiper GMSA.

  4. Run the playbooks/deploy-microsoft-sql.yml playbook. This playbook calls the microsoft-sql role to install Microsoft SQL and dependencies, configure databases, users, permissions, firewall rules, etc. It also contains some tasks specific to Kuiper/System Pulse deployments. 

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

Sample Variables

SQL Template Variables

The sample below is for a Kuiper/System Pulse multipurpose SQL Deployment. Be sure to customize environment specific variables in the template. Many of the lines containing environment specific values are highlighted in the code block below. See comments for more information.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
# These are sample storage variables used by the storage provisioning roles. They're included here to show how the storage provisioning roles can be used to set up the storage, but are not required to be defined in the SQL playbook variables if the storage is already set up or will be set up separately.

volumes:
  disk1:
    DriveLetter: G
    FSLabel: Data
    AllocationUnitSize: 65536
  disk2:
    DriveLetter: H
    FSLabel: Log
    AllocationUnitSize: 65536
  disk3:
    DriveLetter: S
    FSLabel: SQL_Server
  # TEMPDB STORAGE IS NOT SHOWN HERE BECAUSE IT'S RECOMMENDED TO USE EPHEMERAL STORAGE

microsoft_sql_install_source_file: SQLServer2022-x64-ENU-Dev.iso

# MAKE SURE DRIVE LETTERS IN THE STORAGE VARIABLES MATCH THE DRIVE LETTERS IN THE SQL VARIABLES BELOW
microsoft_sql_dsc_sqltempdbdir: 'T:\TempDB'    # RECOMMENDED TO USE EPHEMERAL
microsoft_sql_dsc_sqltempdblogdir: 'T:\TempDB' # STORAGE FOR TEMPDB
microsoft_sql_dsc_installsqldatadir: 'S:\SQL Server Data'
microsoft_sql_dsc_instancename: MSSQLSERVER
microsoft_sql_dsc_sqlbackupdir: 'S:\SQL Server Backups'
microsoft_sql_dsc_sqluserdbdir: 'G:\Data'
microsoft_sql_dsc_sqluserdblogdir: 'H:\Log'
microsoft_sql_dsc_sqlsysadminaccounts: # THE SQL SETUP REQUIRES AT LEAST ONE SYSTEM ADMINISTRATOR ACCOUNT
  - sapphire\epicadmin

# OPTIONALLY OVERRIDE THE SOURCE AND VERSION OF SQL SERVER MANAGEMENT STUDIO TO INSTALL
# smss_install_source_dir: https://aka.ms/ssms/22/release
# smss_install_file: vs_SSMS.exe

service_username: sapphire\kuiper-gmsa$

# Create Databases
microsoft_sql_databases:
  - name: 'Kuiper'
    db_size: 4096 MB
    db_max_size: 10240 MB
    db_growth: 256 MB
    log_size: 1024 MB
    log_max_size: 10240 MB
    log_growth: 500 MB
    roles:
      - name: db_owner
        members:
          - sapphire\epicadmin # required while running the Kuiper install for SQL test to succeed
          - sapphire\kuiper-gmsa$
      - name: db_ddladmin
        members:
          - sapphire\kuiper-gmsa$
      - name: db_datawriter
        members:
          - sapphire\kuiper-gmsa$
      - name: db_datareader
        members:
          - sapphire\kuiper-gmsa$
    users:
      - sapphire\epicadmin # required while running the Kuiper install for SQL test to succeed
      - sapphire\kuiper-gmsa$
  - name: 'SystemPulse'
    db_size: 4096 MB
    db_max_size: 10240 MB
    db_growth: 256 MB
    log_size: 1024 MB
    log_max_size: 10240 MB
    log_growth: 500 MB
    roles:
      - name: db_owner
        members:
          - spadmin
      - name: db_datawriter
        members:
          - spuser
      - name: db_datareader
        members:
          - spuser
      - name: sp_user
        members:
          - spuser
    users:
      - spadmin
      - spuser