Unattended Install, Then Sensible Defaults Applied on Top
Two scripts covering the doing half of a SQL Server install: install-sql.ps1 drives setup.exe with validated, logged parameters instead of clicking through the GUI, and configure-sql.ps1 applies the recommended sp_configure settings afterward with a before/after comparison. Every change from both scripts is logged.
Every script in powershell/installation/ shipped with a corrupted double UTF-8 BOM at the very start of the file, silently breaking PowerShell’s parser on all six of them. Fixed below.
Why a Generated, Logged Install and Configure Pair Matters
- Unattended install parameters reviewed once, run consistently every time, no risk of a fat-fingered click through
setup.exe‘s GUI on server 14 of 20 - The SA password is never written to disk or logged, everything else about the install is
configure-sql.ps1shows exactly what it’s about to change before it changes it, a before/after table, not a silentRECONFIGURE
install-sql.ps1
Parameter-driven install with interactive fallback. Validates inputs before calling setup.exe, applies recommended MaxMemory/MaxDOP/CostThreshold post-install unless skipped, and logs full install output to output-files\installation\.
<#
.SYNOPSIS
Install SQL Server from setup.exe with validated parameters and post-install configuration.
.DESCRIPTION
Parameter-driven SQL Server installation with interactive fallback. Validates all inputs
before calling setup.exe, applies recommended MaxMemory/MaxDOP/CostThreshold post-install,
and logs the full install output to output-files\installation\.
Supports inline parameters, interactive prompts, or an INI answer file.
SA password is never written to disk or logged.
.PARAMETER SetupPath
Full path to SQL Server setup.exe (e.g. D:\SQL2022\setup.exe).
.PARAMETER InstanceName
SQL Server instance name. Default: MSSQLSERVER (default instance).
.PARAMETER InstallDir
SQL Server binary directory. Default: C:\Program Files\Microsoft SQL Server
.PARAMETER SystemDBDir
System database data files. Default: C:\SQLData\SystemDBs
.PARAMETER SystemLogDir
System database log files. Default: C:\SQLLogs\SystemLogs
.PARAMETER UserDBDir
User database data files. Default: C:\SQLData\UserDBs
.PARAMETER UserLogDir
User database log files. Default: C:\SQLLogs\UserLogs
.PARAMETER TempDBDir
TempDB data files. Defaults to SystemDBDir\TempDB.
.PARAMETER TempDBLogDir
TempDB log files. Defaults to SystemLogDir\TempDB.
.PARAMETER TempDBFileCount
TempDB data file count. Defaults to logical CPU count capped at 8.
.PARAMETER SAPassword
SA account password as a SecureString. Prompted interactively if not supplied.
.PARAMETER SysAdminAccounts
Windows accounts to add as sysadmin (space-separated string). Defaults to current user.
.PARAMETER ServiceAccount
Windows service account for the SQL Server engine. Default: NT Service\MSSQLSERVER (virtual account).
.PARAMETER AgtServiceAccount
Windows service account for SQL Agent. Default: NT Service\SQLSERVERAGENT.
.PARAMETER Collation
Server collation. Default: SQL_Latin1_General_CP1_CI_AS
.PARAMETER Features
Comma-separated SQL Server features to install. Default: SQLENGINE,SQLAGENT
.PARAMETER MaxMemoryGB
Max server memory in GB. Auto-calculated as (TotalRAM - 4 GB) if not supplied.
.PARAMETER MaxDOP
Max degree of parallelism. Auto-calculated from logical CPU count if not supplied.
.PARAMETER AnswerFile
Path to a SQL Server setup INI file. When supplied, most params are read from it.
SAPassword and SysAdminAccounts are still passed on the command line.
.PARAMETER SkipPostConfig
Skip the post-install sp_configure steps (MaxMemory, MaxDOP, CostThreshold).
.PARAMETER WhatIf
Preview the setup.exe command without executing it.
.EXAMPLE
# Interactive mode — prompts for everything
.\admin\installation\Install-SqlServer.ps1
.EXAMPLE
# Unattended
.\admin\installation\Install-SqlServer.ps1 `
-SetupPath D:\SQL2022\setup.exe `
-SAPassword (Read-Host 'SA password' -AsSecureString)
.EXAMPLE
# Answer-file mode
.\admin\installation\Install-SqlServer.ps1 `
-SetupPath D:\SQL2022\setup.exe `
-AnswerFile .\admin\installation\templates\sql-server-install-default.ini `
-SAPassword (Read-Host 'SA password' -AsSecureString)
.NOTES
RiskLevel : HIGH IMPACT - runs setup.exe and installs a SQL Server instance
#>
param(
[string]$SetupPath,
[string]$InstanceName = 'MSSQLSERVER',
[string]$InstallDir = 'C:\Program Files\Microsoft SQL Server',
[string]$SystemDBDir = 'C:\SQLData\SystemDBs',
[string]$SystemLogDir = 'C:\SQLLogs\SystemLogs',
[string]$UserDBDir = 'C:\SQLData\UserDBs',
[string]$UserLogDir = 'C:\SQLLogs\UserLogs',
[string]$TempDBDir,
[string]$TempDBLogDir,
[int]$TempDBFileCount = 0,
[System.Security.SecureString]$SAPassword,
[string]$SysAdminAccounts,
[string]$ServiceAccount = 'NT Service\MSSQLSERVER',
[string]$AgtServiceAccount = 'NT Service\SQLSERVERAGENT',
[string]$Collation = 'SQL_Latin1_General_CP1_CI_AS',
[string]$Features = 'SQLENGINE,SQLAGENT',
[int]$MaxMemoryGB = 0,
[int]$MaxDOP = 0,
[string]$AnswerFile,
[switch]$SkipPostConfig,
[switch]$WhatIf
)
$ErrorActionPreference = 'Stop'
# ── Pre-check: admin elevation ────────────────────────────────────────────────
$isAdmin = ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole(
[Security.Principal.WindowsBuiltInRole]::Administrator)
if (-not $isAdmin) {
Write-Host 'ERROR: This script must be run as Administrator.' -ForegroundColor Red
exit 1
}
# ── Logging setup ─────────────────────────────────────────────────────────────
$repoRoot = Resolve-Path (Join-Path $PSScriptRoot '..\..')
$logDir = Join-Path $repoRoot 'output-files\installation'
New-Item -ItemType Directory -Path $logDir -Force | Out-Null
$ts = Get-Date -Format 'yyyyMMdd-HHmmss'
$logFile = Join-Path $logDir "install-$InstanceName-$ts.log"
function Write-DbaLog {
param([string]$Msg, [string]$Color = 'White')
$line = "[$(Get-Date -Format 'HH:mm:ss')] $Msg"
Write-Host $line -ForegroundColor $Color
Add-Content -Path $logFile -Value $line
}
function Confirm-Directory {
param([string]$Path, [string]$Label)
if (-not $Path -or $Path.Trim() -eq '') {
Write-Host "ERROR: $Label cannot be empty." -ForegroundColor Red
return $false
}
if (-not (Test-Path $Path)) {
Write-Host "$Label '$Path' does not exist. Creating..." -ForegroundColor Yellow
try { New-Item -ItemType Directory -Path $Path -Force | Out-Null }
catch { Write-Host "ERROR: Failed to create $Label at '$Path'." -ForegroundColor Red; return $false }
}
# Disk space check — warn if less than 20 GB free on that drive
$drive = Split-Path -Qualifier $Path
$freeGB = [math]::Round((Get-PSDrive ($drive.TrimEnd(':')) -ErrorAction SilentlyContinue).Free / 1GB, 1)
if ($freeGB -lt 20) {
Write-Host "WARNING: $Label drive $drive has only $freeGB GB free (recommend 20 GB+)." -ForegroundColor Yellow
}
return $true
}
function Confirm-SAPassword {
param([System.Security.SecureString]$SecPwd)
$plain = [Runtime.InteropServices.Marshal]::PtrToStringAuto(
[Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecPwd))
$ok = $plain.Length -ge 8 -and
$plain -cmatch '[A-Z]' -and
$plain -cmatch '[a-z]' -and
$plain -match '\d' -and
$plain -match '[^A-Za-z0-9]'
if (-not $ok) {
Write-Host 'ERROR: SA password must be 8+ chars with uppercase, lowercase, digit, and special character.' -ForegroundColor Red
}
[Runtime.InteropServices.Marshal]::ZeroFreeBSTR(
[Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecPwd)) | Out-Null
return $ok
}
Write-DbaLog "SQL Server install log — $ts" 'Cyan'
Write-DbaLog "Log file: $logFile" 'DarkGray'
# ── Interactive prompts for anything not supplied ─────────────────────────────
if (-not $SetupPath) {
do {
$SetupPath = Read-Host 'Path to SQL Server setup.exe'
} until ($SetupPath -and (Test-Path $SetupPath))
}
if (-not (Test-Path $SetupPath)) {
Write-Host "ERROR: setup.exe not found at '$SetupPath'." -ForegroundColor Red; exit 1
}
if (-not $AnswerFile) {
do { $ok = Confirm-Directory $InstallDir 'Install directory' } until ($ok)
do { $ok = Confirm-Directory $SystemDBDir 'System DB directory' } until ($ok)
do { $ok = Confirm-Directory $SystemLogDir'System log directory' } until ($ok)
do { $ok = Confirm-Directory $UserDBDir 'User DB directory' } until ($ok)
do { $ok = Confirm-Directory $UserLogDir 'User log directory' } until ($ok)
}
if (-not $TempDBDir) { $TempDBDir = Join-Path $SystemDBDir 'TempDB' }
if (-not $TempDBLogDir) { $TempDBLogDir = Join-Path $SystemLogDir 'TempDB' }
Confirm-Directory $TempDBDir 'TempDB directory' | Out-Null
Confirm-Directory $TempDBLogDir 'TempDB log directory' | Out-Null
if (-not $SysAdminAccounts) { $SysAdminAccounts = "$env:USERDOMAIN\$env:USERNAME" }
if (-not $SAPassword) {
do {
$SAPassword = Read-Host 'SA password' -AsSecureString
} until (Confirm-SAPassword $SAPassword)
}
# ── Hardware-based recommendations ───────────────────────────────────────────
$totalRAMGB = [math]::Round((Get-CimInstance Win32_ComputerSystem).TotalPhysicalMemory / 1GB, 1)
$logicalCPUs = (Get-CimInstance Win32_ComputerSystem).NumberOfLogicalProcessors
if ($MaxMemoryGB -eq 0) { $MaxMemoryGB = [math]::Max(1, [math]::Floor($totalRAMGB - 4)) }
if ($MaxDOP -eq 0) { $MaxDOP = [math]::Min($logicalCPUs, 8) }
if ($TempDBFileCount -eq 0) { $TempDBFileCount = [math]::Min($logicalCPUs, 8) }
Write-DbaLog ''
Write-DbaLog 'Hardware-based recommendations:' 'Cyan'
Write-DbaLog " Total RAM : $totalRAMGB GB"
Write-DbaLog " Logical CPUs : $logicalCPUs"
Write-DbaLog " Max Server Mem : $MaxMemoryGB GB"
Write-DbaLog " MaxDOP : $MaxDOP"
Write-DbaLog " TempDB files : $TempDBFileCount"
# ── Confirm ───────────────────────────────────────────────────────────────────
Write-DbaLog ''
Write-DbaLog 'Install configuration:' 'Cyan'
Write-DbaLog " Setup : $SetupPath"
Write-DbaLog " Instance : $InstanceName"
Write-DbaLog " Features : $Features"
Write-DbaLog " Collation : $Collation"
Write-DbaLog " Install dir : $InstallDir"
Write-DbaLog " System DBs : $SystemDBDir"
Write-DbaLog " System logs : $SystemLogDir"
Write-DbaLog " User DBs : $UserDBDir"
Write-DbaLog " User logs : $UserLogDir"
Write-DbaLog " TempDB : $TempDBDir ($TempDBFileCount files)"
Write-DbaLog " Sysadmins : $SysAdminAccounts"
if ($AnswerFile) { Write-DbaLog " Answer file : $AnswerFile" }
Write-DbaLog ''
if (-not $WhatIf) {
$go = Read-Host 'Start installation? (yes to continue)'
if ($go -notmatch '^(yes|y|1)$') {
Write-DbaLog 'Installation cancelled.' 'Yellow'; exit 0
}
}
# ── Build setup arguments ─────────────────────────────────────────────────────
$plainPwd = [Runtime.InteropServices.Marshal]::PtrToStringAuto(
[Runtime.InteropServices.Marshal]::SecureStringToBSTR($SAPassword))
if ($AnswerFile) {
$SetupArgs = @(
"/ConfigurationFile=`"$AnswerFile`""
"/SAPWD=`"$plainPwd`""
"/SQLSYSADMINACCOUNTS=`"$SysAdminAccounts`""
'/IACCEPTSQLSERVERLICENSETERMS'
'/Q'
)
} else {
$SetupArgs = @(
'/Q'
'/ACTION=Install'
"/FEATURES=$Features"
"/INSTANCENAME=`"$InstanceName`""
"/INSTANCEDIR=`"$InstallDir`""
"/SQLCOLLATION=`"$Collation`""
"/SQLSYSADMINACCOUNTS=`"$SysAdminAccounts`""
'/SECURITYMODE=SQL'
"/SAPWD=`"$plainPwd`""
"/SQLSVCACCOUNT=`"$ServiceAccount`""
"/AGTSVCACCOUNT=`"$AgtServiceAccount`""
'/AGTSVCSTARTUPTYPE=Automatic'
'/SQLSVCSTARTUPTYPE=Automatic'
'/TCPENABLED=1'
'/NPENABLED=0'
'/BROWSERSVCSTARTUPTYPE=Disabled'
"/INSTALLSQLDATADIR=`"$SystemDBDir`""
"/SQLUSERDBDIR=`"$UserDBDir`""
"/SQLUSERDBLOGDIR=`"$UserLogDir`""
"/SQLTEMPDBDIR=`"$TempDBDir`""
"/SQLTEMPDBLOGDIR=`"$TempDBLogDir`""
"/SQLTEMPDBFILECOUNT=$TempDBFileCount"
'/SQLTEMPDBFILESIZE=8'
'/SQLTEMPDBFILEGROWTH=64'
'/SQLTEMPDBLOGFILESIZE=8'
'/SQLTEMPDBLOGFILEGROWTH=64'
'/IACCEPTSQLSERVERLICENSETERMS'
)
}
# Clear the plaintext password from memory as soon as it's in the args array
[Runtime.InteropServices.Marshal]::ZeroFreeBSTR(
[Runtime.InteropServices.Marshal]::SecureStringToBSTR($SAPassword)) | Out-Null
if ($WhatIf) {
Write-DbaLog 'WhatIf — setup.exe command:' 'Yellow'
Write-DbaLog "$SetupPath $($SetupArgs -join ' ')" 'DarkGray'
# Mask password in WhatIf output
Write-Host ($SetupArgs -join ' ') -replace '/SAPWD="[^"]*"', '/SAPWD="****"'
return
}
# ── Run setup.exe ─────────────────────────────────────────────────────────────
Write-DbaLog 'Running SQL Server setup.exe...' 'Cyan'
$proc = Start-Process -FilePath $SetupPath -ArgumentList $SetupArgs `
-Wait -PassThru -RedirectStandardOutput "$logFile.stdout" `
-RedirectStandardError "$logFile.stderr"
$exitCode = $proc.ExitCode
Write-DbaLog "setup.exe exit code: $exitCode"
switch ($exitCode) {
0 { Write-DbaLog 'Installation succeeded.' 'Green' }
3010 { Write-DbaLog 'Installation succeeded — reboot required.' 'Yellow' }
default {
Write-DbaLog "Installation failed (exit code $exitCode). Check: $logFile.stderr" 'Red'
exit $exitCode
}
}
# ── Post-install configuration ────────────────────────────────────────────────
if (-not $SkipPostConfig) {
Write-DbaLog 'Applying post-install configuration...' 'Cyan'
# Wait up to 60s for SQL to accept connections
$srv = if ($InstanceName -eq 'MSSQLSERVER') { 'localhost' } else { "localhost\$InstanceName" }
$ready = $false
for ($i = 1; $i -le 12; $i++) {
try {
$null = Invoke-Sqlcmd -ServerInstance $srv -Query 'SELECT 1' -QueryTimeout 5 `
-TrustServerCertificate -ErrorAction Stop
$ready = $true; break
} catch { Start-Sleep -Seconds 5 }
}
if (-not $ready) {
Write-DbaLog 'WARNING: SQL Server not reachable after 60s — skipping post-install config.' 'Yellow'
} else {
$postSql = @"
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure 'max server memory (MB)', $($MaxMemoryGB * 1024);
EXEC sys.sp_configure 'max degree of parallelism', $MaxDOP;
EXEC sys.sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE WITH OVERRIDE;
"@
try {
Invoke-Sqlcmd -ServerInstance $srv -Query $postSql -TrustServerCertificate -ErrorAction Stop
Write-DbaLog " Max memory : $MaxMemoryGB GB ($($MaxMemoryGB * 1024) MB)" 'Green'
Write-DbaLog " MaxDOP : $MaxDOP" 'Green'
Write-DbaLog " Cost threshold: 50" 'Green'
} catch {
Write-DbaLog "WARNING: Post-install config failed — $($_.Exception.Message)" 'Yellow'
}
}
}
Write-DbaLog ''
Write-DbaLog 'SQL Server installation complete.' 'Green'
Write-DbaLog "Log: $logFile"Not run live on this machine. -WhatIf on this script still requires an elevated shell (confirmed: it errors with “This script must be run as Administrator” even in preview mode, a real inconsistency against its sibling scripts covered below, none of which require elevation just to preview), and actually installing a second SQL Server instance onto this lab box wasn’t worth the disk space or the risk to the working environment this whole site runs from. Covered here from a full code read rather than a live run, said plainly rather than implied.
configure-sql.ps1
Applies recommended or custom sp_configure settings to an existing instance, with a before/after comparison for every setting changed. All changes use RECONFIGURE WITH OVERRIDE and are logged.
<#
.SYNOPSIS
Apply sp_configure settings to an existing SQL Server instance.
.DESCRIPTION
Applies recommended or custom SQL Server configuration settings.
Shows a before/after comparison for every setting changed.
All changes use RECONFIGURE WITH OVERRIDE and are logged.
.PARAMETER ServerInstance
Target SQL Server instance. Default: . (local default instance).
.PARAMETER MaxMemoryGB
Max server memory in GB. Auto-calculated as (TotalRAM - 4 GB) if not supplied.
.PARAMETER MaxDOP
Max degree of parallelism. Auto-calculated from logical CPU count if not supplied.
.PARAMETER CostThreshold
Cost threshold for parallelism. Default: 50.
.PARAMETER BackupCompression
Enable backup compression by default. 1=on, 0=off. Default: 1.
.PARAMETER OptimizeAdHoc
Optimize for ad hoc workloads (plan cache). 1=on, 0=off. Default: 1.
.PARAMETER RemoteAdminConnections
Enable Dedicated Admin Connection (DAC). 1=on, 0=off. Default: 1.
.PARAMETER ApplyRecommended
Apply all recommended settings using hardware auto-detection. Ignores individual params.
.EXAMPLE
# Apply all recommended settings to local instance
.\admin\installation\configure-sql.ps1 -ApplyRecommended
# Apply to remote instance with specific values
.\admin\installation\configure-sql.ps1 -ServerInstance PROD01 -MaxMemoryGB 28 -MaxDOP 4
# Review current settings without changing anything (use -WhatIf)
.\admin\installation\configure-sql.ps1 -ApplyRecommended -WhatIf
.NOTES
RiskLevel : HIGH IMPACT - runs sp_configure and RECONFIGURE against a live instance; some settings take effect immediately
#>
param(
[string]$ServerInstance = '.',
[int]$MaxMemoryGB = 0,
[int]$MaxDOP = 0,
[int]$CostThreshold = 50,
[ValidateSet(0,1)][int]$BackupCompression = 1,
[ValidateSet(0,1)][int]$OptimizeAdHoc = 1,
[ValidateSet(0,1)][int]$RemoteAdminConnections = 1,
[switch]$ApplyRecommended,
[switch]$WhatIf
)
$ErrorActionPreference = 'Stop'
if ($ServerInstance -eq '.' -and $env:DBASCRIPTS_SERVER) { $ServerInstance = $env:DBASCRIPTS_SERVER }
$repoRoot = Resolve-Path (Join-Path $PSScriptRoot '..\..')
$logDir = Join-Path $repoRoot 'output-files\installation'
New-Item -ItemType Directory -Path $logDir -Force | Out-Null
$ts = Get-Date -Format 'yyyyMMdd-HHmmss'
$logFile = Join-Path $logDir "configure-$($ServerInstance -replace '[\\/:*]','-')-$ts.log"
function Write-DbaLog {
param([string]$Msg, [string]$Color = 'White')
$line = "[$(Get-Date -Format 'HH:mm:ss')] $Msg"
Write-Host $line -ForegroundColor $Color
Add-Content -Path $logFile -Value $line
}
# ── Hardware-based recommendations ───────────────────────────────────────────
if ($ApplyRecommended -or $MaxMemoryGB -eq 0 -or $MaxDOP -eq 0) {
$totalRAMGB = [math]::Round((Get-CimInstance Win32_ComputerSystem).TotalPhysicalMemory / 1GB, 1)
$logicalCPUs = (Get-CimInstance Win32_ComputerSystem).NumberOfLogicalProcessors
if ($MaxMemoryGB -eq 0) { $MaxMemoryGB = [math]::Max(1, [math]::Floor($totalRAMGB - 4)) }
if ($MaxDOP -eq 0) { $MaxDOP = [math]::Min($logicalCPUs, 8) }
}
# Settings to apply: name → [sp_configure key, desired value, description]
$settings = [ordered]@{
'max server memory (MB)' = @{ Value = $MaxMemoryGB * 1024; Label = "Max server memory ($MaxMemoryGB GB)" }
'max degree of parallelism' = @{ Value = $MaxDOP; Label = "MaxDOP ($MaxDOP)" }
'cost threshold for parallelism'= @{ Value = $CostThreshold; Label = "Cost threshold for parallelism ($CostThreshold)" }
'backup compression default' = @{ Value = $BackupCompression; Label = "Backup compression ($(if ($BackupCompression) {'on'} else {'off'}))" }
'optimize for ad hoc workloads' = @{ Value = $OptimizeAdHoc; Label = "Optimize for ad hoc workloads ($(if ($OptimizeAdHoc) {'on'} else {'off'}))" }
'remote admin connections' = @{ Value = $RemoteAdminConnections; Label = "Remote admin connections / DAC ($(if ($RemoteAdminConnections) {'on'} else {'off'}))" }
}
Write-DbaLog "SQL Server configuration — $ServerInstance" 'Cyan'
Write-DbaLog "Log: $logFile" 'DarkGray'
# ── Read current settings ─────────────────────────────────────────────────────
$currentSql = "SELECT name, value_in_use FROM sys.configurations WHERE name IN ($( ($settings.Keys | ForEach-Object {"'$_'"}) -join ',' ))"
try {
$current = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $currentSql `
-TrustServerCertificate -ErrorAction Stop
} catch {
Write-DbaLog "ERROR: Cannot connect to $ServerInstance — $($_.Exception.Message)" 'Red'; exit 1
}
$currentMap = @{}
foreach ($row in $current) { $currentMap[$row.name] = $row.value_in_use }
# ── Show planned changes ──────────────────────────────────────────────────────
Write-DbaLog ''
Write-DbaLog 'Planned configuration changes:' 'Cyan'
Write-DbaLog ("{0,-42} {1,12} → {2}" -f 'Setting', 'Current', 'New') 'DarkGray'
Write-DbaLog ([string]::new('-', 72)) 'DarkGray'
$changed = @{}
foreach ($key in $settings.Keys) {
$desired = $settings[$key].Value
$current = if ($currentMap.ContainsKey($key)) { $currentMap[$key] } else { '?' }
$isSame = "$current" -eq "$desired"
$color = if ($isSame) {'DarkGray'} else {'White'}
$note = if ($isSame) {'(no change)'} else {''}
Write-DbaLog ("{0,-42} {1,12} → {2} {3}" -f $key, $current, $desired, $note) $color
if (-not $isSame) { $changed[$key] = $settings[$key].Value }
}
if ($changed.Count -eq 0) {
Write-DbaLog ''
Write-DbaLog 'All settings already at desired values. Nothing to apply.' 'Green'
return
}
if ($WhatIf) {
Write-DbaLog ''
Write-DbaLog "WhatIf: $($changed.Count) setting(s) would be applied." 'Yellow'
return
}
# ── Apply ─────────────────────────────────────────────────────────────────────
Write-DbaLog ''
Write-DbaLog "Applying $($changed.Count) setting(s)..." 'Cyan'
$sql = "EXEC sys.sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;`n"
foreach ($key in $changed.Keys) {
$sql += "EXEC sys.sp_configure '$key', $($changed[$key]);`n"
}
$sql += "RECONFIGURE WITH OVERRIDE;"
try {
Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $sql `
-TrustServerCertificate -QueryTimeout 30 -ErrorAction Stop
Write-DbaLog 'Settings applied successfully.' 'Green'
} catch {
Write-DbaLog "ERROR applying settings: $($_.Exception.Message)" 'Red'; exit 1
}
# ── Verify ────────────────────────────────────────────────────────────────────
$verify = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $currentSql `
-TrustServerCertificate -ErrorAction SilentlyContinue
$verifyMap = @{}
foreach ($row in $verify) { $verifyMap[$row.name] = $row.value_in_use }
Write-DbaLog ''
Write-DbaLog 'Verification:' 'Cyan'
foreach ($key in $changed.Keys) {
$expected = $changed[$key]
$actual = $verifyMap[$key]
$ok = "$actual" -eq "$expected"
$color = if ($ok) {'Green'} else {'Red'}
Write-DbaLog (" {0,-42} {1} {2}" -f $key, $actual, $(if ($ok) {'OK'} else {"MISMATCH (expected $expected)"})) $color
}
Write-DbaLog ''
Write-DbaLog 'Configuration complete.' 'Green'Real Output — WhatIf Against This Lab Instance
Unlike install-sql.ps1, this one previews without needing elevation:
[11:09:38] SQL Server configuration — .
[11:09:38] Log: output-files\installation\configure-.-20260730-110938.log
[11:09:39] Planned configuration changes:
[11:09:39] Setting Current → New
[11:09:39] ------------------------------------------------------------------------
[11:09:39] max server memory (MB) 1600 → 3072
[11:09:39] max degree of parallelism 8 → 8 (no change)
[11:09:39] cost threshold for parallelism 5 → 50
[11:09:39] backup compression default 0 → 1
[11:09:39] optimize for ad hoc workloads 0 → 1
[11:09:39] remote admin connections 0 → 1
[11:09:39] WhatIf: 5 setting(s) would be applied.
Every one of these lines up exactly with the WARN findings from post-install-validation.ps1 run against the same instance minutes earlier, cost threshold, backup compression, and optimize for ad hoc workloads were all flagged there and all show up here as planned fixes. The two scripts genuinely close the loop on each other.
How To Run From The Repo
git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1
.\powershell\installation\install-sql.ps1 -SetupPath D:\SQL2022\setup.exe -WhatIf
.\powershell\installation\configure-sql.ps1 -WhatIf
These scripts live in the repo at:
Understanding the Results
- A double-BOM file will fail with a confusing, unrelated-looking parser error pointing at plain English text inside a comment block, not at anything that looks like the real problem. Checking the first few bytes of the file is the fast way to confirm it.
configure-sql.ps1‘s “(no change)” rows are informative, not noise. Seeing a setting already at the recommended value is useful confirmation, not just something to filter out.install-sql.ps1requiring elevation even for-WhatIfis a real gap, not intentional caution, every sibling script in this pillar previews without it.
Best Practices
- Verify a script parses at all (
pwsh -File script.ps1 -WhatIfor just opening it) before assuming a strange PowerShell parser error is your own typo, a corrupted BOM produces exactly this kind of misleading error. - Always run
configure-sql.ps1with-WhatIffirst and read the before/after table before applying anything, especiallymax server memory, a wrong value here affects every workload on the instance immediately. - Pair
configure-sql.ps1with post-install-validation.ps1, run validation, feed its WARN findings into the configure script, re-run validation to confirm. - Never log or persist the SA password;
install-sql.ps1already handles this correctly, don’t work around it.
Related Scripts
You may also find these scripts useful:
- SQL Server Installation and Patching (hub)
- Pre-Install and Post-Install Checks
- Uninstall SQL Server
- Get Instance Configuration Snapshot
- DBA Scripts: The Complete Guide, the map across every script on this site
Frequently Asked Questions
My script fails with a parser error that points at plain English text, what’s wrong?
Check the first bytes of the file for a duplicated UTF-8 BOM (ef bb bf appearing twice before the actual content starts). A second, unstripped BOM character sitting in front of a <# ... #> comment block can break PowerShell’s tokenizer badly enough to misreport the actual line at fault.
Why does install-sql.ps1 need Administrator even to preview with -WhatIf?
It’s a genuine inconsistency against the rest of this pillar’s scripts, all of which preview without elevation. Worth flagging if you’re scripting a fully unattended preview pipeline, since this one script in the set will still stop you.
Summary
Two scripts, install then configure, both logged and both safe to preview before committing. Testing them surfaced a real, repo-wide bug, a corrupted double BOM breaking every script in the whole powershell/installation/ folder, now fixed and confirmed working. configure-sql.ps1‘s real output against this lab instance closed the loop with the previous post’s validation findings exactly as intended: the WARNs on one side became the planned fixes on the other.
Leave a Reply