High Availability Investigation
Purpose
Primary entry point for HA investigations across SQL Server, Azure SQL, and Fabric. Use when users report AG sync issues, unhealthy replicas, failovers, large redo/log send queues, seeding failures, lease timeout symptoms, or geo-replication lag.
This skill follows a 12-step workflow:
- Detect platform, edition, and HA capability
- Review AG configuration and replica settings
- Check replica health, roles, and connectivity
- Analyze per-database sync state, redo queue, and log send queue
- Capture HADR wait statistics over a short snapshot
- Review failover history and AG-related errors
- Validate AG endpoint and transport configuration
- Measure log send and transport performance
- Inspect redo thread activity on the secondary
- Check WSFC cluster health (on-premises/IaaS only)
- Monitor automatic seeding progress (SQL 2016+/MI)
- Review Azure SQL DB / Fabric geo-replication status
Scope
- For Azure SQL geo-secondary lag, geo-replication link, or failover group incidents, use the
investigate-geo-dr-issuesskill. - For a proactive HA posture assessment with no active incident, use the
ha-assessment-health-checkskill.
Required tools
- A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL
Tools MCP server that ships with the
microsoft-sql-adminplugin (mssql_list_database_connections,mssql_connect_database, thenmssql_execute_query); any other SQL tool the agent host provides for running T-SQL and reading result sets;sqlcmdfrom a terminal. If the SQL Tools MCP server is not connected or lists no connections, follow thesql-tools-mcp-setupskill, which asks before installing or configuring anything. If no SQL tool can be made available, give the user the T-SQL to run and ask for the results. - Least privilege: connect that tool with a diagnostic login that cannot write, for example one that
holds only
VIEW SERVER PERFORMANCE STATE(VIEW SERVER STATEbefore SQL Server 2022) or, on Azure SQL Database,VIEW DATABASE PERFORMANCE STATEor##MS_ServerStateReader##, plus any other read permission these queries need. A tool is only as read-only as the login it connects with.
Prerequisites
- Before running queries, confirm which server and database you are connected to (e.g.
SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME(), SERVERPROPERTY('EngineEdition')). - Run each step's query from
references/queries.mdwith the SQL query tool. - Run Steps 3–9 while the issue is active; connect directly to a secondary for Step 9.
- Run Step 10 only on on-premises or IaaS SQL Server with WSFC.
Parameters
| Parameter | Default | Description |
|---|---|---|
| AGName | (optional) | Filter to a specific availability group name. If omitted, shows all AGs |
| LookbackMinutes | 60 |
How far back to look for failover events and state changes |
| RedoQueueThresholdKB | 100000 |
Redo queue size (KB) threshold for warnings (~100 MB) |
| LogSendQueueThresholdKB | 50000 |
Log send queue size (KB) threshold for warnings (~50 MB) |
| SnapshotDurationSeconds | 30 |
Duration for HADR wait statistics snapshot |
Remediation Safety
- All diagnostic queries in this skill are read-only.
- State-changing commands in the references (
ALTER DATABASE ... SET HADR RESUMEandALTER EVENT SESSION ... STATE = START) are recommendations only. - Never run any of them without the user's explicit confirmation for that specific command. A request to investigate, diagnose, or "fix" the problem is not that confirmation.
- Before asking, explain the impact: resuming data movement on a replica restarts log send and redo, which adds load on both replicas.
Investigation Workflow
Step 1: Platform Detection and HA Capability
Load and execute the Step 1 query from references/queries.md. It determines edition, version, EngineEdition, and whether HADR is enabled.
Routing: editions 1/4/6/9/11 stop; edition 5 or 12 skips to Step 12; editions 2/3/8 with HADR enabled proceed to Steps 2–11.
Step 2: AG Configuration Overview
Load and execute the Step 2 query from references/queries.md to review AG topology, sync mode, failover mode, session timeout, health timeout, seeding mode, and endpoint URL.
Key signals: aggressive failure_condition_level, invalid automatic failover + async combinations, risky timeouts, and automatic seeding choices.
Step 3: AG and Replica Health Status
Load and execute the Step 3 query from references/queries.md to inspect replica role, operational state, connectivity, sync health, recovery health, and last connection errors.
Key signals: RESOLVING, DISCONNECTED, NOT_HEALTHY, PENDING_FAILOVER, or populated last_error_number / last_error_description.
Step 4: Database Replica States — Sync, Redo Queue, Log Send Queue
Load and execute the Step 4 query from references/queries.md. This is the primary per-database view for sync_state, sync_health, redo queue, log send queue, redo/log send rates, and estimated RPO/RTO impact.
Key signals: queue sizes above thresholds, NOT SYNCHRONIZING, old last_redone_time, long estimated redo/log drain times, and non-null suspend_reason.
Step 5: HADR Wait Statistics Analysis
Load and execute the Step 5 query from references/queries.md to capture a SnapshotDurationSeconds delta for HADR-related waits.
Key signals: dominant HADR_SYNC_COMMIT, HADR_CLUSAPI_CALL, HADR_LOG_BLOCK_FLUSH, PARALLEL_REDO_*, DIRTY_PAGE_TABLE_LOCK, or REDO_THREAD_PENDING_WORK waits.
Step 6: Failover History from Ring Buffers
Load and execute the Step 6 query from references/queries.md to pull recent AlwaysOn_health ring buffer transitions and AG-related error-log entries.
Key signals: repeated state changes, RESOLVING -> PRIMARY transitions, lease timeout 19407, connection timeout 35206, join error 35201, or a missing AlwaysOn_health session.
Step 7: AG Endpoint and Transport Health
Load and execute the Step 7 query from references/queries.md to validate the database mirroring endpoint used by AG transport.
Key signals: stopped or disabled endpoints, missing endpoints, auth mismatches, disabled encryption, and endpoint-port firewall issues.
Step 8: Log Send and Transport Performance
Load and execute the Step 8 query from references/queries.md for an outbound transport view by database.
Key signals: growing log_send_queue_size_kb, time_to_drain_sec > 60, high send/receive latency, high receive/harden latency, or zero send rate with a non-zero queue.
Step 9: Redo Thread Analysis on Secondary
Load and execute the Step 9 query from references/queries.md while connected to the secondary replica.
Key signals: redo blocked by DIRTY_PAGE_TABLE_LOCK, storage waits such as PAGEIOLATCH_* or WRITELOG, PARALLEL_REDO_TRAN_TURN, REDO_THREAD_PENDING_WORK, or a blocking session.
Step 10: Cluster Health — On-Premises / IaaS Only
Load and execute the Step 10 query from references/queries.md only for box-product SQL Server on-premises or IaaS VMs.
Key signals: non-normal quorum, down members, missing witness, missing cluster metadata, single-node membership, or no public cluster network.
Step 11: Seeding Progress Monitoring
Load and execute the Step 11 query from references/queries.md only on SQL Server 2016+ or Azure SQL Managed Instance.
Key signals: failed or stalled automatic seeding, low transfer rate, pct_complete not moving, or seeding completed without the database reaching SYNCHRONIZED.
Step 12: Azure SQL DB and Fabric Geo-Replication Status
Load and execute the Step 12 query from references/queries.md only for Azure SQL Database (EngineEdition 5) and Fabric SQL Database (EngineEdition 12).
Key signals: replication_lag_sec > 30, prolonged CATCH_UP, SEEDING, SUSPENDED, missing geo-replication rows, or being connected to the secondary role.
Report & Decision Tree
Use references/report-template.md to assemble findings into the final report. It contains the report template plus the root-cause decision tree for resolving RESOLVING replicas, large redo/log send queues, dominant HADR waits, seeding failures, geo-replication lag, and frequent failovers.