Patching & Upgrades

Status: Reviewed (deep-dive v1)
Stack: PowerShell (build/version + Windows patch context) → SQL target matrix/procs → Blazor Server UI
Depends on: SQL-01 Inventory (instance population); coordinate windows with SQL-04 Agent / SQL-03 maint
Goal: Know every instance’s build vs target, plan CU/GDR/upgrade windows, and see who’s overdue — without turning this into a full upgrade project plan for every host.

Why this next

Unpatched SQL is risk (security + known bugs). Blind patching without inventory, backups, and a window is also risk. This topic is currency + cadence, not step-by-step installer screenshots for every release.

What “good” looks like

Each instance: ProductVersion, ProductLevel, Edition, build string, last patched date (best-effort)
Target matrix: agreed CU/GDR (or “N-1”) per version family (2016/2019/2022/…)
Blazor: out-of-date list + days behind target
Pre-flight checklist tied to backup (SQL-02) and change control
Cluster/AG notes: patch order awareness (overview only until HA topic deepens)

Build vs target (starting model — confirm with Rick)

Field	Source
Current build	`SERVERPROPERTY('ProductVersion')`, `ProductLevel`, `ProductUpdateLevel` / registry / inventory collect
Target build	`dbo.SqlBuildTarget` (major.minor.build → target label)
Policy	e.g. “prod within 1 CU of current recommended” / “GDR within 30 days”

Do not invent a mandatory target CU number in this doc — Rick sets the matrix; the system stores and flags drift.

Checklist — collect (PowerShell + T-SQL)

[ ] From inventory collect (or dedicated): version, level, edition, engine edition, install path, clustered?, AG present?
[ ] Optional PS: Windows Update / last boot history for host context (not a substitute for SQL CU)
[ ] Optional: SQL Server setup bootstrapper / file version of sqlservr.exe as cross-check
[ ] Emit instance key + CollectedAt

Read-only. Collectors never launch setup.

Checklist — store (SQL + stored procedures)

Table	Purpose
`dbo.SqlInstanceBuild`	Latest build facts (can live on SqlInstance)
`dbo.SqlBuildTarget`	Version family → target build / label / severity if behind
`dbo.SqlPatchHistory`	Recorded patch events (who/when/from→to/ticket)
`dbo.SqlPatchWindow`	Planned windows per instance/group

Procs:

[ ] usp_SqlBuild_ApplySnapshot
[ ] usp_SqlBuild_GetOutOfDate — Blazor overdue list
[ ] usp_SqlBuild_GetInstanceDetail
[ ] usp_SqlPatchHistory_Add — post-change logging

Flag rule (example): current build < target → Yellow; < target and older than policy days → Red; unknown target → Gray.

Checklist — present (Blazor Server)

[ ] Out-of-date instances (version, current, target, days/severity)
[ ] Filters: version family, prod/nonprod (if tagged), Red only
[ ] Detail: patch history + next planned window
[ ] Actions v1: link this runbook + “request window” note — no remote patch from UI

Pre-patch / upgrade checklist (operator)

Confirm instance on inventory + backup coverage Green (SQL-02) for user DBs
Note edition features (Enterprise vs Standard) and AG/cluster topology
Read CU/GDR known issues for this build (official KB) — don’t skip
Maintenance window + rollback plan (prior build media / VM snapshot if infra provides)
Stop or quiesce heavy jobs (SQL-04); defer index execute (SQL-03)
Apply in agreed order (standalone vs AG: typically secondaries first / follow current Microsoft guidance for your topology — confirm per env)
Post-checks: service up, version string updated, Agent running, smoke query, backup job success
Log SqlPatchHistory + clear overdue on next collect

Upgrade vs CU (keep distinct)

Change	Typical risk	Notes
GDR / security	Lower than major	Often faster path; still needs window
CU	Medium	Cumulative fixes; test nonprod first when possible
Major version upgrade	High	Compatibility level, deprecated features, duration — separate project checklist later
Edition upgrade	Medium/High	Licensing + feature enablement

This deep-dive prioritizes CU/GDR currency. Major upgrades get a future dedicated note if Rick wants it.

Common failure patterns

Symptom	Likely cause
Setup fails mid-way	Permissions; AV locks; disk space (SQL-06); pending reboot
Version unchanged after “success”	Patched wrong instance; named instance confusion
App breaks after CU	Plan/regression; compat; known issue — rollback decision
AG unhealthy post-patch	Wrong order; redo lag; need resume/sync time

Permissions notes

Collect: local/admin or equivalent for file/version; SQL view for properties
Patch install: admin + change control — not Blazor app pool
Blazor: read procs only

Done definition for this topic

[ ] Build facts on inventoried instances
[ ] At least one row in target matrix (even draft)
[ ] Out-of-date board works
[ ] List Notes link this doc

Next candidates (when Rick directs)

HA/DR awareness · Alerting & runbooks · (major upgrade playbook if needed)

SQL Dude — SQL-08 Patching & Upgrades v1