# Sqlag Review

> Audits SQL Server Always On Availability Group configuration correctness across all layers — prerequisites, replica design, listener architecture, backup strategy, endpoint security, distributed AG topology, Basic and Contained AG constraints, and application integration readiness. Use this skill when setting up a new AG, reviewing an existing AG design before a DR test, preparing for a failover, or investigating connection failures, listener misconfigurations, backup failures on secondaries, or endpoint certificate expiry. Applies 37 checks (F1–F37) across 7 categories. Trigger for questions about AG prerequisites, session timeout, failure condition level, read-only routing configuration, MultiSubnetFailover, backup preferred replica, distributed AG setup, Basic AG limits, Contained AG, endpoint encryption, or automatic seeding mode during a manual-restore workflow. Companion to /sqlhadr-review (runtime health) and /sqlclusterlog-review (WSFC events).

- Skill: `vanterx/sqlag-review` (Agent Skill, multi-file: 8 files)
- Install (CLI): `npx skillmds@latest add vanterx/sqlag-review`
- Raw SKILL.md: https://api.skillmd.com/api/skills/vanterx/sqlag-review/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: vanterx (https://skillmd.com/u/vanterx)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/vanterx/sqlag-review

---


# SQL Server Always On AG Configuration Review Skill

## Purpose

Audit the configuration and design of one or more SQL Server Always On Availability Groups.
Applies 37 checks (F1–F37) across seven categories:

- **F1–F6** — Prerequisites and instance setup: AlwaysOn feature, database recovery model,
  endpoint state, endpoint encryption, failure condition level, version alignment across replicas
- **F7–F13** — Replica configuration design: synchronous replica count, WAN session timeout,
  health check timeout, backup priority ties, replica join state, database join completeness,
  readable secondary availability
- **F14–F18** — Listener and network design: multi-subnet IP completeness, read-only routing URL,
  routing list on primary, non-default port documentation, MultiSubnetFailover guidance
- **F19–F23** — Backup strategy: automated backup preference, preferred-replica guard function,
  log backup scheduling, compression, and missed offload opportunity
- **F24–F27** — Endpoint security: cross-domain Windows auth, certificate expiry, RC4 algorithm,
  firewall port gaps
- **F28–F33** — Distributed AG and advanced features: listener URL requirement for distributed AGs,
  Basic AG limits, Contained AG auth, synchronous distributed link, cross-database dependencies
- **F34–F37** — Operational monitoring: Extended Events AG session, listener IP conformance, AG database-count scale ceiling, automatic seeding left active during a manual-restore workflow

**Scope distinction:** This skill audits configuration correctness ("is the AG designed right?").
Use `/sqlhadr-review` (H1–H28) for runtime health ("is the AG healthy right now?") and
`/sqlclusterlog-review` (L1–L30) for WSFC cluster log events.

---

## Input

Accept any of:

- **File path** — path to a saved text or CSV file containing the catalog view output
- **Inline paste** — query results pasted directly (tab- or pipe-delimited)
- **Natural language description** — description of the AG topology and any known issues

### Recommended Capture Queries

Run the following on the **primary replica** to collect the required data.

**Query 1 — Instance and AG overview**
```sql
SELECT
    SERVERPROPERTY('IsHadrEnabled')   AS hadr_enabled,
    SERVERPROPERTY('ProductVersion')  AS product_version,
    SERVERPROPERTY('Edition')         AS edition,
    ag.name                           AS ag_name,
    ag.failure_condition_level,
    ag.health_check_timeout,
    ag.automated_backup_preference_desc,
    ag.db_failover,
    ag.basic_features,
    ag.is_contained,
    ag.required_synchronized_secondaries_to_commit
FROM sys.availability_groups ag;
```

**Query 2 — Replica configuration**
```sql
SELECT
    ag.name                               AS ag_name,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ar.session_timeout,
    ar.primary_role_allow_connections_desc,
    ar.secondary_role_allow_connections_desc,
    ar.backup_priority,
    ar.seeding_mode_desc,
    ar.endpoint_url,
    ar.read_only_routing_url
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
ORDER BY ag.name, ar.replica_server_name;
```

**Query 2b — Replica join state** (F11 — from DMV, not catalog view)
```sql
SELECT
    replica_server_name,
    join_state_desc    -- NOT_JOINED | JOINED_STANDALONE | JOINED_FCI
FROM sys.dm_hadr_availability_replica_cluster_states
ORDER BY replica_server_name;
```

**Query 3 — Listener and IP configuration**
```sql
SELECT
    ag.name                         AS ag_name,
    agl.dns_name,
    agl.port,
    agl.is_conformant,              -- F35: 0 = mismatch with cluster resource
    aglip.ip_address,
    aglip.ip_subnet_mask,
    aglip.state_desc                AS ip_state  -- ONLINE | OFFLINE | ONLINE_PENDING | FAILED
FROM sys.availability_groups ag
JOIN sys.availability_group_listeners agl ON ag.group_id = agl.group_id
JOIN sys.availability_group_listener_ip_addresses aglip ON agl.listener_id = aglip.listener_id;
```

**Query 4 — Mirroring endpoint**
```sql
SELECT
    name,
    state_desc,
    role_desc,
    connection_auth_desc,
    is_encryption_enabled,
    encryption_algorithm_desc,
    port
FROM sys.database_mirroring_endpoints;
```

**Query 5 — AG database recovery models**
```sql
SELECT
    adc.ag_database_id,
    db.name                AS database_name,
    db.recovery_model_desc,
    db.is_read_committed_snapshot_on,
    db.state_desc
FROM sys.availability_databases_cluster adc
JOIN sys.databases db ON adc.database_id = db.database_id
ORDER BY db.name;
```

**Query 6 — Endpoint certificates (certificate auth only)**
```sql
SELECT
    name,
    subject,
    expiry_date,
    pvt_key_encryption_type_desc,
    thumbprint
FROM sys.certificates
WHERE pvt_key_encryption_type_desc IS NOT NULL
ORDER BY expiry_date;
```

---

## Thresholds Reference

| Threshold | Value | Used by |
|-----------|-------|---------|
| Failure condition level — too permissive | = 1 → Warning | F5 |
| Failure condition level — too aggressive | = 5 → Warning | F5 |
| Health check timeout — too aggressive | < 15,000 ms → Warning | F9 |
| Synchronous-commit replicas | ≥ 4 total (incl. primary) → Warning | F7 |
| WAN session timeout | < 30 sec on ASYNC replica → Warning | F8 |
| Certificate expiry — near | < 90 days → Warning | F25 |
| Certificate expiry — imminent | < 30 days → Critical | F25 |
| Backup priority tie | All eligible secondaries at 50 with SECONDARY preference → Warning | F10 |
| AG database count | > 100 databases per physical machine (all AGs aggregated) → Warning | F36 |

---

## Category 1 — Prerequisites and Instance Setup (F1–F6)

Evaluate these first. Missing prerequisites prevent AG operation entirely.

### F1 — AlwaysOn Feature Disabled
- **Trigger:** `SERVERPROPERTY('IsHadrEnabled') = 0`
- **Severity:** Critical
- **Fix:** Enable via SQL Server Configuration Manager → SQL Server Services → instance
  Properties → AlwaysOn High Availability tab → check "Enable Always On Availability Groups".
  Restart the SQL Server service after enabling. Or: `Enable-SqlAlwaysOn -ServerInstance
  '<instance>' -Restart` (requires SqlServer PowerShell module).

### F2 — AG Database Not in FULL Recovery Model
- **Trigger:** Any database in `sys.availability_databases_cluster` shows
  `recovery_model_desc != 'FULL'` in `sys.databases`
- **Severity:** Critical
- **Fix:** Switch to FULL recovery and take a full backup before joining the AG:
  `ALTER DATABASE [db] SET RECOVERY FULL;`
  `BACKUP DATABASE [db] TO DISK = N'\\backup\db.bak';`
  Log backups must follow to maintain the log chain required by AG log shipping.

### F3 — Mirroring Endpoint Missing or Not Started
- **Trigger:** No row in `sys.database_mirroring_endpoints` OR `state_desc != 'STARTED'`
- **Severity:** Critical
- **Fix:** Create and start the endpoint (if missing):
  `CREATE ENDPOINT [Hadr_endpoint] STATE = STARTED AS TCP (LISTENER_PORT = 5022)`
  `FOR DATABASE_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES,`
  `AUTHENTICATION = WINDOWS NEGOTIATE);`
  If the endpoint exists but is stopped: `ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;`

### F4 — Endpoint Encryption Disabled or Downgrade-Permitted
- **Trigger:** `sys.database_mirroring_endpoints.is_encryption_enabled = 0` (Critical — encryption
  DISABLED) or `is_encryption_enabled = 1` with `encryption_algorithm_desc` containing `NONE`
  (e.g. `'NONE, AES'` — the SUPPORTED/negotiable mode that permits plaintext if the peer does not
  enforce encryption → Warning), or `encryption_algorithm_desc` containing `RC4` (Warning — weak
  cipher). There is no `encryption_desc` column; the SUPPORTED-vs-REQUIRED state is encoded by
  whether `encryption_algorithm_desc` lists `NONE`.
- **Severity:** Critical for DISABLED; Warning for SUPPORTED/negotiable or RC4
- **Fix:** Enforce encryption on all replicas:
  `ALTER ENDPOINT [Hadr_endpoint] FOR DATABASE_MIRRORING`
  `(ENCRYPTION = REQUIRED ALGORITHM AES);`
  Both endpoints must be changed to REQUIRED before either can fully enforce AES.

### F5 — Failure Condition Level at Extremes
- **Trigger:** `sys.availability_groups.failure_condition_level = 1` (only a complete SQL Server
  service failure or lease expiry triggers automatic failover — resource pressure, spinlocks, and
  write-access violations are ignored) or `= 5` (any qualified failure condition, including
  exhaustion of worker threads and unsolvable deadlocks, triggers failover)
- **Severity:** Warning
- **Fix:** Level 3 is the default — triggers on critical internal errors (orphaned spinlocks,
  write-access violations, excessive dump generation). Level 1 is too permissive (misses
  out-of-memory and scheduler hangs); level 5 risks spurious failovers on transient conditions.
  Most deployments should use level 3:
  `ALTER AVAILABILITY GROUP [ag] SET (FAILURE_CONDITION_LEVEL = 3);`

### F6 — SQL Server Version Mismatch Across Replicas
- **Trigger:** Replicas report different major SQL Server version numbers (e.g., SQL 2019 and
  SQL 2022 coexisting beyond a rolling upgrade window)
- **Severity:** Warning
- **Fix:** Mixed versions are supported only during a rolling upgrade (secondary first, then
  primary). Confirm the upgrade is in progress and complete it within the supported window.
  AG behavior differences between major versions can cause unexpected plan changes and feature
  incompatibilities on the new primary after failover.

---

## Category 2 — Replica Configuration Design (F7–F13)

These checks surface design choices that increase commit latency, risk false disconnections,
or leave databases unjoinable.

### F7 — Excessive Synchronous-Commit Replicas
- **Trigger:** COUNT of replicas with `availability_mode_desc = 'SYNCHRONOUS_COMMIT'`
  (including the primary) ≥ 4
- **Severity:** Warning
- **Fix:** Every synchronous secondary must acknowledge each commit before the primary
  returns to the application. Adding more than 2–3 synchronous secondaries multiplies commit
  latency proportionally. Demote DR-site or reporting replicas to ASYNCHRONOUS_COMMIT:
  `ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'server'`
  `WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);`

### F8 — WAN Async Replica Session Timeout Too Low
- **Trigger:** `sys.availability_replicas.session_timeout < 30` on any replica with
  `availability_mode_desc = 'ASYNCHRONOUS_COMMIT'`
- **Severity:** Warning
- **Fix:** The default session timeout (10 seconds) is designed for LAN. WAN replicas with
  > 10 ms round-trip time can trigger spurious DISCONNECTED state and health alerts. Increase
  the timeout: `ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'server'`
  `WITH (SESSION_TIMEOUT = 30);` — minimum 5 seconds; 30–60 seconds for WAN.

### F9 — Health Check Timeout Too Aggressive
- **Trigger:** `sys.availability_groups.health_check_timeout < 15000` (ms)
- **Severity:** Warning
- **Fix:** `sp_server_diagnostics` is called every `health_check_timeout / 3` seconds. A value
  below 15,000 ms calls it more than every 5 seconds, adding unnecessary overhead and risking
  false-positive failovers. The default is 30,000 ms. Increase for WAN topologies:
  `ALTER AVAILABILITY GROUP [ag] SET (HEALTH_CHECK_TIMEOUT = 30000);`

### F10 — Backup Priority Ties Make Secondary Selection Non-Deterministic
- **Trigger:** `sys.availability_groups.automated_backup_preference_desc IN
  ('SECONDARY', 'SECONDARY_ONLY')` AND all eligible secondary replicas have
  `backup_priority = 50` (the default)
- **Severity:** Warning
- **Fix:** When backup preference is SECONDARY or SECONDARY_ONLY and all secondaries have
  equal priority, `sys.fn_hadr_backup_is_preferred_replica()` may select different replicas
  across runs, causing log chain fragmentation. Set distinct priorities:
  `ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'server'`
  `WITH (BACKUP_PRIORITY = 70);` — higher value = more preferred (0–100).

### F11 — Replica Join State Incomplete
- **Trigger:** Any row in `sys.dm_hadr_availability_replica_cluster_states` has `join_state_desc
  = 'NOT_JOINED'` (valid joined values are `JOINED_STANDALONE` for standalone instances and
  `JOINED_FCI` for failover cluster instances)
- **Severity:** Warning
- **Fix:** The replica was added to the AG definition but the secondary has not yet joined.
  On the secondary instance: `ALTER AVAILABILITY GROUP [ag] JOIN;`
  Then restore databases with NORECOVERY and join each:
  `ALTER DATABASE [db] SET HADR AVAILABILITY GROUP = [ag];`

### F12 — AG Databases Missing from Secondary After Replica Join
- **Trigger:** On the secondary replica, `sys.dm_hadr_database_replica_cluster_states` shows
  `is_database_joined = 0` for one or more AG databases (run locally on the secondary). Do **not**
  compare `sys.availability_databases_cluster` counts between replicas — that view returns the same
  cluster-wide list on every replica and cannot detect a database that is unjoined on one replica.
- **Severity:** Warning
- **Fix:** Databases were not restored and joined after the replica joined. For each missing
  database: restore with `RESTORE DATABASE [db] FROM DISK = '...' WITH NORECOVERY, REPLACE;`
  then join: `ALTER DATABASE [db] SET HADR AVAILABILITY GROUP = [ag];`
  Or use automatic seeding if `seeding_mode_desc = 'AUTOMATIC'` is set.

### F13 — No Readable Secondary Configured
- **Trigger:** All replicas have `secondary_role_allow_connections_desc = 'NO'`
- **Severity:** Info
- **Fix:** If read offloading is desired, configure a readable secondary:
  `ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'server'`
  `WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));`
  Enable RCSI on the primary to prevent redo blocking by readers:
  `ALTER DATABASE [db] SET READ_COMMITTED_SNAPSHOT ON;`

---

## Category 3 — Listener and Network Design (F14–F18)

### F14 — Multi-Subnet Listener Missing IP for a Subnet
- **Trigger:** Replica `endpoint_url` values contain IP addresses on distinct subnets (different
  network prefixes) AND `sys.availability_group_listener_ip_addresses` has fewer IP rows than
  distinct subnets represented by the replicas
- **Severity:** Warning
- **Fix:** A VNN listener requires one static IP per subnet for seamless failover across subnets.
  Add the missing IP: `ALTER AVAILABILITY GROUP [ag] MODIFY LISTENER N'listener'`
  `(ADD IP (N'10.1.2.10', N'255.255.255.0'));`
  Also ensure application connection strings include `MultiSubnetFailover=True`.

### F15 — Read-Only Routing URL Absent on Readable Secondary
- **Trigger:** `secondary_role_allow_connections_desc IN ('READ_ONLY', 'ALL')` AND
  `read_only_routing_url IS NULL` on that replica
- **Severity:** Warning
- **Note:** This is the canonical check for this condition. `sqlhadr-review` H21 covered the
  identical condition and is retired in favor of this check.
- **Fix:** Without a routing URL, read-intent connections to the listener will not be
  redirected to this secondary even if a routing list is configured on the primary:
  `ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'secondary'`
  `WITH (SECONDARY_ROLE`
  `(READ_ONLY_ROUTING_URL = N'TCP://secondary.domain.com:1433'));`
  Use the FQDN and the SQL Server port (1433 or custom), not the endpoint port (5022).

### F16 — Read-Only Routing List Not Set on Primary Replica
- **Trigger:** Readable secondaries exist (F15 precondition met on at least one replica) AND
  no replica has a `READ_ONLY_ROUTING_LIST` configured (detectable when
  `read_only_routing_url` is set on secondaries but no routing list row appears)
- **Severity:** Warning
- **Fix:** `ApplicationIntent=ReadOnly` connections via the listener will land on the primary
  unless the primary's routing list redirects them. Configure on each replica (when acting as
  primary):
  `ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'primary'`
  `WITH (PRIMARY_ROLE`
  `(READ_ONLY_ROUTING_LIST = ('secondary1', 'secondary2')));`

### F17 — Listener on Non-Default Port
- **Trigger:** `sys.availability_group_listeners.port != 1433`
- **Severity:** Info
- **Fix:** Connection strings must explicitly include the custom port:
  `Server=listener-name,<port>;` — verify that all application configurations and ODBC DSNs
  include the port. Confirm firewalls and load balancers allow the custom port from all
  application server subnets.

### F18 — Multi-Subnet OFFLINE Listener IP Without MultiSubnetFailover Guidance
- **Trigger:** `sys.availability_group_listener_ip_addresses.state_desc = 'OFFLINE'` on one
  or more listener IP rows (indicating a standby-subnet IP in a multi-subnet VNN listener)
- **Severity:** Info
- **Fix:** In a multi-subnet VNN listener, only the active subnet's IP is ONLINE at any time;
  IPs on other subnets show OFFLINE. This is normal. Ensure all application connection strings
  include `MultiSubnetFailover=True` so that the driver attempts all IPs simultaneously during
  failover, reducing failover detection time from minutes to seconds.

---

## Category 4 — Backup Strategy (F19–F23)

### F19 — Automated Backup Preference Set to NONE
- **Trigger:** `sys.availability_groups.automated_backup_preference_desc = 'NONE'`
- **Severity:** Warning
- **Fix:** With preference NONE, `sys.fn_hadr_backup_is_preferred_replica()` always returns 1
  on every replica, making every replica a backup candidate simultaneously. This causes
  duplicate backups and log chain conflicts unless backup jobs explicitly coordinate target
  replicas. Set to SECONDARY_ONLY or SECONDARY to enable automatic preferred-replica
  arbitration: `ALTER AVAILABILITY GROUP [ag]`
  `SET (AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY);`

### F20 — Backup Jobs Not Guarded by sys.fn_hadr_backup_is_preferred_replica
- **Trigger:** `automated_backup_preference_desc IN ('SECONDARY', 'SECONDARY_ONLY')` AND
  backup job step content (from description or `msdb.dbo.sysjobsteps`) contains no reference
  to `sys.fn_hadr_backup_is_preferred_replica`
- **Severity:** Warning
- **Fix:** Without the guard function, backup jobs on every replica run simultaneously,
  creating parallel log chains and wasting I/O. Wrap all backup logic:
  `IF sys.fn_hadr_backup_is_preferred_replica(DB_NAME()) = 1`
  `BEGIN BACKUP DATABASE [db] TO DISK = N'...' WITH COMPRESSION, STATS = 10; END`

### F21 — Log Backups Not Scheduled for AG Databases in FULL Recovery
- **Trigger:** AG databases in FULL recovery model (F2 not fired) with no log backup jobs
  identified in `msdb.dbo.sysjobsteps` or confirmed via description
- **Severity:** Warning
- **Fix:** Log backups must run on whichever replica `sys.fn_hadr_backup_is_preferred_replica()`
  selects. Without log backups, the transaction log grows unbounded on the primary. Schedule
  log backups every 15–60 minutes depending on RPO requirements. The log chain is maintained
  regardless of which replica runs the backup.

### F22 — Backup Compression Disabled on Secondary Backup Host
- **Trigger:** Secondary replica is the designated backup host (SECONDARY or SECONDARY_ONLY
  preference, highest backup_priority) AND `sp_configure 'backup compression default' = 0`
  on that instance
- **Severity:** Info
- **Fix:** Backup compression reduces backup file size and I/O at the cost of modest CPU usage
  on the secondary. Enable on the secondary:
  `EXEC sp_configure 'backup compression default', 1; RECONFIGURE;`
  CPU impact on a secondary does not affect primary commit latency.

### F23 — PRIMARY Preference with 3 or More Replicas
- **Trigger:** `automated_backup_preference_desc = 'PRIMARY'` AND 3 or more replicas
  are configured
- **Severity:** Info
- **Fix:** With 3+ replicas, PRIMARY preference concentrates all backup I/O on the primary,
  competing with production workloads. Consider SECONDARY_ONLY to offload backup I/O:
  `ALTER AVAILABILITY GROUP [ag]`
  `SET (AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY);`
  Set `backup_priority` values to control which secondary is preferred.

---

## Category 5 — Endpoint Security (F24–F27)

### F24 — Windows Authentication on Endpoint in Cross-Domain or Workgroup Scenario
- **Trigger:** `sys.database_mirroring_endpoints.connection_auth_desc` contains `WINDOWS`
  AND replica `endpoint_url` values suggest replicas in different DNS domains or workgroup
  (no common domain suffix)
- **Severity:** Warning
- **Fix:** Windows (Kerberos/NTLM) authentication requires trust between domains. For
  workgroup, cross-domain, or cloud-hybrid scenarios, use certificate-based authentication:
  `ALTER ENDPOINT [Hadr_endpoint] FOR DATABASE_MIRRORING`
  `(AUTHENTICATION = CERTIFICATE [hadr_cert]);`
  Create and exchange certificates between replicas before altering the endpoint.

### F25 — Endpoint Certificate Expiring Within 90 Days
- **Trigger:** Certificate used for mirroring endpoint authentication has
  `expiry_date < DATEADD(day, 90, GETDATE())` in `sys.certificates`
- **Severity:** Warning if < 90 days; Critical if < 30 days
- **Fix:** An expired endpoint certificate causes replica authentication failures and
  disconnections. Create a replacement certificate before expiry, share the public key
  with all partner replicas, update the remote login mapping, and alter the endpoint to
  use the new certificate. Do not wait until expiry — rolling certificate changes while
  the AG is healthy is far safer than emergency rotation after disconnection.

### F26 — Endpoint Using RC4 Encryption Algorithm
- **Trigger:** `sys.database_mirroring_endpoints.encryption_algorithm_desc = 'RC4'`
- **Severity:** Critical
- **Fix:** RC4 is cryptographically broken; NIST deprecated it in 2013. SQL Server 2016+
  disables RC4 by default. Rotate to AES immediately:
  `ALTER ENDPOINT [Hadr_endpoint] FOR DATABASE_MIRRORING`
  `(ENCRYPTION = REQUIRED ALGORITHM AES);`
  This must be changed on all replicas. A brief endpoint restart may be required.

### F27 — Endpoint Port Documented as Blocked
- **Trigger:** User description mentions that the endpoint port (default 5022, or the port
  shown in `sys.database_mirroring_endpoints`) is blocked by a firewall, network ACL, or
  security group
- **Severity:** Warning
- **Fix:** AG replicas communicate exclusively through the database mirroring endpoint port.
  A blocked port causes replica disconnection. Open the endpoint port (TCP, inbound and
  outbound) between all replica nodes in firewall rules, Windows Firewall, NSG rules
  (Azure), and security groups (AWS). Also open the listener port (default 1433) for
  application traffic.

---

## Category 6 — Distributed AG and Advanced Features (F28–F33)

### F28 — Distributed AG Using Instance Name Instead of Listener URL
- **Trigger:** Distributed AG `AVAILABILITY GROUP ON` clause in the creation script or
  description references a SQL Server instance name (e.g., `TCP://SERVER01:5022`) rather
  than a listener DNS name (e.g., `TCP://ag-listener.domain.com:5022`)
- **Severity:** Warning
- **Fix:** Distributed AGs must reference the local AG's listener, not an instance name.
  If the primary within a local AG fails over to another replica, the distributed AG link
  breaks if it points to the old primary's instance name. Recreate the distributed AG
  referencing the listener: `CREATE AVAILABILITY GROUP [DistributedAG] WITH (DISTRIBUTED)`
  `AVAILABILITY GROUP ON 'LocalAG' WITH`
  `(LISTENER_URL = N'TCP://local-listener.domain.com:5022', ...);`

### F29 — Basic AG with More Than One Database
- **Trigger:** `sys.availability_groups.basic_features = 1` AND COUNT of databases in
  `sys.availability_databases_cluster` for this AG > 1
- **Severity:** Warning
- **Fix:** Basic Availability Groups (SQL Server 2016+ Standard Edition) support only one
  database per AG. Additional databases must be added to separate AGs. Alternatively,
  upgrade to Enterprise Edition to remove this restriction.

### F30 — Basic AG Configured with Readable Secondary
- **Trigger:** `sys.availability_groups.basic_features = 1` AND any replica shows
  `secondary_role_allow_connections_desc != 'NO'`
- **Severity:** Warning
- **Fix:** Basic AGs do not support readable secondaries — read connections to a Basic AG
  secondary will be rejected regardless of the `secondary_role_allow_connections` setting.
  Do not route `ApplicationIntent=ReadOnly` connections to a Basic AG listener.

### F31 — Contained AG Using Windows Endpoint Authentication
- **Trigger:** `sys.availability_groups.is_contained = 1` AND
  `sys.database_mirroring_endpoints.connection_auth_desc` contains `WINDOWS` — SQL Server
  2022+ only; skip if SQL version < 2022
- **Severity:** Warning
- **Fix:** Contained AGs are designed for domain-independent deployments (containers,
  Kubernetes, workgroups). Using Windows authentication for the endpoint reintroduces an
  Active Directory dependency that negates the containment benefit. Switch to
  certificate-based endpoint authentication as described in F24.

### F32 — Distributed AG Left in Synchronous Commit as Permanent Configuration
- **Trigger:** Distributed AG shows `AVAILABILITY_MODE = SYNCHRONOUS_COMMIT` on the
  inter-AG link AND no recent failover or migration activity is evident — SQL Server 2016+
  only; skip if SQL version < 2016
- **Severity:** Info
- **Fix:** SYNCHRONOUS_COMMIT on a distributed AG is valid and is used deliberately during
  planned zero-data-loss failovers (as documented by Microsoft). However, leaving it as the
  permanent steady-state configuration adds commit latency proportional to the inter-site
  round-trip time. After a planned failover completes, revert to asynchronous for normal DR:
  `ALTER AVAILABILITY GROUP [DistributedAG]`
  `MODIFY AVAILABILITY GROUP ON 'SecondaryAG'`
  `WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);`
  Use `SYNCHRONOUS_COMMIT` only during the planned failover procedure window.

### F33 — AG Databases With Cross-Database Dependencies on Non-AG Databases
- **Trigger:** User description or T-SQL code references databases outside this AG via
  three-part names, linked servers, or `USE [other_db]` — and those databases are not
  members of the same AG
- **Severity:** Info
- **Fix:** After a failover, the AG databases promote to the new primary but non-AG databases
  remain on the old primary (or on a separate instance). Cross-database queries, linked
  server calls, and three-part names to non-AG databases will fail immediately after
  failover. Options: (1) include dependent databases in the same AG; (2) use read-only
  replicas with linked servers pointing to the listener; (3) refactor to eliminate
  cross-database dependencies at the AG boundary.

---

## Category 7 — Operational Monitoring (F34–F37)

### F34 — No Extended Events Session for AG Diagnostics
- **Trigger:** `sys.dm_xe_sessions` contains no session with events targeting
  `alwayson_*` event channels or AG-specific events such as
  `availability_group_lease_expired` or `availability_replica_state_change`
- **Severity:** Info
- **Fix:** Create a lightweight always-on XE session to capture AG state changes:
  `CREATE EVENT SESSION [AG_Diagnostics] ON SERVER`
  `ADD EVENT sqlserver.availability_group_lease_expired,`
  `ADD EVENT sqlserver.availability_replica_state_change,`
  `ADD EVENT sqlserver.hadr_log_block_send_complete`
  `ADD TARGET package0.ring_buffer (SET max_memory = 51200)`
  `WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);`
  `ALTER EVENT SESSION [AG_Diagnostics] ON SERVER STATE = START;`

### F35 — Listener IP Configuration Not Conformant with Windows Cluster
- **Trigger:** `sys.availability_group_listeners.is_conformant = 0` for any listener row
- **Severity:** Warning
- **Fix:** A non-conformant IP indicates a mismatch between the SQL Server AG listener
  metadata and the Windows Server Failover Cluster IP resource. This can prevent automatic
  IP activation during failover. Resolve by dropping and recreating the listener IP in
  alignment with the cluster resource configuration, or repair the cluster resource via
  Failover Cluster Manager to match the SQL Server listener definition.

### F36 — AG Database Count Exceeds Microsoft's Tested Scale Ceiling
- **Trigger:** COUNT of availability databases across **all** AGs on the physical machine
  (aggregate, not per-AG) exceeds 100, or the AG count on the machine exceeds 10 — Microsoft's
  tested scale ceiling is expressed per physical machine, not per AG
- **Severity:** Warning
- **Fix:** Microsoft has tested up to 10 availability groups and 100 availability databases
  per physical machine; this is not an enforced limit, but going meaningfully beyond it is
  untested territory. Signs of an overloaded instance include worker thread exhaustion, slow
  responses from AG system views/DMVs, and stalled dispatcher dumps. Before going live with a
  large multi-hundred-database AG: load-test with a production-like workload under failure
  conditions (not just steady-state), monitor `sys.dm_os_wait_stats` for `HADR_*` and
  `DBMIRROR_*` waits, and consider splitting the workload across multiple AGs on the same
  replicas (a single instance can host many AGs) if thread exhaustion or DMV latency appears
  under test.

### F37 — Automatic Seeding Left Enabled During a Manual-Restore Workflow
- **Trigger:** `seeding_mode_desc = AUTOMATIC` on one or more replicas (`sys.availability_replicas`)
  AND the described or planned database-onboarding process is manual backup/restore (e.g.,
  "restore WITH NORECOVERY then ADD DATABASE", a migration runbook, or a `GRANT CREATE ANY
  DATABASE` step performed for an unrelated reason) rather than direct seeding over the network
- **Severity:** Warning
- **Fix:** `SEEDING_MODE` is evaluated dynamically at the moment a database is added to or
  discovered by the AG, not fixed once at AG creation. If a replica is left at `AUTOMATIC`
  while databases are being restored manually, both onboarding paths target the same secondary
  database at once. **Note:** Microsoft Learn does **not** document this as causing a "hybrid or
  corrupt restore chain" — combining seeding methods is supported, and the documented outcome is
  that one path conflicts with the other (for example, an automatic seed aborts because the
  database already exists from the manual restore, or the manual restore is redundant), producing
  failed-seed errors and an ambiguous onboarding state rather than corruption. The practical risk
  is operational confusion and a database that doesn't reach `SYNCHRONIZED` cleanly. To avoid it,
  pick one method: before any manual-restore operation, explicitly set every
  target replica to manual seeding: `ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON
  N'server' WITH (SEEDING_MODE = MANUAL)`. Confirm with `SELECT replica_server_name,
  seeding_mode_desc FROM sys.availability_replicas`. If `GRANT CREATE ANY DATABASE` was issued
  for any replica, reissue `ALTER AVAILABILITY GROUP [ag] DENY CREATE ANY DATABASE` on replicas
  that should not auto-create databases, and check `sys.dm_hadr_automatic_seeding` for any
  seeding operation that started without being requested.

---

## Version-Aware Check Suppression

If the SQL Server version is provided, read `VERSION_COMPATIBILITY.md`. For checks that
require a minimum version above the instance version: verbose mode → log as
`SKIP (version: requires SQL 20XX+, instance is SQL 20YY)`; standard report → omit
entirely. Version-gated checks: F31 (SQL 2022+), F32 (SQL 2016+).

---

## Output Format

```
## AG Configuration Analysis

### Summary
- X Critical, Y Warnings, Z Info
- Availability group: [ag_name] | Replicas: N | Version: [product_version] | Edition: [edition]
- Highest-risk finding: [check name and ID]

### Critical Issues

### [C1 — F3] Mirroring Endpoint Not Started — PROD-SQL02
- **Observed:** sys.database_mirroring_endpoints.state_desc = 'STOPPED' on PROD-SQL02
- **Impact:** Replica cannot receive or send AG log stream; AG is broken until endpoint restarts.
- **Fix:** ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

### Warnings

### [W1 — F8] WAN Async Replica Session Timeout Too Low — DR-SQL01
- **Observed:** session_timeout = 10 on ASYNC replica DR-SQL01
- **Impact:** 10-second timeout causes false DISCONNECTED state on WAN links > 10ms RTT.
- **Fix:** ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'DR-SQL01' WITH (SESSION_TIMEOUT = 30);

### Info

### [I1 — F13] No Readable Secondary Configured
- **Observed:** All secondary replicas have secondary_role_allow_connections_desc = 'NO'
- **Impact:** Reporting and read-scale workloads cannot be offloaded to secondaries.
- **Fix:** Configure ALLOW_CONNECTIONS = READ_ONLY and enable RCSI on the primary database.

### Passed Checks

| Check | Result |
|-------|--------|
| F1 — AlwaysOn Feature Disabled | PASS — IsHadrEnabled = 1 |
| F2 — AG Database Not in FULL Recovery | PASS — all AG databases in FULL recovery |
```

Include a **Prioritized Action Order** table after all findings:

```
### Prioritized Action Order

| Priority | Action | Resolves | Effort |
|----------|--------|----------|--------|
| 1 — Immediately | Start mirroring endpoint on PROD-SQL02 | C1 | 2 min |
| 2 — Today | Increase session_timeout on DR-SQL01 to 30 seconds | W1 | 5 min |
| 3 — This sprint | Configure readable secondary and RCSI | I1 | 30 min |

---
*Analyzed by: [AI model and version] · [date and time, local timezone or UTC]*
```

---

## Output Filters

**`--brief`** — Omit the Passed Checks table and attribution footer. Output Summary, Findings,
and Prioritized Action Order only.

**`--critical-only`** — Suppress Warning and Info findings. Omit Passed Checks table.
Use during incidents when only blocking issues matter.

Both flags can be combined: `--brief --critical-only` produces Summary and Critical findings only.

---

## Verbose Output (--verbose)

When the request includes `--verbose`, `--trace`, or the word `verbose`:

Append a `## Check Evaluation Log` section after the Passed Checks table:

| Check | Evidence | Threshold | Result |
|-------|----------|-----------|--------|
| [ID — Name] | [key attribute(s) or "absent"] | [threshold or condition] | PASS / **FIRE → severity** / NOT ASSESSED |

Save both files to the working directory:
```
output/sqlag-review/<YYYY-MM-DD-HHmmss>-<input-prefix>/analysis.md
output/sqlag-review/<YYYY-MM-DD-HHmmss>-<input-prefix>/trace.md
```

Derive `<input-prefix>` from the AG name, filename stem, or `run` as fallback.

---

## Notes

- When only natural language input is provided, state which catalog view data is missing and
  apply only the checks that can be evaluated from the described values.
- F28–F33 require description or T-SQL context that may not be available from DMV output
  alone; apply only when relevant information is present.
- Do not invent findings not triggered by the rules above.
- F20 and F21 require visibility into `msdb.dbo.sysjobsteps`; if not provided, note as
  NOT ASSESSED and recommend manual review.

---

## Companion Skills

- `/sqlhadr-review` — Runtime health of the AG (disconnected replicas, lag, queue sizes,
  sync state). Run alongside this skill to get both configuration correctness and current
  health state in one analysis session.
- `/sqlclusterlog-review` — WSFC cluster log analysis (lease timeouts, quorum loss, node
  eviction, AG resource transitions). Essential after a failover event.
- `/sqlerrorlog-review` — SQL Server ERRORLOG analysis including AG failover events, lease
  expiry messages, and secondary lag signals.
- `/sqlspn-review` — SPN and Kerberos delegation analysis for AG listeners. Run when
  Kerberos authentication fails through the listener (double-hop, constrained delegation).
- `/sqlencryption-review` — Full encryption posture audit including endpoint certificates,
  TDE on AG databases, and backup encryption.
- `/sqlmigration-review` — Dispatches AG-as-migration-mechanism findings here (edition limits
  on Basic/Contained AG, distributed AG topology gaps) when AG seeding is the chosen migration
  mechanism.

- **mssql-performance-review** — Orchestrator that routes mixed artifacts to multiple
  specialised skills (this one included). Use when you have several artifact types together
  or describe a symptom without knowing which skill to run.