Inventory & Configuration Baseline

Status: Reviewed (deep-dive v1)
Stack: PowerShell collect → SQL store/procs → Blazor Server UI
Goal: Know every SQL instance you manage, what it looks like today, and when it drifts.

Why this first

Backup, index, security, and capacity work all assume a trustworthy inventory. Without it you maintain ghosts and miss real servers.

What “good” looks like

Every instance has a row: host, instance name, version/edition/build, online/offline
Key config captured: max server memory, min memory, max DOP, cost threshold, fill factor (if set), collation, authentication mode
tempdb: file count, initial size, autogrowth, equal-size check
Agent: running?, operators, mail profile present?
Linked servers / credentials listed (names only in UI; secrets never)
Last successful collect timestamp; drift flagged vs previous snapshot

Checklist — collect (PowerShell)

Run against AD-discovered Windows servers (or a known host list). Prefer SMO / SqlServer module where available; fall back to registry + WMI.

[ ] Discover computers in scope (AD OU / group / CSV allowlist)
[ ] Detect SQL services (MSSQLSERVER, MSSQL$*, SQLAgent*) and startup state
[ ] Resolve instance → port / pipe; note cluster/AG listener names if present
[ ] Capture: ProductVersion, ProductLevel, Edition, EngineEdition, Collation, IsClustered
[ ] Capture sp_configure (or equivalent) for: max server memory, min server memory, max degree of parallelism, cost threshold for parallelism, backup compression default, remote admin connections
[ ] tempdb: file names, sizes, growth, paths (via T-SQL once connected)
[ ] Agent: service state; count of operators; Database Mail profile exists?
[ ] Linked servers: name, product, data source (no passwords)
[ ] Write raw JSON/CSV payload with CollectedAt (UTC) and collector host

Keep collectors idempotent and off-peak. One script family, parameterized by host list.

Checklist — store (SQL + stored procedures)

Suggested minimal schema (names flexible; keep stable keys):

Table	Purpose
`dbo.SqlInstance`	One row per instance (natural key: host + instance)
`dbo.SqlInstanceConfig`	Latest config snapshot columns
`dbo.SqlInstanceConfigHistory`	Prior snapshots for drift
`dbo.SqlTempdbFile`	Current tempdb file layout
`dbo.SqlLinkedServer`	Linked server inventory
`dbo.CollectRun`	Run id, start/end, source, row counts, errors

Procs (lean set):

[ ] usp_SqlInstance_Upsert — merge instance + touch LastSeenAt
[ ] usp_SqlInstanceConfig_ApplySnapshot — write current; if changed vs last, insert history + set DriftFlag
[ ] usp_CollectRun_Start / _Complete — audit each PS run
[ ] usp_SqlInventory_GetCatalog — Blazor grid source (filter: drift, edition, outdated build)
[ ] usp_SqlInventory_GetInstanceDetail — single-instance blade

Retention: keep history ≥ 90 days (or until confirmed with Rick). Failures logged on CollectRun, not silent.

Checklist — present (Blazor Server)

[ ] Catalog page: instances, version/build, edition, last collect, drift badge
[ ] Filters: drifted only, Agent stopped, edition, version family
[ ] Detail blade: config values, tempdb layout, linked servers, last N history rows
[ ] Actions (read-first): link to runbook; optional “request recollect” flag for next PS cycle
[ ] Never show secrets; mask connection strings if stored

Map UI fields 1:1 to proc result sets — avoid ad-hoc SQL in the UI layer.

Practical thresholds (starting defaults)

Check	Flag when
Collect freshness	`LastSeenAt` older than 24–48 hours
Max memory	Unset / default on dedicated SQL host, or > 90% of RAM without leaving OS headroom (~4–8 GB+ depending on host)
tempdb files	Single data file on multi-core OLTP (review); unequal sizes
Agent	Critical instance and Agent not running
Build	Below agreed CU/GDR target (see Patching topic later)

Tune numbers with Rick once real hosts are in the catalog — don’t invent estate-wide policy yet.

Operator runbook (short)

Open Blazor inventory catalog → filter Drift or Stale collect
Open instance → note what changed (config history)
If unexpected: confirm with change log / owner before reverting
If collect failed: check PS run log + SQL CollectRun error; fix connectivity/permissions; re-queue
New server in AD but missing from catalog: add to allowlist / OU scope, re-run discover

Permissions notes

PS account: local admin or equivalent for service/registry discovery; SQL login with view-server-state + read on inventory DB for upsert path (dedicated writer login preferred)
Blazor app pool: read-only exec on usp_SqlInventory_*; no write to production user DBs

Done definition for this topic

[ ] Collectors writing to SQL for at least one real instance
[ ] Catalog UI shows that instance with last collect time
[ ] Drift path tested (change max memory in non-prod → next collect flags)
[ ] This note linked from the Knowledge Base list item

Next after this

Backup & restore readiness — uses this inventory as the population list.

SQL Dude — SQL-01 Inventory Baseline v1