Build Out-of-Date List

Status: Reviewed (spike v1)
Path: Last optional spike #8 (completes spike set 01–08 + approved 1→2→3→7)
Stack: PowerShell (optional host context) → SQL target matrix/procs → Blazor Server
Builds on: SQL-08 Patching & upgrades; requires build fields on dbo.SqlInstance from SQL-SPIKE-01 (extend if thin)
Goal: Compare each instance’s build to a Rick-maintained target matrix; Blazor list of out-of-date instances — no remote patch from UI.

In scope (MVP)

• Ensure SqlInstance has ProductVersion / ProductLevel / Edition (already in SPIKE-01)
• SqlBuildTarget matrix: version family → target build string + label
• Evaluate overdue vs target; severity by how far / policy days
• Blazor out-of-date board + simple target admin read (edit can be SQL seed for MVP)
• Optional: SqlPatchHistory insert stub for manual logging after a patch window
• Reuse CollectRun only if re-collecting builds; else evaluate from existing inventory

Out of scope

• Running setup/CU installers
• Full AG patch-order automation (point at SQL-09)
• Scraping Microsoft CU pages automatically
• Spike #7 wiring required (note BuildOutOfDate hook)

A. Schema extensions

-- If not already present on SqlInstance from SPIKE-01, add:
-- ProductVersion, ProductLevel, Edition already defined in SPIKE-01.
-- Optional extras:
ALTER TABLE dbo.SqlInstance ADD
  ProductUpdateLevel NVARCHAR(32) NULL,
  ProductUpdateReference NVARCHAR(64) NULL,
  BuildCapturedAt DATETIME2(0) NULL;
-- (skip ALTER if columns exist — document in deploy script)

CREATE TABLE dbo.SqlBuildTarget (
  BuildTargetId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  VersionFamily NVARCHAR(32) NOT NULL,     -- e.g. '2019','2022','2016'
  TargetProductVersion NVARCHAR(64) NOT NULL, -- e.g. '15.0.4430.1'
  TargetLabel NVARCHAR(128) NOT NULL,        -- e.g. 'CU28' / 'GDR'
  MinSeverityIfBehind VARCHAR(16) NOT NULL CONSTRAINT DF_BuildTarget_Sev DEFAULT ('High'),
  PolicyDaysYellow INT NOT NULL CONSTRAINT DF_BuildTarget_Y DEFAULT (30),
  PolicyDaysRed INT NOT NULL CONSTRAINT DF_BuildTarget_R DEFAULT (90),
  IsActive BIT NOT NULL CONSTRAINT DF_BuildTarget_Active DEFAULT (1),
  Notes NVARCHAR(256) NULL,
  CONSTRAINT UQ_SqlBuildTarget_Family UNIQUE (VersionFamily)
);

CREATE TABLE dbo.SqlInstanceBuildStatus (
  SqlInstanceId INT NOT NULL PRIMARY KEY REFERENCES dbo.SqlInstance(SqlInstanceId),
  VersionFamily NVARCHAR(32) NULL,
  CurrentProductVersion NVARCHAR(64) NULL,
  TargetProductVersion NVARCHAR(64) NULL,
  TargetLabel NVARCHAR(128) NULL,
  IsOutOfDate BIT NOT NULL CONSTRAINT DF_BuildStatus_OOD DEFAULT (0),
  RagStatus CHAR(1) NOT NULL CONSTRAINT DF_BuildStatus_RAG DEFAULT ('G'), -- G/Y/R/U (unknown target)
  ComparedAt DATETIME2(0) NOT NULL
);

CREATE TABLE dbo.SqlPatchHistory (
  PatchHistoryId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  FromVersion NVARCHAR(64) NULL,
  ToVersion NVARCHAR(64) NULL,
  PatchLabel NVARCHAR(128) NULL,
  PerformedAt DATETIME2(0) NOT NULL,
  PerformedBy NVARCHAR(128) NULL,
  Ticket NVARCHAR(64) NULL,
  Notes NVARCHAR(512) NULL
);

Version family parse (MVP): major of ProductVersion → map
- 16.x → 2022
- 15.x → 2019
- 14.x → 2017
- 13.x → 2016
(extend as needed)

Compare rule: parse ProductVersion as dotted ints; current < target → out of date.
If no target for family → RagStatus = U (unknown).
If out of date: default Y; if BuildCapturedAt/LastSeenAt older than PolicyDaysRed since first detected — keep simple MVP: use policy days only if you store FirstOutOfDateAt; else severity = MinSeverityIfBehind mapped to R for Critical/High families Rick marks.

Simpler MVP RAG: Out of date → Y; out of date AND MinSeverityIfBehind = Critical → R; unknown target → U; else G.

Procs:

CREATE PROC dbo.usp_SqlBuild_ApplyFromInventory AS
BEGIN
  -- For each active SqlInstance: derive VersionFamily, lookup active SqlBuildTarget,
  -- set SqlInstanceBuildStatus IsOutOfDate/RagStatus/ComparedAt
END;

CREATE PROC dbo.usp_SqlBuild_GetOutOfDate
  @IncludeUnknown BIT = 0 AS
BEGIN
  SELECT i.HostName, i.InstanceName, i.Edition, i.ProductVersion, i.ProductLevel,
         s.VersionFamily, s.TargetProductVersion, s.TargetLabel, s.RagStatus, s.ComparedAt,
         i.SqlInstanceId
  FROM dbo.SqlInstanceBuildStatus s
  JOIN dbo.SqlInstance i ON i.SqlInstanceId = s.SqlInstanceId
  WHERE i.IsActive = 1
    AND (s.IsOutOfDate = 1 OR (@IncludeUnknown = 1 AND s.RagStatus = 'U'))
  ORDER BY CASE s.RagStatus WHEN 'R' THEN 0 WHEN 'Y' THEN 1 WHEN 'U' THEN 2 ELSE 3 END,
           i.HostName, i.InstanceName;
END;

CREATE PROC dbo.usp_SqlBuild_GetTargets AS
  SELECT * FROM dbo.SqlBuildTarget WHERE IsActive = 1 ORDER BY VersionFamily;

CREATE PROC dbo.usp_SqlPatchHistory_Add
  @SqlInstanceId INT, @FromVersion NVARCHAR(64) = NULL, @ToVersion NVARCHAR(64) = NULL,
  @PatchLabel NVARCHAR(128) = NULL, @PerformedBy NVARCHAR(128) = NULL,
  @Ticket NVARCHAR(64) = NULL, @Notes NVARCHAR(512) = NULL AS ...

After inventory collect (SPIKE-01), schedule usp_SqlBuild_ApplyFromInventory.

B. PowerShell (optional)

Usually no new collector if SPIKE-01 already stores ProductVersion. Optional enrichment:

# SERVERPROPERTY('ProductUpdateLevel'), ProductUpdateReference

Script name if used: Collect-SqlBuildFacts.ps1 (thin) then call apply proc.

C. Blazor Server (MVP UI)

Page: /builds or /sql/builds

Grid: Host | Instance | Edition | Current version | Level | Target | Label | RAG

Filters: Out-of-date only (default on); Version family; include unknown targets

Secondary: read-only Targets table from usp_SqlBuild_GetTargets (editing via DBA seed is fine for MVP)

Actions v1: link SQL-08 pre-patch checklist; optional form → usp_SqlPatchHistory_Add
No invoke setup / CU download

App pool: read procs (+ optional history add)

D. Acceptance checks

• [ ] Seed targets for at least one family you run (e.g. 2019/2022) — Rick supplies real CU builds
• [ ] Instance below target → appears out of date with Y/R
• [ ] Instance at/above target → not on default list
• [ ] Family without target → U when IncludeUnknown on
• [ ] Re-run apply after inventory version change updates status
• [ ] Patch history add inserts a row without changing build columns (build changes on next inventory)

E. Repo layout

/sql/build/001_tables.sql
/sql/build/002_procs.sql
/sql/build/003_seed_targets.sql   -- placeholders; Rick fills real builds
/ps/Build/Collect-SqlBuildFacts.ps1  -- optional
/src/.../Pages/Builds/Index.razor
/src/.../Services/BuildCurrencyService.cs

Hook for spike #7 (later)

BuildOutOfDate where RagStatus IN (Y,R) → alert; Critical if R.

Spike set status

Beyond this set: wait for Rick (implementation in repo, estate targets, notify channels, etc.).

SQL Dude — SQL-SPIKE-08 Build Out-of-Date v1