SQL Server Maintenance Knowledge Base — Topic Outline
Audience: Rick Childers
Stack mapping: PowerShell (Server/AD collection) → SQL Server + stored procedures → Blazor Server UI
Hub: Chief of Staff Data → SQL Knowledge (Documents + Knowledge Base list + Knowledge page)
Working style: Structured notes, checklists, short deep-dives. Deepen one topic at a time.
How topics map to the stack
| Layer |
Role |
| PowerShell |
Discover hosts/instances, AD context, disk/OS health, schedule collectors, export raw facts |
| SQL + stored procs |
Persist baselines, retention, scoring/thresholds, maintenance procedures, history |
| Blazor Server |
Dashboards, drill-downs, ack/assign, runbook links, operator workflows |
Every lasting topic should eventually answer: What do we collect? Where is it stored? What does the UI show? What does an operator do?
Priority outline (lean)
1. Inventory & configuration baseline (start here)
- Instances, versions/editions, features, collations, max memory, tempdb layout
- Linked servers, credentials, Agent operators/mail
- PS: WMI/SMO/registry + AD computer membership
- SQL: Inventory tables + upsert procs
- Blazor: Instance catalog, drift vs last baseline
2. Backup & restore readiness
- Full / diff / log coverage, retention, checksum, restore-verify cadence
- RPO/RTO targets per database class
- PS: Probe backup history + file presence on share
- SQL: Coverage scoring procs; restore-test log
- Blazor: Coverage heat map, failed backup queue
3. Index & statistics maintenance
- Fragmentation vs write cost; rebuild/reorg thresholds; stats update policy
- Offline vs online; maintenance windows
- PS: Optional host disk pressure before heavy jobs
- SQL: Index health procs; Agent job wrappers
- Blazor: Top fragmented objects, last run status
4. SQL Agent jobs & schedules
- Critical jobs inventory, failure history, duration baselines
- Overlap / runaway detection
- PS: Cross-instance job census
- SQL: Job history warehouse + alert procs
- Blazor: Failed/long-running job board
5. Security & access hygiene
- Logins/users/roles, orphaned users, sysadmin sprawl, xp_cmdshell, guest
- Certificate/key expiration (where applicable)
- PS: AD group membership for Windows logins
- SQL: Permission snapshot procs
- Blazor: Privilege exceptions list
6. Space, growth & capacity
- Data/log file growth, Autogrowth events, volume free space
- PS: Volume free space + mount points
- SQL: File size history, growth forecasts
- Blazor: Capacity trend cards, threshold alerts
7. Performance health (ops view, not deep tuning)
- Wait stats snapshot, blocking, top expensive queries (DMVs)
- Plan cache hygiene notes (high level)
- PS: Schedule collectors off-peak
- SQL: Snapshot tables + retention
- Blazor: Hot waits / blockers / top SQL cards
8. Patching & build currency
- CU/GDR level vs target; instance restart windows
- PS: Build/version collect + Windows patch context
- SQL: Target matrix table
- Blazor: Out-of-date instances list
9. HA/DR awareness (overview first)
- Backup-based DR vs Always On / log shipping / mirroring (as deployed)
- Failover runbook pointers only until environment confirmed
- Stack: Same inventory + health probes; UI status only when HA exists
10. Alerting, runbooks & change control
- Severity model, ack/escalate, post-incident checklist
- Link each alert type to a short runbook doc in SQL Knowledge
- Blazor: Ack workflow; SQL: audit of actions; PS: optional ticket/email hooks
Suggested first artifacts (this seed)
- Starter guide (Documents) — how to use the hub + conventions
- List rows (Draft) — one per priority topic above (1–5 first)
- Next deep-dive candidate: Inventory & configuration baseline
Conventions (keep usage-efficient)
- Prefer one checklist or one-pager per topic before long essays
- Tag list items:
Draft → Reviewed → Canonical
- Name docs:
SQL-NN-Topic-ShortName.md (or .docx if editing in browser)
- Always note PS collect → SQL store → Blazor surface in the note header
- No sprawling research runs; deepen one list item at a time
Seeded by SQL Dude — outline v1