Alerting & Runbooks

Status: Reviewed (deep-dive v1)
Stack: PowerShell (optional email/webhook hooks) → SQL alert warehouse/procs → Blazor Server UI (ack/assign)
Depends on: Findings from SQL-01..09 (inventory stale, backup Red, job fail, security, capacity, perf, patch overdue, HA unhealthy)
Goal: One severity model, one ack workflow, and a short runbook link per alert type — so noise doesn’t bury real fires.

Why this closes the outline

Topics 01–09 create signals. Without alerting hygiene you get either silence or pager fatigue. This note is the glue: normalize, route, ack, and point to the right deep-dive.

What “good” looks like

Shared severity: Critical / High / Medium / Low (or Red/Yellow mapping)
Every alert type maps to a SharePoint runbook doc (SQL-0N…)
Blazor: open alerts, ack, assign, snooze/exception with expiry
Dedup / correlation so one root cause ≠ 50 tickets
Audit: who acked what and when
No auto-remediation from UI in v1 (observe + human action)

Severity model (starting — confirm with Rick)

Severity	Examples	Response bias
Critical	Backup Red on Tier A; AG not syncing prod; disk < 10%; sysadmin surprise; Agent down on prod	Immediate
High	Job Critical failed; log overdue FULL; blocking > N min; patch GDR overdue	Same business day
Medium	Index queue backlog; capacity Yellow; perf wait spike without user impact	Scheduled
Low	Stale collect; informational drift	Digest

Map each collector flag → severity in dbo.AlertRule.

Alert catalog (wire to existing topics)

Alert type	Source topic	Default runbook
`InventoryStale`	SQL-01	SQL-01
`BackupOverdue` / `BackupNever`	SQL-02	SQL-02
`IndexMaintFailed`	SQL-03	SQL-03
`AgentJobFailed` / `AgentDisabledCritical`	SQL-04	SQL-04
`SecurityFinding`	SQL-05	SQL-05
`VolumeTight` / `GrowthForecast`	SQL-06	SQL-06
`Blocking` / `HotWait` / `TopQuerySpike`	SQL-07	SQL-07
`BuildOutOfDate`	SQL-08	SQL-08
`HaUnhealthy`	SQL-09	SQL-09

Add types sparingly; prefer enriching existing ones.

Checklist — detect & store

Table	Purpose
`dbo.AlertRule`	Type → severity, threshold, enabled, runbook URL
`dbo.Alert`	Open/closed instances (natural key: type+entity+fingerprint)
`dbo.AlertEvent`	State changes (open, ack, escalate, close)
`dbo.AlertAck`	Who/when/comment
`dbo.AlertException`	Approved suppressions with expiry

Procs:

[ ] usp_Alert_Evaluate — run after collects; upsert open alerts; auto-close cleared
[ ] usp_Alert_Ack — Blazor ack
[ ] usp_Alert_GetOpenBoard
[ ] usp_Alert_GetDetail — timeline + runbook link

Dedup: same type + instance + object fingerprint within window updates LastSeenAt, doesn’t spam new rows.

Checklist — notify (keep lean)

[ ] Blazor badge counts (always on)
[ ] Optional: email via Database Mail / PS Send-MailMessage / webhook — Rick chooses channel
[ ] Critical: notify on open; High: notify on open or digest Q1h; Medium/Low: daily digest
[ ] Never put secrets in notification body

PS role: optional dispatcher reading usp_Alert_GetUnnotified.

Checklist — present (Blazor)

[ ] Open alerts board (severity, age, instance, type, ack state)
[ ] Filters: Critical only, unacked, mine
[ ] Detail: description, runbook button (deep link to SQL Knowledge doc), timeline
[ ] Actions: Ack, Assign, Snooze until, Request exception — no kill/failover/patch
[ ] Home: count by severity

Operator workflow (short)

Open board → Critical unacked first
Open detail → read runbook link (correct SQL-0N)
Ack with short note (“investigating backup share”)
Fix using that topic’s checklist
Confirm signal cleared on next evaluate (or manually close if rule allows)
If recurring noise: tune threshold or add dated exception — don’t forever-mute Critical without Rick

Post-incident mini checklist

[ ] Timeline captured in AlertEvent
[ ] Root cause one-liner in Notes / ticket
[ ] Runbook gap? → amend the SQL-0N doc (small PR-style edit)
[ ] Rule tune if false positive

Common failure patterns

Symptom	Likely cause
Alert storm	Missing dedup; threshold too tight; collect flap
Silent failure	Evaluate job not running (SQL-04); rules disabled
Acked forever open	Auto-close not wired; finder still true
Wrong runbook	Alert type mis-mapped in `AlertRule`

Permissions notes

Evaluate job: write to alert tables only
Blazor: exec ack/board procs; authenticated user identity for audit
Notification creds: secured; not in source docs

Done definition

[ ] AlertRule rows for core types covering SQL-01..09
[ ] Open board + ack path working
[ ] At least one Critical path tested end-to-end (open → ack → clear)
[ ] List Notes link this doc

Outline complete (SQL-01..10)

#	Topic	Doc
01	Inventory & configuration baseline	`SQL-01-Inventory-Baseline.md`
02	Backup & restore readiness	`SQL-02-Backup-Restore-Readiness.md`
03	Index & statistics maintenance	`SQL-03-Index-Statistics-Maintenance.md`
04	SQL Agent jobs & schedules	`SQL-04-SQL-Agent-Jobs-Schedules.md`
05	Security & access hygiene	`SQL-05-Security-Access-Hygiene.md`
06	Capacity & growth	`SQL-06-Capacity-Growth.md`
07	Performance health	`SQL-07-Performance-Health.md`
08	Patching & upgrades	`SQL-08-Patching-Upgrades.md`
09	HA/DR awareness	`SQL-09-HA-DR.md`
10	Alerting & runbooks	`SQL-10-Alerting-Runbooks.md`

Beyond this outline: wait for Rick (implementation spikes, estate-specific targets, major-upgrade playbook, etc.).

SQL Dude — SQL-10 Alerting & Runbooks v1