HA/DR Awareness
Status: Reviewed (deep-dive v1)
Stack: PowerShell (topology discovery) → SQL health/procs → Blazor Server UI
Depends on: SQL-01 Inventory; SQL-02 Backup (DR floor); SQL-08 Patching (AG patch order)
Goal: Know what HA/DR you actually have, whether it’s healthy, and what the first moves are in a failover — without a novel on every Microsoft HA feature.
Why this next
Patching and backups assume you know primary vs secondary and how you’d recover. HA/DR awareness closes that loop. Document reality first (AG, log shipping, mirroring legacy, backup-only DR) — don’t invent Always On where none exists.
What “good” looks like
- Each critical DB tagged with DR pattern + RPO/RTO class (align with SQL-02)
- Topology inventory: AGs, replicas, roles, sync state, listeners
- Blazor: unhealthy replicas / suspended / not synchronizing surfaced
- Short failover / failback checklist per pattern in use
- Quorum / cluster notes only where WSFC is present
Patterns (pick what you deploy)
| Pattern |
Typical use |
Ops focus |
| Backup-only DR |
Smaller / cost-sensitive |
SQL-02 restore drills |
| Always On AG |
HA + readable secondaries |
Sync health, failover mode, backup preference |
| Log shipping |
DR with delay OK |
Copy/restore lag, alert on job fail |
| FCI (instance cluster) |
Instance-level HA |
Quorum, shared storage, failover history |
| Mirroring (legacy) |
Only if still present |
Plan migration; don’t expand |
Rick confirms which patterns exist; collectors discover and flag unknowns.
Checklist — collect
Always On (when present):
- [ ] AG name, cluster, listener DNS/port
- [ ] Replica: server, role, availability mode, failover mode, join state, connected state
- [ ] DB in AG: sync state, suspend reason
- [ ] Optional:
sys.dm_hadr_* lag estimates
Log shipping:
- [ ] Primary/secondary, last copy/restore times, status from
msdb log shipping tables
FCI / WSFC (PS):
- [ ] Cluster name, node online, resource group owner for SQL
Backup-only:
- [ ] Rely on SQL-02 coverage + restore-test freshness
Emit instance/AG keys + CollectedAt. Read-only — no failover from collectors.
Checklist — store (SQL + procs)
| Table |
Purpose |
dbo.HaTopology |
Pattern per instance/DB group |
dbo.AgReplica |
Replica health snapshot |
dbo.AgDatabase |
Per-DB sync state |
dbo.LogShipStatus |
Lag / job status |
dbo.HaIncident |
Failover events / tickets (manual + detected) |
Procs:
- [ ]
usp_Ha_ApplyAgSnapshot
- [ ]
usp_Ha_GetUnhealthy — Blazor board
- [ ]
usp_Ha_GetTopologyDetail
- [ ]
usp_Ha_GetDrReadiness — ties to backup class + last restore test
Checklist — present (Blazor)
- [ ] Unhealthy HA (not syncing, disconnected, suspended)
- [ ] Topology view (AG → replicas → role)
- [ ] DR readiness badge (backup Green + HA healthy / or backup-only OK)
- [ ] Actions v1: runbook links only — no forced failover from UI
Operator runbooks (short)
A — AG unhealthy / not synchronizing
- Confirm which DB/replica; check suspend reason
- Network / endpoint / service account / disk (SQL-06)
- Don’t resume blindly if data divergence risk — follow change control
- After fix: wait for synchronized; document in
HaIncident
B — Planned AG failover (overview)
- Backup Green; app owners notified; jobs quiesced (SQL-04)
- Prefer manual failover path for sync commit; respect async (possible data loss — explicit approval)
- Post: listener routing, Agent jobs on new primary, backup preference, patch notes (SQL-08)
- Failback only when healthy + approved
C — Backup-only DR invoke
- Declare RPO loss window
- Restore full/diff/log chain to DR instance (SQL-02)
- Smoke test + redirect apps
- Schedule reverse protection
D — Log shipping lag
- Check copy/restore Agent jobs (SQL-04)
- Disk on secondary; network share
- Catch-up or rebuild secondary if broken
Tie-ins
- SQL-02: DR floor is always backups — HA doesn’t replace them
- SQL-03/04: Prefer heavy maint on appropriate replica/role
- SQL-08: Patch secondaries / order per topology guidance
- SQL-07: Failover storms often show as blocking/waits during cutover
Common failure patterns
| Symptom |
Likely cause |
| Databases Not Synchronizing |
Suspended, network, redo blocked, disk full |
| App can’t connect after failover |
Listener DNS/SPN/firewall; connection string |
| Data loss surprise |
Async commit failover without approval |
| Jobs fail on new primary |
Jobs not set for failover / missing operators |
Permissions notes
- Collect: VIEW SERVER STATE / AG DMV rights; PS cluster read
- Failover: sysadmin / AG permission + change control — not Blazor
- Blazor: read procs only
Done definition
- [ ] Topology rows for real HA (or explicit backup-only tags)
- [ ] Unhealthy board works when something’s wrong (or stays empty when healthy)
- [ ] At least one pattern runbook matched to Rick’s estate
- [ ] List Notes link this doc
Next candidate
Alerting & runbooks — wire severity/ack across SQL-01..09 findings.
SQL Dude — SQL-09 HA/DR Awareness v1