Patching twenty production SQL Server instances by hand at 2:00 AM is how configuration drift and accidental failovers happen. However, automating a flawed process only spreads mistakes faster.
Effective SQL Server patch automation is not about building the fastest installer—it is about enforcing a tested, repeatable sequence that knows exactly when to stop. A reliable script validates the binary hash, records the baseline build, patches an explicit target instance, parses the Setup Bootstrap summary, proves the running engine version, and halts immediately on the first unexpected exit code.
Why Automate SQL Server Patching?
When executed with strict pre-flight and post-flight gates, PowerShell patch automation delivers measurable operational benefits across enterprise database estates:
- Deterministic Execution Across Environments: Every dev, QA, and production node receives the exact same validated Cumulative Update (CU) binary, eliminating mixed-build drift between replicas.
- Separation of Approval and Orchestration: A DBA still selects the application-certified CU and approves each rollout wave, while PowerShell enforces every prerequisite check without midnight fatigue.
- Controlled Cluster Sequencing: Automation guarantees that passive nodes and secondary replicas are patched, rebooted, and verified as
SYNCHRONIZEDbefore any primary role failover occurs. - Audit-Ready Evidence Trails: Every run captures pre-patch builds, SHA-256 package hashes, Setup
Summary.txtresults, and post-patchProductVersionoutputs in timestamped logs. - Fail-Safe Containment: Unlike a human operator who might push through a warning under time pressure, a well-written script halts on the first anomaly and leaves downstream servers untouched.
The Five-Gate Patching Pipeline
Before writing cluster loops, structure every host-level patch run around five sequential checkpoints—with a hard stop if any gate fails:
- Stage & Verify the Package: Confirm the approved CU executable exists in a controlled local or internal SMB repository, verify its Authenticode digital signature, and validate its SHA-256 hash against your change record.
- Baseline the Target Instance: Query
ProductVersionfrom the live SQL Server instance to ensure it matches the exact starting build tested in your lab, and verify free disk space, pending reboot flags, and active backup/maintenance jobs. - Run Setup Explicitly & Wait: Invoke
setup.exewith an explicit/InstanceName=switch (avoiding blind/AllInstancessweeps on shared hosts), wait synchronously for exit (-Wait), and capture the process exit code. - Parse the Setup Bootstrap Summary: Locate the latest
Summary.txtunderSetup Bootstrap\Log\and verify that every targeted feature—not just the first matching word—reportedPassed. - Verify the Running Engine: Reconnect after any required OS reboot and confirm that
SERVERPROPERTY('ProductVersion')andProductUpdateLevelmatch the target CU, all user databases areONLINE, and SQL Server Agent is running.
Step 1: Defensive Standalone Patch Script (SHA-256 & Setup Verification)
Never allow production servers to download patches from the internet during a maintenance window, and never assume exit code 0 alone means every component succeeded.
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[string]$SqlInstance, # e.g., "SQLPROD01" or "SQLPROD01\INST2"
[Parameter(Mandatory = $true)]
[string]$PackagePath, # e.g., "C:\SqlPatch\SQLServer2022-KB5042211-x64.exe"
[Parameter(Mandatory = $true)]
[string]$ExpectedSha256, # Approved SHA256 from change ticket
[Parameter(Mandatory = $true)]
[string]$ExpectedStartBuild, # e.g., "16.0.4135.4"
[Parameter(Mandatory = $true)]
[string]$TargetBuild # e.g., "16.0.4140.1"
)
$ErrorActionPreference = "Stop"
# Determine named instance vs default instance
$InstanceName = if ($SqlInstance -match "\\") { $SqlInstance.Split("\")[1] } else { "MSSQLSERVER" }
# --- GATE 1: Verify Package Existence, Signature & SHA-256 Hash ---
if (-not (Test-Path -LiteralPath $PackagePath)) {
throw "STOP: Approved CU package missing at '$PackagePath'."
}
$Signature = Get-AuthenticodeSignature -LiteralPath $PackagePath
if ($Signature.Status -ne "Valid") {
throw "STOP: Digital signature verification failed ($($Signature.Status))."
}
$ActualHash = (Get-FileHash -LiteralPath $PackagePath -Algorithm SHA256).Hash
if ($ActualHash -ne $ExpectedSha256.ToUpper()) {
throw "STOP: SHA-256 mismatch. Expected $ExpectedSha256, got $ActualHash."
}
# --- GATE 2: Baseline Running Build Before Setup ---
$PreQuery = "SELECT CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(50)) AS Build;"
$CurrentBuild = (Invoke-Sqlcmd -ServerInstance $SqlInstance -Query $PreQuery -TrustServerCertificate).Build
if ($CurrentBuild -ne $ExpectedStartBuild) {
throw "STOP: Instance '$SqlInstance' is at build $CurrentBuild (expected $ExpectedStartBuild)."
}
# --- GATE 3: Run Setup Explicitly and Wait ---
$Arguments = @(
"/qs",
"/Action=Patch",
"/InstanceName=$InstanceName",
"/IAcceptSQLServerLicenseTerms",
"/MediaPath=`"C:\Temp\SqlPatchExtract`""
)
$Process = Start-Process -FilePath $PackagePath -ArgumentList $Arguments -Wait -PassThru
# Exit code 0 = success; 3010 = success, reboot required
if ($Process.ExitCode -notin @(0, 3010)) {
throw "STOP: SQL Setup exited with code $($Process.ExitCode). Inspect Setup Bootstrap\Log."
}
# --- GATE 4: Parse Latest Setup Bootstrap Summary.txt ---
$BootstrapRoot = "C:\Program Files\Microsoft SQL Server"
$LatestSummary = Get-ChildItem -Path $BootstrapRoot -Filter "Summary.txt" -Recurse -ErrorAction SilentlyContinue |
Sort-Object LastWriteTime -Descending |
Select-Object -First 1
if (-not $LatestSummary) {
throw "STOP: Could not locate Setup Bootstrap Summary.txt for verification."
}
$SummaryContent = Get-Content -LiteralPath $LatestSummary.FullName -Raw
if ($SummaryContent -match "Final result:\s+(?!Passed)") {
throw "STOP: Setup Summary.txt did not report 'Final result: Passed'. Check $($LatestSummary.FullName)."
}
if ($Process.ExitCode -eq 3010) {
Write-Warning "Setup succeeded with exit code 3010 (Reboot Required). Reboot host before running Gate 5 verification."
return
}
# --- GATE 5: Verify Running Engine Build ---
$PostBuild = (Invoke-Sqlcmd -ServerInstance $SqlInstance -Query $PreQuery -TrustServerCertificate).Build
if ($PostBuild -ne $TargetBuild) {
throw "STOP: Post-patch build is $PostBuild, expected $TargetBuild."
}
Write-Host "SUCCESS: '$SqlInstance' verified at target build $PostBuild." -ForegroundColor Green
Operational Guardrail: Test deliberate failures in your lab before scheduling production waves—pass a wrong SHA-256 hash, an incorrect instance name, and a mismatched starting build to confirm the script halts cleanly every single time.
Step 2: Rolling Patch Automation for Always On Availability Groups
Patching an Always On Availability Group (AG) via an alphabetical host list is a recipe for an outage. An AG-aware orchestrator must query the listener dynamically, patch secondary replicas first, wait for full synchronization health, fail over cleanly to a synchronous secondary, and patch the former primary last.
Rolling AG Patch Workflow
param(
[Parameter(Mandatory = $true)]
[string]$AgListener,
[Parameter(Mandatory = $true)]
[string]$PatchRepo,
[Parameter(Mandatory = $true)]
[string]$TargetBuild
)
$ErrorActionPreference = "Stop"
# 1. Query AG topology from the Listener (never hardcode primary/secondary roles)
$Ag = Get-DbaAvailabilityGroup -SqlInstance $AgListener
$PrimaryNode = $Ag.PrimaryReplica
$SecondaryNodes = $Ag.AvailabilityReplicas.Name | Where-Object { $_ -ne $PrimaryNode }
# 2. Pre-flight check: Stop if any AG database is unhealthy before touching binaries
$Unhealthy = Get-DbaAgDatabase -SqlInstance $PrimaryNode -AvailabilityGroup $Ag.Name |
Where-Object { $_.SynchronizationState -ne "Synchronized" -and $_.AvailabilityMode -eq "SynchronousCommit" }
if ($Unhealthy) {
throw "STOP: Synchronous AG databases are not in SYNCHRONIZED state. Aborting rollout."
}
# 3. Patch and reboot each Secondary Replica sequentially
foreach ($Node in $SecondaryNodes) {
Write-Host "Patching secondary replica: $Node..." -ForegroundColor Cyan
Update-DbaInstance -ComputerName $Node -Path $PatchRepo -Restart -ExtractPath "C:\Temp\SqlPatchExtract" -Confirm:$false
# Hold rollout until replica rejoins and catches up
do {
Start-Sleep -Seconds 15
$States = (Get-DbaAgDatabase -SqlInstance $PrimaryNode -AvailabilityGroup $Ag.Name |
Where-Object { $_.ReplicaName -eq $Node }).SynchronizationState
} while ($States -contains "NotSynchronizing")
}
# 4. Select a healthy SynchronousCommit secondary for zero-data-loss failover
$FailoverTarget = ($Ag.AvailabilityReplicas | Where-Object {
$_.Name -ne $PrimaryNode -and $_.AvailabilityMode -eq "SynchronousCommit"
} | Select-Object -First 1).Name
if (-not $FailoverTarget) {
throw "STOP: No SynchronousCommit secondary available for planned failover."
}
Write-Host "Executing planned failover to $FailoverTarget..." -ForegroundColor Yellow
Invoke-DbaAgFailover -SqlInstance $FailoverTarget -AvailabilityGroup $Ag.Name -Confirm:$false
Start-Sleep -Seconds 20
# 5. Patch and reboot the former Primary Replica
Write-Host "Patching former primary replica: $PrimaryNode..." -ForegroundColor Cyan
Update-DbaInstance -ComputerName $PrimaryNode -Path $PatchRepo -Restart -ExtractPath "C:\Temp\SqlPatchExtract" -Confirm:$false
# 6. Final verification across all replicas
Get-DbaBuild -SqlInstance $Ag.AvailabilityReplicas.Name |
Select-Object SqlInstance, Build, CULevel
Step 3: Rolling Patch Automation for Failover Cluster Instances (FCI)
In a Windows Server Failover Cluster (WSFC) running a shared-storage Failover Cluster Instance (FCI), SQL Server runs on only one active node at a time, while passive nodes hold idle local binaries.
- Patch Passive Nodes First: Running Setup on passive nodes updates their local binaries with zero impact on active database connections.
- Reboot Passive Nodes: Restart each patched passive node and confirm its WSFC status returns to
Up. - Move the Cluster Role: Execute
Move-ClusterGroupto fail over the SQL Server role to a patched passive node (typically a 10–25 second storage and service cutover). - Patch & Reboot the Former Active Node: Update binaries on the original owner node and reboot it.
param(
[Parameter(Mandatory = $true)]
[string]$ClusterName,
[Parameter(Mandatory = $true)]
[string]$SqlClusterGroup, # e.g., "SQL Server (MSSQLSERVER)"
[Parameter(Mandatory = $true)]
[string]$PackagePath
)
Import-Module FailoverClusters
$ErrorActionPreference = "Stop"
$Group = Get-ClusterGroup -Cluster $ClusterName -Name $SqlClusterGroup
$ActiveNode = $Group.OwnerNode.Name
$PassiveNodes = (Get-ClusterNode -Cluster $ClusterName | Where-Object { $_.State -eq "Up" }).Name |
Where-Object { $_ -ne $ActiveNode }
if (-not $PassiveNodes) {
throw "STOP: No healthy passive WSFC nodes available for rolling FCI patch."
}
# 1. Patch and reboot passive nodes
foreach ($Node in $PassiveNodes) {
Invoke-Command -ComputerName $Node -ScriptBlock {
param($Exe)
$Proc = Start-Process -FilePath $Exe -ArgumentList "/qs /Action=Patch /AllInstances /IAcceptSQLServerLicenseTerms /MediaPath=`"C:\Temp\SqlPatchExtract`"" -Wait -PassThru
if ($Proc.ExitCode -notin @(0, 3010)) {
throw "STOP: Passive node patch failed on $env:COMPUTERNAME (ExitCode: $($Proc.ExitCode))"
}
} -ArgumentList $PackagePath
Restart-Computer -ComputerName $Node -Wait -For PowerShell -Timeout 600
}
# 2. Fail over SQL Server role to the first patched passive node
$NewOwner = $PassiveNodes[0]
Move-ClusterGroup -Cluster $ClusterName -Name $SqlClusterGroup -Node $NewOwner | Out-Null
do {
Start-Sleep -Seconds 5
$State = (Get-ClusterGroup -Cluster $ClusterName -Name $SqlClusterGroup).State
} while ($State -ne "Online")
# 3. Patch and reboot the former active node
Invoke-Command -ComputerName $ActiveNode -ScriptBlock {
param($Exe)
$Proc = Start-Process -FilePath $Exe -ArgumentList "/qs /Action=Patch /AllInstances /IAcceptSQLServerLicenseTerms /MediaPath=`"C:\Temp\SqlPatchExtract`"" -Wait -PassThru
if ($Proc.ExitCode -notin @(0, 3010)) {
throw "STOP: Former active node patch failed on $env:COMPUTERNAME (ExitCode: $($Proc.ExitCode))"
}
} -ArgumentList $PackagePath
Restart-Computer -ComputerName $ActiveNode -Wait -For PowerShell -Timeout 600
Post-Patch Verification Query
Run this validation query across every patched standalone instance, AG listener, or FCI virtual network name before signing off on the change ticket:
SELECT
@@SERVERNAME AS ConnectedInstance,
SERVERPROPERTY('MachineName') AS ActiveHost,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductUpdateLevel') AS CumulativeUpdate,
SERVERPROPERTY('IsClustered') AS IsClustered,
SERVERPROPERTY('IsHadrEnabled') AS IsAlwaysOnEnabled;
-- Confirm zero user databases are stuck in non-ONLINE states
SELECT name, state_desc, user_access_desc
FROM sys.databases
WHERE state_desc <> 'ONLINE';
AG vs. FCI Rolling Patch Summary
| Architecture Dimension | Always On Availability Group (AG) | Failover Cluster Instance (FCI) |
|---|---|---|
| Rollout Order | Secondary replicas -> planned failover -> former primary | Passive WSFC nodes -> Move-ClusterGroup -> former active node |
| Expected Application Impact | 1–3 seconds (listener reconnect) | 10–25 seconds (shared storage dismount + SQL service startup) |
| Mandatory Pre-Cutover Gate | Synchronous replicas in SYNCHRONIZED state | All passive nodes rebooted and Up in Failover Cluster Manager |
| Rollback Scope | Individual replica instance (setup.exe /Action=RemovePatch) | Node-by-node instance binary rollback |