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

| 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 | 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 '' -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.availabilityreplicas.sessiontimeout 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 ; — 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.fnhadrbackupispreferred_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 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 10ms RTT.

• Fix: ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'DR-SQL01' WITH (SESSION_TIMEOUT = 30);

Info

###

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: vanterx
• Source: vanterx/mssql-performance-skills
• License: MIT

Install and usage instructions live in the source repository linked above.