# The latest powershell gives us more flexiablity to use 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

# Powershell 5.0 requires a reboot, so lets get it done if it's needed.
- name: Windows | Reboot to complete Powershell 5.0 install
  win_reboot:
    # We will give windows a full hour to reboot.
    reboot_timeout: 3600
    post_reboot_delay: 60
  when: check_powershell5.changed

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

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

# Temporary path to store downloader
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 in MB
# values must be divisible by 512

# Max memory to allocate to this instance
mssql_max_server_memory: 1024

# Memory to reserve to the OS
mssql_os_memory_reservation: 512

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

# Suppress reboots that may occur during SQL Setup tasks
# you will want to set this to True if working on a sensitive system:
mssql_suppress_reboot: False

### Service Accounts ###

# SQL Service Account
# regex statements used in some steps expect the format of CONTOSO\
# do not use @CONTOSO.com for these accounts as SQL install will fail
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 indicates mixed-mode auth, while Windows indicates Windows Auth.
mssql_security_mode: sql

# SA user password, if security mode is set to 'SQL'
# by default for testing we'll be lazy and use the service account password,
# but on live systems you should use something else:
mssql_sa_password: "{{ mssql_sqlsvc_account_pass }}"

# features - Comma seperated list of features to be installed
#
# example:   
# mssql_features: SQLENGINE,AS
# 
# The list of features below is untested, some may not work with DSC
#
# Features list: 
#
# Database engine = SQLENGINE
# Replication = REPLICATION
# Full-text and semantic extractions for search = FULLTEXT
# Data quality services = DQ
# Analysis services = AS
# Reporting services – native = RS
# Reporting services – sharepoint = RS_SHP
# Reporting services add-in for sharepoint products = RS_SHPWFE
# Data quality client = DQC
# SQL Server data tools = BIDS
# Client tools connectivity = CONN
# Integration services = IS
# Client tools backwards compatibility = BC
# Client tools SDK = SDK
# Documentation components = BOL
# Management tools – basic = SSMS
# Management tools – advanced = ADV_SSMS
# Distributed replay controller = DREPLAY_CTLR
# Distributed replay client = DREPLAY_CLT
# SQL client connectivity SDK = SNAC_SDK
# Master data services = MDS
# ADVANCEDANALYTICS 	Installs R Services, requires the database engine. Unattended installations require /IACCEPTROPENLICENSETERMS parameter.   

mssql_features: SQLENGINE,FULLTEXT,CONN

# Collation
mssql_collation: SQL_Latin1_General_CP1_CI_AS

# Browser service startup mode
# Specifies the startup mode for SQL Server Browser service. { Automatic | Disabled | 'Manual' }
mssql_browsersvc_mode: Automatic

# Default Account Access
# Ansible_Admin must be included so that the playbook can make 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

# When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, 
# it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, 
# for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors 
# to use in parallel plan execution. 
#
# If the affinity mask option is not set to the default, it may restrict the number of processors available to 
# SQL Server on symmetric multiprocessing (SMP) systems. 
#
# To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. 
#
# See: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option
mssql_max_degree_of_parallelism: 0

# Minimum memory to allocate to SQL
#
# Should remain 0 in most cases.
#
# see: Optimizing Server Performance Using Memory Configuration Options
# https://technet.microsoft.com/en-us/library/ms177455(v=sql.105).aspx
#
# The min server memory server configuration option can be used to ensure that 
# SQL Server does not release memory below the configured minimum server memory 
# once that threshold is reached. This configuration option can be set to a specific value 
# based on the size and activity of your SQL Server. If you choose to set this value, 
# set it to some reasonable value to ensure that the operating system does not request too 
# much memory from SQL Server, which can affect SQL Server performance.
mssql_min_server_memory: 0

- name: SQL Server
  hosts: sql_server
  tags: mssql

  roles:
  - { role: kkolk.mssql }