HA Environment Assessment & Health Check
Proactive assessment only. Do not change configuration unless the user explicitly asks.
For active issues, use investigate-ha-issues instead.
Scope
- Validates SQL Server Always On AGs (cluster, AG configuration, failover readiness, listeners, endpoints, database settings, backups) and the HA posture of Azure SQL Database, Managed Instance, and Fabric SQL Database, with pass/fail/warning findings.
- For geo-replication, failover groups, geo-secondary planned-failover readiness, or geo-DR RPO/RTO, use the
geo-dr-assessment-health-check skill.
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.
Prerequisites
- Confirm which instance you are connected to (e.g.
SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME(), SERVERPROPERTY('EngineEdition')).
- Connect to the primary replica for full AG visibility.
Parameters
| Parameter |
Default |
Description |
| AGName |
(all AGs) |
Filter to a specific AG. Omit to assess all. |
Remediation Safety
- All assessment queries in this skill are read-only.
- State-changing commands in the references (
ALTER AVAILABILITY GROUP including FAILOVER and
FORCE_FAILOVER_ALLOW_DATA_LOSS, ALTER DATABASE, sp_configure with RECONFIGURE, and
CREATE LOGIN) 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 and rollback. A failover interrupts every connection to the
availability group, and
FORCE_FAILOVER_ALLOW_DATA_LOSS can lose committed transactions.
Workflow
Execute steps in order. Step 1 determines which steps apply per platform.
Note on step numbering: There is intentionally no Step 7 — the workflow runs 1–6 then 8–13. The numbers are stable identifiers referenced by the routing matrix, report template, and remediation guide, so the gap is preserved deliberately rather than renumbered.
Step 1: Platform Detection and HA Capability
Run with the SQL query tool.
Query: See references/queries.md#step-1-platform-detection-and-ha-capability
Platform routing — use this matrix for all subsequent steps:
| Engine Edition |
Steps to Execute |
Query Reference |
| 3 (Enterprise) |
Steps 1–6, 8, 9, 11, 12, 13 — requires IsHadrEnabled = 1 for steps 2–6, 8, 9, 12–13 |
Step 1: queries.md, Steps 2–6, 8, 9, 12–13: queries-sqlserver.md, Step 11: queries-azure.md |
| 2 (Standard) |
1, 2, 3, 4, 5, 6, 8, 9, 11, 12 — requires IsHadrEnabled = 1 for AG steps |
Step 1: queries.md, Steps 2–6, 8, 9, 12: queries-sqlserver.md, Step 11: queries-azure.md |
| 5 (Azure SQL DB) |
1, 10, 11 only |
Step 1: queries.md, Steps 10–11: queries-azure.md |
| 8 (Azure SQL MI) |
1, 10, 11, 12 always. Run AG steps 3, 4, 5, 8 only when an MI-link AG exists (ag_count > 0) — a standalone MI's HA is platform-managed and is not exposed via the AG catalog views/DMVs, so empty AG/listener results are expected and must not be reported as FAIL; assess MI HA via Step 10 instead (no cluster/endpoint/backup — msdb.dbo.backupset does not record managed backups) |
Step 1: queries.md, AG steps: queries-sqlserver.md, Steps 10–11: queries-azure.md |
| 12 (Fabric SQL DB) |
1, 10, 11 only |
Step 1: queries.md, Steps 10–11: queries-azure.md |
| 9 (Azure SQL Edge) |
1, 11 only (no AG/cluster — Always On is not supported on SQL Edge) |
Step 1: queries.md, Step 11: queries-azure.md |
| 6 (Synapse Dedicated), 11 (Synapse serverless / Fabric Warehouse) |
Stop — HA assessment not applicable. Report N/A. |
— |
| 1, 4 (Personal/Express) |
Stop — HA not supported. Report FAIL. |
— |
AG steps (2–9, 12–13) require IsHadrEnabled = 1. If HADR is not enabled on Enterprise/Standard, skip AG steps and report as finding.
HA Posture Decision Tree (Engine Edition 2/3 — on-prem or Azure IaaS)
Run the HA Posture Classification query (second query in references/queries.md#step-1-platform-detection-and-ha-capability) and branch on the ha_posture column. The classification uses DMVs only.
- Branch A — No HA enabled (
is_hadr_enabled = 0, or HADR enabled with ag_count = 0): Stop the AG steps (2–9, 12–13). The instance is a single point of failure. Report this as the primary finding and deliver the enablement guidance in references/remediation-guide.md#branch-a--no-ha-enabled.
- Branch B — Basic Availability Group (
basic_ag_count > 0, Standard Edition): Run the applicable AG steps, then evaluate the Basic AG limitations and best practices in references/remediation-guide.md#branch-b--basic-availability-group.
- Branch C — Advanced (full) Availability Group (
advanced_ag_count > 0): Run the full AG steps, then apply the best-practice review in references/remediation-guide.md#branch-c--advanced-full-availability-group.
Azure platforms (Engine Edition 5, 8, 12) have platform-managed HA that cannot be disabled. Skip this decision tree and use Steps 10–11.
Steps 2–6, 8, 9, 12–13: SQL Server AG Assessment
Load and execute queries from references/queries-sqlserver.md for each applicable step per the routing matrix above. Execute the SQL exactly as written — do not invent replacement queries.
Each query includes guard conditions and inline PASS/FAIL/WARNING verdicts via SQL CASE columns.
For each step with FAIL or WARNING verdicts: read the corresponding section in references/remediation-guide.md for remediation guidance:
- Step 2 — Cluster Health
- Step 3 — AG Configuration
- Step 4 — Failover Readiness
- Step 5 — Listener & Connectivity
- Step 6 — Endpoint Security
- Step 8 — Database-Level Configuration
- Step 9 — Backup Configuration
- Step 13 — Distributed & Contained AG
Step 10: Azure Platform HA
Load and execute queries from references/queries-azure.md.
Run the service tier query for all Azure platforms. Then run the geo-replication query (DB only) or failover group query (MI only) as applicable.
If FAIL/WARNING: read references/remediation-guide.md.
Step 11: Connection Resiliency
Load and execute the connection resiliency query from references/queries-azure.md. Applies to all platforms.
If FAIL/WARNING: read references/remediation-guide.md.
Step 12: Overall HA Health Scorecard
For SQL Server (and MI only when an MI-link AG exists): load and execute the scorecard query from references/queries-sqlserver.md.
For Azure SQL DB, Fabric, and standalone MI (platform-managed HA): produce scorecard from Step 10 results only (no scorecard query).
Step 13: Distributed & Contained AG Assessment
Enterprise only (Engine Edition 3), SQL Server 2016+ (v13+). Requires IsHadrEnabled = 1.
Skip if no distributed or contained AGs are detected.
Load and execute the distributed/contained AG query from references/queries-sqlserver.md.
This step detects:
- Distributed AGs — validates listener presence on both underlying AGs, checks forwarder sync lag (
last_hardened_lsn vs last_commit_lsn), and flags availability mode mismatches between underlying AGs.
- Contained AGs (SQL 2022+) — validates system database synchronization within the contained AG.
If FAIL/WARNING: read references/remediation-guide.md.
Report Output
Read references/report-template.md before presenting the final assessment report.
Apply final status:
- Any FAIL: Critical — immediate remediation required. Prioritize: data movement suspension > recovery model > endpoint state > cluster members.
- Only WARNINGs: Functional but has resilience gaps. Schedule remediation during next maintenance window.
- All PASS: Meets best-practice baseline. Document for audit trail; re-assess quarterly or after topology changes.
Gotchas
sys.dm_hadr_* and sys.availability_* DMVs do not exist on Azure SQL DB (5), Fabric SQL DW (11), or Fabric SQL DB (12). Never run AG queries on those platforms.
- On any SQL Server where HADR is not enabled (even Enterprise), AG DMVs return errors. Always check
IsHadrEnabled = 1 first.
- Azure SQL MI monitors auto-failover groups via
sys.dm_geo_replication_link_status — NOT sys.dm_hadr_fabric_replica_states.
- Empty result from listener queries means no listener is configured — a FAIL for production SQL Server AGs (Engine Edition 2/3) only. On Azure SQL MI (8), empty AG/listener results are expected because HA is platform-managed (failover handled by the gateway) and not exposed via
sys.availability_group_listeners — unless an MI link is configured. Never report empty listeners as FAIL on MI; assess MI HA via Step 10 (sys.dm_geo_replication_link_status).
- Connect to the primary replica. Secondaries have limited visibility into AG-wide configuration.
msdb.dbo.backupset history may be truncated or empty on secondaries — backup assessment is most reliable on the primary.
- Backups may be routed to a secondary via
automated_backup_preference (SECONDARY/SECONDARY_ONLY). In that case the primary's msdb.dbo.backupset will not contain those records — Step 9 downgrades a missing full backup to WARNING (not FAIL) when the preference targets a secondary. Verify backups on the preferred backup replica.
- Distributed AGs (
is_distributed = 1) only support manual failover. Do not report missing automatic failover as an issue for distributed AGs.
- Contained AGs (
is_contained = 1, SQL 2022+) replicate master/msdb within the AG. The is_contained column does not exist on SQL Server versions prior to 2022 — guard with version check.
- Distributed AGs require listeners on both underlying AGs. A distributed AG with no listener on the underlying AG will fail to synchronize.
1---2name: ha-assessment-health-check3description: "Use when the user wants a proactive Always On availability group health check before an audit, go-live or change: sync, listeners, failover readiness. For an active incident, use `investigate-ha-issues`."4argument-hint: "[AGName]"5metadata:6 source: https://skillmd.com/skills/microsoft/ha-assessment-health-check7---89# HA Environment Assessment & Health Check1011> Proactive assessment only. Do not change configuration unless the user explicitly asks.12> For active issues, use **investigate-ha-issues** instead.1314## Scope1516- Validates SQL Server Always On AGs (cluster, AG configuration, failover readiness, listeners, endpoints, database settings, backups) and the HA posture of Azure SQL Database, Managed Instance, and Fabric SQL Database, with pass/fail/warning findings.17- For geo-replication, failover groups, geo-secondary planned-failover readiness, or geo-DR RPO/RTO, use the `geo-dr-assessment-health-check` skill.1819## Required tools2021- A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL22 Tools MCP server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`,23 `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides for24 running T-SQL and reading result sets;25 [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the SQL26 Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup` skill,27 which asks before installing or configuring anything. If no SQL tool can be made available, give the28 user the T-SQL to run and ask for the results.29- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that30 holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure31 SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read32 permission these queries need. A tool is only as read-only as the login it connects with.3334## Prerequisites3536- Confirm which instance you are connected to (e.g. `SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME(), SERVERPROPERTY('EngineEdition')`).37- Connect to the **primary replica** for full AG visibility.3839## Parameters4041| Parameter | Default | Description |42|-----------|---------|-------------|43| AGName | *(all AGs)* | Filter to a specific AG. Omit to assess all. |4445## Remediation Safety4647- All assessment queries in this skill are read-only.48- State-changing commands in the references (`ALTER AVAILABILITY GROUP` including `FAILOVER` and49 `FORCE_FAILOVER_ALLOW_DATA_LOSS`, `ALTER DATABASE`, `sp_configure` with `RECONFIGURE`, and50 `CREATE LOGIN`) are **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 and rollback. A failover interrupts every connection to the54 availability group, and `FORCE_FAILOVER_ALLOW_DATA_LOSS` can lose committed transactions.5556## Workflow5758Execute steps in order. Step 1 determines which steps apply per platform.5960> **Note on step numbering:** There is intentionally no Step 7 — the workflow runs 1–6 then 8–13. The numbers are stable identifiers referenced by the routing matrix, report template, and remediation guide, so the gap is preserved deliberately rather than renumbered.6162---6364### Step 1: Platform Detection and HA Capability6566> Run with the SQL query tool.6768> **Query:** See [references/queries.md#step-1-platform-detection-and-ha-capability](references/queries.md#step-1-platform-detection-and-ha-capability)6970**Platform routing — use this matrix for all subsequent steps:**7172| Engine Edition | Steps to Execute | Query Reference |73|----------------|------------------|-----------------|74| 3 (Enterprise) | Steps 1–6, 8, 9, 11, 12, 13 — requires `IsHadrEnabled = 1` for steps 2–6, 8, 9, 12–13 | Step 1: [queries.md](references/queries.md), Steps 2–6, 8, 9, 12–13: [queries-sqlserver.md](references/queries-sqlserver.md), Step 11: [queries-azure.md](references/queries-azure.md) |75| 2 (Standard) | 1, 2, 3, 4, 5, 6, 8, 9, 11, 12 — requires `IsHadrEnabled = 1` for AG steps | Step 1: [queries.md](references/queries.md), Steps 2–6, 8, 9, 12: [queries-sqlserver.md](references/queries-sqlserver.md), Step 11: [queries-azure.md](references/queries-azure.md) |76| 5 (Azure SQL DB) | 1, 10, 11 only | Step 1: [queries.md](references/queries.md), Steps 10–11: [queries-azure.md](references/queries-azure.md) |77| 8 (Azure SQL MI) | 1, 10, 11, 12 always. Run AG steps 3, 4, 5, 8 **only when an MI-link AG exists** (`ag_count > 0`) — a standalone MI's HA is platform-managed and is **not** exposed via the AG catalog views/DMVs, so empty AG/listener results are expected and must **not** be reported as FAIL; assess MI HA via Step 10 instead (no cluster/endpoint/backup — `msdb.dbo.backupset` does not record managed backups) | Step 1: [queries.md](references/queries.md), AG steps: [queries-sqlserver.md](references/queries-sqlserver.md), Steps 10–11: [queries-azure.md](references/queries-azure.md) |78| 12 (Fabric SQL DB) | 1, 10, 11 only | Step 1: [queries.md](references/queries.md), Steps 10–11: [queries-azure.md](references/queries-azure.md) |79| 9 (Azure SQL Edge) | 1, 11 only (no AG/cluster — Always On is not supported on SQL Edge) | Step 1: [queries.md](references/queries.md), Step 11: [queries-azure.md](references/queries-azure.md) |80| 6 (Synapse Dedicated), 11 (Synapse serverless / Fabric Warehouse) | Stop — HA assessment not applicable. Report N/A. | — |81| 1, 4 (Personal/Express) | Stop — HA not supported. Report FAIL. | — |8283> AG steps (2–9, 12–13) require `IsHadrEnabled = 1`. If HADR is not enabled on Enterprise/Standard, skip AG steps and report as finding.8485#### HA Posture Decision Tree (Engine Edition 2/3 — on-prem or Azure IaaS)8687Run the **HA Posture Classification** query (second query in [references/queries.md#step-1-platform-detection-and-ha-capability](references/queries.md#step-1-platform-detection-and-ha-capability)) and branch on the `ha_posture` column. The classification uses DMVs only.8889- **Branch A — No HA enabled** (`is_hadr_enabled = 0`, or HADR enabled with `ag_count = 0`): **Stop the AG steps (2–9, 12–13).** The instance is a single point of failure. Report this as the primary finding and deliver the enablement guidance in [references/remediation-guide.md#branch-a--no-ha-enabled](references/remediation-guide.md#branch-a--no-ha-enabled).90- **Branch B — Basic Availability Group** (`basic_ag_count > 0`, Standard Edition): Run the applicable AG steps, then evaluate the Basic AG limitations and best practices in [references/remediation-guide.md#branch-b--basic-availability-group](references/remediation-guide.md#branch-b--basic-availability-group).91- **Branch C — Advanced (full) Availability Group** (`advanced_ag_count > 0`): Run the full AG steps, then apply the best-practice review in [references/remediation-guide.md#branch-c--advanced-full-availability-group](references/remediation-guide.md#branch-c--advanced-full-availability-group).9293> Azure platforms (Engine Edition 5, 8, 12) have platform-managed HA that cannot be disabled. Skip this decision tree and use Steps 10–11.9495---9697### Steps 2–6, 8, 9, 12–13: SQL Server AG Assessment9899Load and execute queries from [references/queries-sqlserver.md](references/queries-sqlserver.md) for each applicable step per the routing matrix above. Execute the SQL exactly as written — do not invent replacement queries.100101Each query includes guard conditions and inline PASS/FAIL/WARNING verdicts via SQL CASE columns.102103**For each step with FAIL or WARNING verdicts:** read the corresponding section in [references/remediation-guide.md](references/remediation-guide.md) for remediation guidance:104- [Step 2 — Cluster Health](references/remediation-guide.md#step-2--windows-server-failover-cluster-health)105- [Step 3 — AG Configuration](references/remediation-guide.md#step-3--ag-configuration)106- [Step 4 — Failover Readiness](references/remediation-guide.md#step-4--failover-readiness)107- [Step 5 — Listener & Connectivity](references/remediation-guide.md#step-5--listener--connectivity)108- [Step 6 — Endpoint Security](references/remediation-guide.md#step-6--endpoint-security)109- [Step 8 — Database-Level Configuration](references/remediation-guide.md#step-8--database-level-configuration)110- [Step 9 — Backup Configuration](references/remediation-guide.md#step-9--backup-configuration)111- [Step 13 — Distributed & Contained AG](references/remediation-guide.md#step-13--distributed--contained-ag)112113---114115### Step 10: Azure Platform HA116117Load and execute queries from [references/queries-azure.md](references/queries-azure.md#step-10-platform-ha-assessment).118Run the service tier query for all Azure platforms. Then run the geo-replication query (DB only) or failover group query (MI only) as applicable.119120If FAIL/WARNING: read [references/remediation-guide.md](references/remediation-guide.md#step-10--azure-platform-ha).121122---123124### Step 11: Connection Resiliency125126Load and execute the connection resiliency query from [references/queries-azure.md](references/queries-azure.md#step-11-connection-resiliency-all-platforms). Applies to all platforms.127128If FAIL/WARNING: read [references/remediation-guide.md](references/remediation-guide.md#step-11--connection-resiliency).129130---131132### Step 12: Overall HA Health Scorecard133134For SQL Server (and MI **only when an MI-link AG exists**): load and execute the scorecard query from [references/queries-sqlserver.md](references/queries-sqlserver.md#step-12-overall-ha-health-scorecard).135For Azure SQL DB, Fabric, and standalone MI (platform-managed HA): produce scorecard from Step 10 results only (no scorecard query).136137---138139### Step 13: Distributed & Contained AG Assessment140141> Enterprise only (Engine Edition 3), SQL Server 2016+ (v13+). Requires `IsHadrEnabled = 1`.142> Skip if no distributed or contained AGs are detected.143144Load and execute the distributed/contained AG query from [references/queries-sqlserver.md](references/queries-sqlserver.md#step-13-distributed--contained-ag-assessment).145146This step detects:147- **Distributed AGs** — validates listener presence on both underlying AGs, checks forwarder sync lag (`last_hardened_lsn` vs `last_commit_lsn`), and flags availability mode mismatches between underlying AGs.148- **Contained AGs** (SQL 2022+) — validates system database synchronization within the contained AG.149150If FAIL/WARNING: read [references/remediation-guide.md](references/remediation-guide.md#step-13--distributed--contained-ag).151152---153154## Report Output155156Read [references/report-template.md](references/report-template.md) before presenting the final assessment report.157158Apply final status:159- **Any FAIL:** Critical — immediate remediation required. Prioritize: data movement suspension > recovery model > endpoint state > cluster members.160- **Only WARNINGs:** Functional but has resilience gaps. Schedule remediation during next maintenance window.161- **All PASS:** Meets best-practice baseline. Document for audit trail; re-assess quarterly or after topology changes.162163## Gotchas164165- `sys.dm_hadr_*` and `sys.availability_*` DMVs do not exist on Azure SQL DB (5), Fabric SQL DW (11), or Fabric SQL DB (12). Never run AG queries on those platforms.166- On any SQL Server where HADR is not enabled (even Enterprise), AG DMVs return errors. Always check `IsHadrEnabled = 1` first.167- Azure SQL MI monitors auto-failover groups via `sys.dm_geo_replication_link_status` — NOT `sys.dm_hadr_fabric_replica_states`.168- Empty result from listener queries means no listener is configured — a FAIL for production **SQL Server AGs (Engine Edition 2/3)** only. On Azure SQL MI (8), empty AG/listener results are expected because HA is platform-managed (failover handled by the gateway) and not exposed via `sys.availability_group_listeners` — unless an MI link is configured. Never report empty listeners as FAIL on MI; assess MI HA via Step 10 (`sys.dm_geo_replication_link_status`).169- Connect to the **primary** replica. Secondaries have limited visibility into AG-wide configuration.170- `msdb.dbo.backupset` history may be truncated or empty on secondaries — backup assessment is most reliable on the primary.171- Backups may be routed to a secondary via `automated_backup_preference` (`SECONDARY`/`SECONDARY_ONLY`). In that case the primary's `msdb.dbo.backupset` will not contain those records — Step 9 downgrades a missing full backup to WARNING (not FAIL) when the preference targets a secondary. Verify backups on the preferred backup replica.172- **Distributed AGs** (`is_distributed = 1`) only support manual failover. Do not report missing automatic failover as an issue for distributed AGs.173- **Contained AGs** (`is_contained = 1`, SQL 2022+) replicate `master`/`msdb` within the AG. The `is_contained` column does not exist on SQL Server versions prior to 2022 — guard with version check.174- Distributed AGs require listeners on **both** underlying AGs. A distributed AG with no listener on the underlying AG will fail to synchronize.