Skip to content
SysAndDB
Go back

Automating SQL Server Patching with PowerShell: A Safe-Stop Blueprint for Standalone & Clustered Estates

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 SYNCHRONIZED before any primary role failover occurs.
  • Audit-Ready Evidence Trails: Every run captures pre-patch builds, SHA-256 package hashes, Setup Summary.txt results, and post-patch ProductVersion outputs 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:

  1. 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.
  2. Baseline the Target Instance: Query ProductVersion from 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.
  3. Run Setup Explicitly & Wait: Invoke setup.exe with an explicit /InstanceName= switch (avoiding blind /AllInstances sweeps on shared hosts), wait synchronously for exit (-Wait), and capture the process exit code.
  4. Parse the Setup Bootstrap Summary: Locate the latest Summary.txt under Setup Bootstrap\Log\ and verify that every targeted feature—not just the first matching word—reported Passed.
  5. Verify the Running Engine: Reconnect after any required OS reboot and confirm that SERVERPROPERTY('ProductVersion') and ProductUpdateLevel match the target CU, all user databases are ONLINE, 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.

  1. Patch Passive Nodes First: Running Setup on passive nodes updates their local binaries with zero impact on active database connections.
  2. Reboot Passive Nodes: Restart each patched passive node and confirm its WSFC status returns to Up.
  3. Move the Cluster Role: Execute Move-ClusterGroup to fail over the SQL Server role to a patched passive node (typically a 10–25 second storage and service cutover).
  4. 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 DimensionAlways On Availability Group (AG)Failover Cluster Instance (FCI)
Rollout OrderSecondary replicas -> planned failover -> former primaryPassive WSFC nodes -> Move-ClusterGroup -> former active node
Expected Application Impact1–3 seconds (listener reconnect)10–25 seconds (shared storage dismount + SQL service startup)
Mandatory Pre-Cutover GateSynchronous replicas in SYNCHRONIZED stateAll passive nodes rebooted and Up in Failover Cluster Manager
Rollback ScopeIndividual replica instance (setup.exe /Action=RemovePatch)Node-by-node instance binary rollback


Previous Post
Automated Patching for SQL Server on Azure VMs: Setup, Always On Guardrails & Azure Update Manager Migration