kkolk.mssql

Description

This Ansible role installs SQL Server Developer Edition 2017 on compatible Windows systems. It can also be modified to install any version of SQL Server. It has been used to install SQL Server 2012/2014 as well.

This role manages necessary local firewall settings and shows how to adjust the SQL instance configuration.

With the default settings, it is designed to work as a role that can be added to a member server in the Windows test environment detailed in my posts at http://frostbyte.us/configure-an-ansible-testing-system-on-windows-part-1/

Requirements

Powershell 5.0 / WMF 5.1 must be installed on the target host.

You can perform this in two steps with:

# Install the latest Powershell for better compatibility with Windows DSC items
- name: Windows | Install Powershell 5.0
  win_chocolatey:
    name: "powershell"
  register: check_powershell5
  become: yes
  become_user: Administrator
  become_method: runas
  retries: 3
  delay: 10

# Reboot is required to complete the Powershell installation
- name: Windows | Reboot to complete Powershell 5.0 install
  win_reboot:
    reboot_timeout: 3600
    post_reboot_delay: 60
  when: check_powershell5.changed

Role Variables

# Source for installation files 
mssql_installation_source: https://go.microsoft.com/fwlink/?linkid=853016

# Path to download installation files
mssql_installation_path: C:\SQLInstall

# Temporary path for download
mssql_temp_download_path: C:\tmp

# Instance details
mssql_instance_name: Test
mssql_drive: C
mssql_userdbvol_name: Userdbvol01
mssql_port: 1433

### Memory Configuration ###
# Memory settings in MB
# Values must be multiples of 512

# Maximum memory for this instance
mssql_max_server_memory: 1024

# Memory reserved for the operating system
mssql_os_memory_reservation: 512

# Total system memory 
mssql_total_system_memory: "{{ mssql_max_server_memory + mssql_os_memory_reservation }}"

# Suppress reboots during SQL setup; set to True for sensitive systems
mssql_suppress_reboot: False

### Service Accounts ###

# SQL Service Account, use CONTOSO\ format, not @CONTOSO.com
mssql_sqlsvc_account: CONTOSO\sql_svc
mssql_sqlsvc_account_pass: MyPlainTextPassWord01

# SQL Agent Service Account
mssql_agentsvc_account: CONTOSO\sql_agt
mssql_agentsvc_account_pass: MyPlainTextPassWord01

# SQL Analysis Services Account
mssql_assvc_account: "{{ mssql_sqlsvc_account }}"
mssql_assvc_account_pass: "{{ mssql_sqlsvc_account_pass }}"

### File and Folder Paths ###

# Volume paths
mssql_userdbvol_path: "{{ mssql_drive }}:\\{{ mssql_userdbvol_name }}"
mssql_db_accesspath: "{{ mssql_userdbvol_path }}\\DatabaseFiles"
mssql_logs_accesspath: "{{ mssql_userdbvol_path }}\\DatabaseLogs"

# Shared files paths
mssql_installshared_path: C:\Program Files\Microsoft SQL Server
mssql_installsharedwow_path: C:\Program Files (x86)\Microsoft SQL Server

# Instance path
mssql_instance_path: "C:\\Program Files\\Microsoft SQL Server\\{{ mssql_instance_name }}"

# SQL DB and Logging Paths
mssql_sqlinstalldata_path: "{{ mssql_db_accesspath }}\\{{mssql_instance_name }}"
mssql_sqluserdata_path: "{{ mssql_db_accesspath }}\\{{ mssql_instance_name }}"
mssql_sqluserlog_path: "{{ mssql_logs_accesspath }}\\{{ mssql_instance_name }}"
mssql_sqltempDB_path: "C:\\TempDBFiles\\Data\\{{mssql_instance_name }}"
mssql_sqltempDBlog_path: "C:\\TempDBFiles\\Log\\{{mssql_instance_name }}"

# Security mode: SQL for mixed-mode authentication, Windows for Windows authentication.
mssql_security_mode: sql

# SA user password; defaults to the service account password for testing.
mssql_sa_password: "{{ mssql_sqlsvc_account_pass }}"

# List of features to install, separated by commas
mssql_features: SQLENGINE,FULLTEXT,CONN

# Collation setting
mssql_collation: SQL_Latin1_General_CP1_CI_AS

# Startup mode for SQL Server Browser service
mssql_browsersvc_mode: Automatic

# Accounts with default access, Ansible_Admin is required for configuration changes post-install
mssql_sysadmin_accounts: 
  - CONTOSO\Domain Admins
  - CONTOSO\Administrator

# Analysis Services Admins (if installed)
mssql_asadmin_accounts: "{{ mssql_sysadmin_accounts }}"

# Tuning options for SQL Server
mssql_max_degree_of_parallelism: 0 # Default value allows SQL Server to handle parallelism automatically
mssql_min_server_memory: 0 # Usually stays 0

Example Playbook

- name: SQL Server
  hosts: sql_server
  tags: mssql

  roles:
  - { role: kkolk.mssql }

License

BSD / MIT

Author Information

Kevin Kolk - http://www.frostbyte.us