/sqlag-review

name: sqlag-review
description: 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).
triggers:
  - /sqlag-review
  - /ag-review
  - /ag-config-review
  - /hadr-config
  - /distributed-ag-review
  - /ag-setup-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