SQL Server Maintenance Outline

Status: Reviewed (deep-dive v1)
Audience: SQL Server admins / PowerShell-to-SQL operators
Stack mapping: PowerShell (Server/AD collection) → SQL Server + stored procedures → Blazor Server UI
Working style: Structured notes, checklists, short deep-dives. Deepen one topic at a time.
Pairs with: Starter Guide, ops topics Inventory & configuration baseline–10, implementation spikes, SQL CLI notes.
Goal: Use this outline as the learning path and backlog map for the SQLKB — not as a second encyclopedia.

Purpose

This outline is the map of the knowledge base:

1. What to learn next — ordered ops themes (01–10) before specialty T-SQL, spikes, and CLI.
2. How each theme hits the stack — every topic should eventually answer collect → store → show → act.
3. Where detail lives — deep-dives, spikes, and CLI notes hang off this spine; do not duplicate them here.

Use it when onboarding, when picking the next deep-dive, or when checking that a Blazor board still maps to a real ops concern.

How the outline maps to SQLKB topics

Ops spine (start here)

Implementation spikes (build the boards)

Spikes turn outline themes into vertical slices (table → proc → Blazor):

CLI & procedure toolbox (how you touch SQL)

Stack mapping (every lasting topic)

Header habit on deep-dives: What do we collect? Where is it stored? What does the UI show? What does an operator do?

Learning path (usage-efficient)

1. Read the Starter Guide for conventions.
2. Walk ops themes 1 → 5 (inventory, backup, index/stats, Agent, security) before specialty T-SQL.
3. For each theme: skim this outline row → open the SQL-0N deep-dive → optionally the matching spike when ready to build.
4. Add CLI notes when you need a concrete invoke path (sqlcmd, bcp, SqlServer module).
5. Pull T-SQL 13–30+ only when a board or proc design needs that pattern (not as the starting curriculum).
6. Deepen one list item at a time; prefer checklist → short deep-dive → spike.

Suggested first build slice: Inventory baseline + Inventory MVP.

Ops checklist hooks (from the outline)

Use these as acceptance prompts when a theme or spike is “Reviewed”:

• [ ] Inventory — instance catalog exists; drift vs last baseline visible
• [ ] Backup — coverage scored; failures queued; RPO class documented
• [ ] Index/stats — thresholds written; Agent (or equivalent) run status visible
• [ ] Agent jobs — critical jobs tagged; fail/long board live
• [ ] Security — sysadmin sprawl / orphans / dangerous config on an exceptions list
• [ ] Capacity — volume free space + growth history with thresholds
• [ ] Perf (ops) — scheduled wait/blocker/top-SQL snapshots with retention
• [ ] Patching — build vs target matrix; out-of-date list
• [ ] HA/DR — patterns discovered; at least one runbook pointer per pattern
• [ ] Alerting — severity model; ack path; runbook link per alert family

Priority outline (detail spine)

1. Inventory & configuration baseline

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: catalog + drift

2. Backup & restore readiness

Full / diff / log coverage, retention, checksum, restore-verify cadence; RPO/RTO by database class.
PS: backup history + file presence · SQL: coverage scoring · Blazor: heat map + failed queue

3. Index & statistics maintenance

Fragmentation vs write cost; rebuild/reorg thresholds; stats policy; online vs offline windows.
PS: optional disk pressure · SQL: index health procs · Blazor: top fragmented + last run

4. SQL Agent jobs & schedules

Critical job inventory, failure history, duration baselines, overlap / runaway detection.
PS: cross-instance census · SQL: job history warehouse · Blazor: failed/long board

5. Security & access hygiene

Logins/users/roles, orphans, sysadmin sprawl, xp_cmdshell, guest; cert/key expiry where applicable.
PS: AD group membership · SQL: permission snapshots · Blazor: privilege exceptions

6. Space, growth & capacity

Data/log growth, autogrowth events, volume free space.
PS: volumes / mount points · SQL: file size history · Blazor: trends + thresholds

7. Performance health (ops view, not deep tuning)

Wait snapshots, blocking, top expensive queries; plan-cache hygiene at a high level.
PS: off-peak collectors · SQL: snapshot + retention · Blazor: waits / blockers / top SQL

8. Patching & build currency

CU/GDR vs target; restart windows.
PS: build + Windows patch context · SQL: target matrix · Blazor: out-of-date list

9. HA/DR awareness

Backup-based DR vs Always On / log shipping / mirroring as deployed; failover runbook pointers until confirmed.
Stack: inventory + health probes; UI status only when HA exists

10. Alerting, runbooks & change control

Severity model, ack/escalate, post-incident checklist; each alert type links to a short runbook.
Blazor: ack workflow · SQL: action audit · PS: optional ticket/email hooks

Conventions (keep usage-efficient)

• Prefer one checklist or one-pager per topic before long essays
• Topic status habit: Draft → Reviewed → deepen only when needed
• Filenames: SQL-NN-Topic-ShortName.md (HTML generated beside them)
• Always note PS collect → SQL store → Blazor surface in the note header
• No sprawling research runs; deepen one outline item at a time
• Local hub: GeeksForgeSite → SQLKB (Home on the menu returns to the parent site)

See also

• Starter Guide
• Ops deep-dives Inventory & configuration baseline–10 (table above)
• Implementation spikes Inventory MVP–08
• Stored Procedures and Functions menu sections for proc/UDF/System stored procedures material