133 lines
5.0 KiB
YAML
133 lines
5.0 KiB
YAML
---
|
|
- name: Create SQL Server data directories
|
|
ansible.windows.win_file:
|
|
path: "{{ item }}"
|
|
state: directory
|
|
loop:
|
|
- "{{ mssql_data_dir }}"
|
|
- "{{ mssql_log_dir }}"
|
|
- "{{ mssql_backup_dir }}"
|
|
- "{{ mssql_tempdb_dir }}"
|
|
- "{{ mssql_tempdb_log_dir }}"
|
|
|
|
- name: Install SQL Server 2022
|
|
block:
|
|
- name: Run SQL Server 2022 setup
|
|
ansible.windows.win_powershell:
|
|
script: |
|
|
$ErrorActionPreference = 'Stop'
|
|
|
|
$sapwd = '{{ mssql_sa_password | replace("'", "''") }}'
|
|
|
|
$proc = Start-Process `
|
|
-FilePath '{{ mssql_iso_drive }}\setup.exe' `
|
|
-ArgumentList @(
|
|
'/Action=Install',
|
|
'/QUIET',
|
|
'/IACCEPTSQLSERVERLICENSETERMS',
|
|
'/FEATURES={{ mssql_features }}',
|
|
'/INSTANCENAME={{ mssql_instance_name }}',
|
|
'/SQLCOLLATION={{ mssql_collation }}',
|
|
'/SECURITYMODE={{ mssql_security_mode }}',
|
|
"/SAPWD=$sapwd",
|
|
'/SQLSYSADMINACCOUNTS="{{ mssql_sysadmin_accounts | join('" "') }}"',
|
|
'/SQLSVCACCOUNT="{{ mssql_sql_svc_account }}"',
|
|
'/AGTSVCACCOUNT="{{ mssql_agt_svc_account }}"',
|
|
'/AGTSVCSTARTUPTYPE=Automatic',
|
|
'/BROWSERSVCSTARTUPTYPE=Automatic',
|
|
'/TCPENABLED={{ mssql_tcp_enabled }}',
|
|
'/SQLUSERDBDIR="{{ mssql_data_dir }}"',
|
|
'/SQLUSERDBLOGDIR="{{ mssql_log_dir }}"',
|
|
'/SQLBACKUPDIR="{{ mssql_backup_dir }}"',
|
|
'/SQLTEMPDBDIR="{{ mssql_tempdb_dir }}"',
|
|
'/SQLTEMPDBLOGDIR="{{ mssql_tempdb_log_dir }}"',
|
|
'/ASSVCACCOUNT="{{ mssql_as_svc_account }}"',
|
|
'/ASSERVERMODE={{ mssql_as_server_mode }}',
|
|
'/ASSYSADMINACCOUNTS="{{ mssql_sysadmin_accounts | join('" "') }}"',
|
|
'/ISSVCACCOUNT="{{ mssql_is_svc_account }}"',
|
|
'/UPDATEENABLED=False'
|
|
) `
|
|
-Wait -PassThru
|
|
|
|
if ($proc.ExitCode -notin @(0, 3010)) {
|
|
throw "SQL Server setup failed with exit code: $($proc.ExitCode)"
|
|
}
|
|
|
|
$Ansible.Result = $proc.ExitCode
|
|
register: mssql_install_result
|
|
timeout: 3600
|
|
|
|
- name: Report install exit code
|
|
ansible.builtin.debug:
|
|
msg: "SQL Server installed (exit code {{ mssql_install_result.result }}{% if mssql_install_result.result == 3010 %} — reboot required{% endif %})"
|
|
|
|
rescue:
|
|
- name: Display setup summary log
|
|
ansible.windows.win_powershell:
|
|
script: |
|
|
$log = Get-ChildItem 'C:\Program Files\Microsoft SQL Server' -Filter 'Summary*.txt' `
|
|
-Recurse -ErrorAction SilentlyContinue |
|
|
Sort-Object LastWriteTime | Select-Object -Last 1
|
|
if ($log) {
|
|
Write-Output "=== $($log.FullName) ==="
|
|
Get-Content $log.FullName | Select-Object -Last 100
|
|
} else {
|
|
Write-Output "No summary log found."
|
|
}
|
|
register: setup_log_content
|
|
|
|
- name: Show log
|
|
ansible.builtin.debug:
|
|
msg: "{{ setup_log_content.output }}"
|
|
|
|
- name: Fail
|
|
ansible.builtin.fail:
|
|
msg: "SQL Server installation failed — see log output above."
|
|
|
|
- name: Ensure SQL Server service is started
|
|
ansible.windows.win_service:
|
|
name: "{{ 'MSSQLSERVER' if mssql_instance_name == 'MSSQLSERVER' else 'MSSQL$' + mssql_instance_name }}"
|
|
state: started
|
|
start_mode: auto
|
|
|
|
- name: Ensure SQL Server Agent is started
|
|
ansible.windows.win_service:
|
|
name: "{{ 'SQLSERVERAGENT' if mssql_instance_name == 'MSSQLSERVER' else 'SQLAgent$' + mssql_instance_name }}"
|
|
state: started
|
|
start_mode: auto
|
|
|
|
- name: Open SQL Server firewall port
|
|
community.windows.win_firewall_rule:
|
|
name: "SQL Server ({{ mssql_instance_name }}) TCP {{ mssql_tcp_port }}"
|
|
localport: "{{ mssql_tcp_port }}"
|
|
action: allow
|
|
direction: in
|
|
protocol: tcp
|
|
state: present
|
|
enabled: true
|
|
|
|
- name: Configure SQL Server memory limits
|
|
ansible.windows.win_powershell:
|
|
script: |
|
|
$ErrorActionPreference = 'Stop'
|
|
|
|
$sqlcmd = Get-ChildItem 'C:\Program Files\Microsoft SQL Server' -Filter 'sqlcmd.exe' `
|
|
-Recurse -ErrorAction SilentlyContinue |
|
|
Where-Object { $_.FullName -notlike '*\x86\*' } |
|
|
Sort-Object LastWriteTime -Descending |
|
|
Select-Object -First 1 -ExpandProperty FullName
|
|
if (-not $sqlcmd) { throw 'sqlcmd.exe not found under C:\Program Files\Microsoft SQL Server' }
|
|
|
|
$instance = if ('{{ mssql_instance_name }}' -eq 'MSSQLSERVER') { '.' } `
|
|
else { '.\{{ mssql_instance_name }}' }
|
|
|
|
$queries = @(
|
|
"EXEC sp_configure 'show advanced options', 1; RECONFIGURE;",
|
|
"EXEC sp_configure 'max server memory (MB)', {{ mssql_max_memory_mb }}; RECONFIGURE;",
|
|
"EXEC sp_configure 'min server memory (MB)', {{ mssql_min_memory_mb }}; RECONFIGURE;"
|
|
)
|
|
foreach ($q in $queries) {
|
|
& $sqlcmd -S $instance -E -Q $q | Out-Null
|
|
}
|
|
$Ansible.Result = "max={{ mssql_max_memory_mb }} MB, min={{ mssql_min_memory_mb }} MB"
|