Investigate Geo-DR Issues
Auto-detects platform via SERVERPROPERTY('EngineEdition'): SQL DB=5, MI=8, SQL Server=1-4, Fabric=12.
Scope
- For a proactive geo-DR readiness or RPO/RTO assessment, use the
geo-dr-assessment-health-check skill.
- For Always On AG replica or synchronization incidents, use the
investigate-ha-issues skill.
Parameters
| Parameter |
Default |
Description |
| DatabaseName |
(empty) |
Optional DB filter; empty = all databases |
| TopN |
10 |
Max rows returned per query |
| LookbackHours |
24 |
Recency window for restart/maintenance detection (GetMaintenanceAndScalingEvents) |
Prerequisites
- Connected to the target database (or
master for failover group queries on Azure SQL DB).
- Confirm which server and database you are connected to (e.g.
SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME(), SERVERPROPERTY('EngineEdition')); if not connected, ask the user for the target.
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-admin plugin (mssql_list_database_connections,
mssql_connect_database, then mssql_execute_query); any other SQL tool the agent host provides for
running T-SQL and reading result sets;
sqlcmd from a terminal. If the SQL
Tools MCP server is not connected or lists no connections, follow the sql-tools-mcp-setup skill,
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 STATE before SQL Server 2022) or, on Azure
SQL Database, VIEW DATABASE PERFORMANCE STATE or ##MS_ServerStateReader##, plus any other read
permission these queries need. A tool is only as read-only as the login it connects with.
Remediation Safety
- All diagnostic queries in this skill are read-only.
- Ending a session (
KILL), failing over, or changing replication settings 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:
KILL rolls back the session's open transaction and cannot be
undone, and a failover interrupts connections and can lose data that was not yet replicated.
Investigation Workflow
Execute all steps in order. Load and execute queries from references/queries.md — run the SQL exactly as written.
Step 1: Split-Brain Detection
Query: See references/queries.md#getsplitbraindetection
Checks replica roles, sync health, suspended state, quorum, and forced failover history.
| Finding |
Action |
Risk = HIGH_RISK (primary NOT SYNCHRONIZING + NOT_HEALTHY) |
Possible split-brain — escalate immediately |
Risk = WARNING (suspended replication) |
Data movement suspended — check GetReplicationBottleneckAnalysis |
QuorumState = FORCED_QUORUM |
Cluster running on forced quorum — risk of data loss on failover |
DB state = SUSPECT or EMERGENCY |
Database corruption — do not failover, engage recovery |
UNSUPPORTED platform |
Expected for Synapse/Fabric Warehouse — skip |
Step 2: Replication Bottleneck Analysis
Query: See references/queries.md#getreplicationbottleneckanalysis
Identifies geo-rep lag, send/redo queue bottlenecks, and synchronization issues.
| Finding |
Action |
Bottleneck = LINK_DOWN |
Geo-rep link suspended — check network, partner server health |
Bottleneck = STALLED (SecSinceRepl > 120) |
No replication progress — investigate partner availability |
Bottleneck = SEND_BOTTLENECK (SendQKB > 100MB) |
Network/throughput issue — check bandwidth, log rate |
Bottleneck = REDO_BOTTLENECK (RedoQKB > 100MB) |
Secondary can't keep up — check IO/CPU on secondary |
Bottleneck = BOTH_BOTTLENECK |
Both send and redo backed up — systematic issue |
Bottleneck = HEALTHY |
Replication healthy — proceed to Step 3 |
Step 3: Wait Stats Analysis
Query: See references/queries.md#getwaitstats
Captures long-running transactions, log space/rate, and HADR/log waits gating failover.
| Finding |
Action |
Readiness = NOT_READY (long txns + active HADR waits) |
Do not failover — resolve blocking transactions first |
Readiness = CAUTION (log > 85% or txn log > 1GB) |
High log usage — check LogReuseWait for holdup reason |
LogReuseWait not NOTHING/CHECKPOINT |
Log truncation blocked — investigate replication, active backup, etc. |
CRITICAL severity on LONG_TXN |
Kill or wait for long transaction before failover |
| High HADR wait types (avg > 5s) |
Replication transport under pressure — check network |
Step 4: Failover History
Query: See references/queries.md#getfailoverhistory
Shows recent role changes, failover events, and forced failover entries.
| Finding |
Action |
FORCED_FAILOVER or FORCE_FAILOVER_ALLOW_DATA_LOSS in log |
Data loss may have occurred — verify data integrity |
LOST_PRIMARY event |
Node lost primary role — correlate with GetMaintenanceAndScalingEvents |
REPL_SUSPENDED event |
Data movement was suspended — check if intentional |
| Frequent role transitions (> 2 in 24h) |
Flapping — check resource pressure and maintenance events |
MinAgo very large on geo-rep link |
Link may be stale — verify with GetReplicationBottleneckAnalysis |
Step 5: Maintenance and Scaling Events
Query: See references/queries.md#getmaintenanceandscalingevents
Detects recent restarts, scaling/SLO operations, and configuration changes. Tune {{LookbackHours}} to adjust the detection window.
| Finding |
Action |
RestartedInWindow = YES |
Recent restart — correlate timing with failover/lag events |
SCALING_OP state = FAILED or CANCELLED |
Failed SLO change — retry or investigate error |
SCALING_OP state = IN_PROGRESS or PENDING |
Scaling in progress — wait for completion before failover |
CONFIG_CHANGE event |
Configuration changed — verify if intentional |
| No events in window |
No maintenance activity — issue is likely workload-driven |
Step 6: Resource Pressure
Query: See references/queries.md#getresourcepressure
Note: This query contains two 1-second WAITFOR DELAY snapshots — inform the user before executing.
Measures CPU, IO, memory, scheduler, and signal-wait pressure.
| Finding |
Action |
CPUPct >= 90 or SQLCPUPct >= 90 |
CPU saturation — check for runaway queries, missing indexes |
DataIOPct >= 90 or AvgReadMs >= 50 |
IO bottleneck — check for table scans, storage throttling |
LogWritePct >= 90 |
Log IO pressure — use the investigate-sql-log-io-pressure skill |
GrantsPending > 0 |
Memory grant waits — queries queuing for memory |
AvgRunnable >= 5 |
Scheduler pressure — CPU oversubscription |
MemState contains low |
System-wide memory pressure — check for memory-intensive queries |
Step 7: Drill by Severity
Prioritize findings: OK < WARNING < CRITICAL. Address CRITICAL items first.
Queries
All diagnostic T-SQL lives in references/queries.md. Each query is platform-aware (auto-detects EngineEdition) and is run with the SQL query tool.
Gotchas
- Azure SQL DB geo-rep DMVs (
sys.dm_geo_replication_link_status) are not available in master — connect to a user database.
gl.last_replication is datetimeoffset (UTC) — queries use SYSUTCDATETIME() (not SYSDATETIME()) for DATEDIFF comparisons.
GetResourcePressure contains two 1-second WAITFOR DELAY snapshots. Warn the user before executing.
sys.dm_db_resource_stats is not supported on MI (@E=8) — MI uses sys.server_resource_stats instead.
secondary_lag_seconds column requires SQL 2016 SP2 / 2017+; the query gracefully emits NULL on older builds.
Guidance
- SQL DB: db-level geo-rep vs FG; MI: instance-level (all DBs); SQL Server: check quorum before AG failover
- Related: the
health-audit-sqlserver, check-live-blocking, and wait-category-analysis skills
1---2name: investigate-geo-dr-issues3description: "Use when a geo-secondary lags behind the primary right now, a geo-replication link is suspended, or a failover group fails over unexpectedly. For a readiness check, use `geo-dr-assessment-health-check`."4user-invocable: true5argument-hint: "[DatabaseName]"6metadata:7 source: https://skillmd.com/skills/microsoft/investigate-geo-dr-issues8---910# Investigate Geo-DR Issues11Auto-detects platform via `SERVERPROPERTY('EngineEdition')`: SQL DB=5, MI=8, SQL Server=1-4, Fabric=12.1213## Scope1415- For a proactive geo-DR readiness or RPO/RTO assessment, use the `geo-dr-assessment-health-check` skill.16- For Always On AG replica or synchronization incidents, use the `investigate-ha-issues` skill.1718## Parameters1920| Parameter | Default | Description |21|-----------|---------|-------------|22| DatabaseName | *(empty)* | Optional DB filter; empty = all databases |23| TopN | 10 | Max rows returned per query |24| LookbackHours | 24 | Recency window for restart/maintenance detection (`GetMaintenanceAndScalingEvents`) |2526## Prerequisites2728- Connected to the target database (or `master` for failover group queries on Azure SQL DB).29- Confirm which server and database you are connected to (e.g. `SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME(), SERVERPROPERTY('EngineEdition')`); if not connected, ask the user for the target.3031## Required tools3233- A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL34 Tools MCP server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`,35 `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides for36 running T-SQL and reading result sets;37 [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the SQL38 Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup` skill,39 which asks before installing or configuring anything. If no SQL tool can be made available, give the40 user the T-SQL to run and ask for the results.41- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that42 holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure43 SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read44 permission these queries need. A tool is only as read-only as the login it connects with.4546## Remediation Safety4748- All diagnostic queries in this skill are read-only.49- Ending a session (`KILL`), failing over, or changing replication settings are50 **recommendations only**.51- Never run any of them without the user's explicit confirmation for that specific command. A52 request to investigate, diagnose, or "fix" the problem is not that confirmation.53- Before asking, explain the impact: `KILL` rolls back the session's open transaction and cannot be54 undone, and a failover interrupts connections and can lose data that was not yet replicated.5556## Investigation Workflow5758Execute all steps in order. Load and execute queries from [references/queries.md](references/queries.md) — run the SQL exactly as written.5960---6162### Step 1: Split-Brain Detection6364> **Query:** See [references/queries.md#getsplitbraindetection](references/queries.md#getsplitbraindetection)6566Checks replica roles, sync health, suspended state, quorum, and forced failover history.6768| Finding | Action |69|---------|--------|70| `Risk` = `HIGH_RISK` (primary NOT SYNCHRONIZING + NOT_HEALTHY) | Possible split-brain — escalate immediately |71| `Risk` = `WARNING` (suspended replication) | Data movement suspended — check `GetReplicationBottleneckAnalysis` |72| `QuorumState` = `FORCED_QUORUM` | Cluster running on forced quorum — risk of data loss on failover |73| DB state = `SUSPECT` or `EMERGENCY` | Database corruption — do not failover, engage recovery |74| `UNSUPPORTED` platform | Expected for Synapse/Fabric Warehouse — skip |7576---7778### Step 2: Replication Bottleneck Analysis7980> **Query:** See [references/queries.md#getreplicationbottleneckanalysis](references/queries.md#getreplicationbottleneckanalysis)8182Identifies geo-rep lag, send/redo queue bottlenecks, and synchronization issues.8384| Finding | Action |85|---------|--------|86| `Bottleneck` = `LINK_DOWN` | Geo-rep link suspended — check network, partner server health |87| `Bottleneck` = `STALLED` (SecSinceRepl > 120) | No replication progress — investigate partner availability |88| `Bottleneck` = `SEND_BOTTLENECK` (SendQKB > 100MB) | Network/throughput issue — check bandwidth, log rate |89| `Bottleneck` = `REDO_BOTTLENECK` (RedoQKB > 100MB) | Secondary can't keep up — check IO/CPU on secondary |90| `Bottleneck` = `BOTH_BOTTLENECK` | Both send and redo backed up — systematic issue |91| `Bottleneck` = `HEALTHY` | Replication healthy — proceed to Step 3 |9293---9495### Step 3: Wait Stats Analysis9697> **Query:** See [references/queries.md#getwaitstats](references/queries.md#getwaitstats)9899Captures long-running transactions, log space/rate, and HADR/log waits gating failover.100101| Finding | Action |102|---------|--------|103| `Readiness` = `NOT_READY` (long txns + active HADR waits) | Do not failover — resolve blocking transactions first |104| `Readiness` = `CAUTION` (log > 85% or txn log > 1GB) | High log usage — check `LogReuseWait` for holdup reason |105| `LogReuseWait` not `NOTHING`/`CHECKPOINT` | Log truncation blocked — investigate replication, active backup, etc. |106| `CRITICAL` severity on `LONG_TXN` | Kill or wait for long transaction before failover |107| High HADR wait types (avg > 5s) | Replication transport under pressure — check network |108109---110111### Step 4: Failover History112113> **Query:** See [references/queries.md#getfailoverhistory](references/queries.md#getfailoverhistory)114115Shows recent role changes, failover events, and forced failover entries.116117| Finding | Action |118|---------|--------|119| `FORCED_FAILOVER` or `FORCE_FAILOVER_ALLOW_DATA_LOSS` in log | Data loss may have occurred — verify data integrity |120| `LOST_PRIMARY` event | Node lost primary role — correlate with `GetMaintenanceAndScalingEvents` |121| `REPL_SUSPENDED` event | Data movement was suspended — check if intentional |122| Frequent role transitions (> 2 in 24h) | Flapping — check resource pressure and maintenance events |123| `MinAgo` very large on geo-rep link | Link may be stale — verify with `GetReplicationBottleneckAnalysis` |124125---126127### Step 5: Maintenance and Scaling Events128129> **Query:** See [references/queries.md#getmaintenanceandscalingevents](references/queries.md#getmaintenanceandscalingevents)130131Detects recent restarts, scaling/SLO operations, and configuration changes. Tune `{{LookbackHours}}` to adjust the detection window.132133| Finding | Action |134|---------|--------|135| `RestartedInWindow` = `YES` | Recent restart — correlate timing with failover/lag events |136| `SCALING_OP` state = `FAILED` or `CANCELLED` | Failed SLO change — retry or investigate error |137| `SCALING_OP` state = `IN_PROGRESS` or `PENDING` | Scaling in progress — wait for completion before failover |138| `CONFIG_CHANGE` event | Configuration changed — verify if intentional |139| No events in window | No maintenance activity — issue is likely workload-driven |140141---142143### Step 6: Resource Pressure144145> **Query:** See [references/queries.md#getresourcepressure](references/queries.md#getresourcepressure)146> **Note:** This query contains two 1-second `WAITFOR DELAY` snapshots — inform the user before executing.147148Measures CPU, IO, memory, scheduler, and signal-wait pressure.149150| Finding | Action |151|---------|--------|152| `CPUPct` >= 90 or `SQLCPUPct` >= 90 | CPU saturation — check for runaway queries, missing indexes |153| `DataIOPct` >= 90 or `AvgReadMs` >= 50 | IO bottleneck — check for table scans, storage throttling |154| `LogWritePct` >= 90 | Log IO pressure — use the `investigate-sql-log-io-pressure` skill |155| `GrantsPending` > 0 | Memory grant waits — queries queuing for memory |156| `AvgRunnable` >= 5 | Scheduler pressure — CPU oversubscription |157| `MemState` contains `low` | System-wide memory pressure — check for memory-intensive queries |158159---160161### Step 7: Drill by Severity162163Prioritize findings: **OK** < **WARNING** < **CRITICAL**. Address CRITICAL items first.164165## Queries166167All diagnostic T-SQL lives in [references/queries.md](references/queries.md). Each query is platform-aware (auto-detects `EngineEdition`) and is run with the SQL query tool.168169## Gotchas170171- Azure SQL DB geo-rep DMVs (`sys.dm_geo_replication_link_status`) are **not available in `master`** — connect to a user database.172- `gl.last_replication` is `datetimeoffset` (UTC) — queries use `SYSUTCDATETIME()` (not `SYSDATETIME()`) for DATEDIFF comparisons.173- `GetResourcePressure` contains two 1-second `WAITFOR DELAY` snapshots. Warn the user before executing.174- `sys.dm_db_resource_stats` is not supported on MI (`@E=8`) — MI uses `sys.server_resource_stats` instead.175- `secondary_lag_seconds` column requires SQL 2016 SP2 / 2017+; the query gracefully emits NULL on older builds.176177## Guidance178- SQL DB: db-level geo-rep vs FG; MI: instance-level (all DBs); SQL Server: check quorum before AG failover179- Related: the `health-audit-sqlserver`, `check-live-blocking`, and `wait-category-analysis` skills