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

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

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)

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

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

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

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)

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

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:

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.