SQL Server Always On AG Scripts Library: Production DBA Toolkit

---

These scripts come from years of managing Always On Availability Groups across multiple production environments — different topologies, different versions, different problems. They have been cleaned, corrected where needed, and annotated so you understand not just what each script does but why it is written the way it is and where it can go wrong.

Every server name, instance name, AG name, database name, IP address, and domain has been replaced with generic placeholders. Replace them with your own environment values before running anything. Placeholders follow this convention throughout: YourAG, PROD-DB1, PROD-DB2, RPT-DB1, YourDatabase, YourDomain.com.

1 AG Replica Configuration Snapshot

Quick one-line view of every replica’s configuration: commit mode, failover mode, whether reads are allowed on the secondary, backup priority, and read-only routing URL. Run this first when verifying a new AG setup or after any configuration change.

SELECT
    replica_server_name,
    endpoint_url,
    availability_mode_desc              AS [Commit Mode],
    failover_mode_desc                  AS [Failover Mode],
    secondary_role_allow_connections_desc AS [Secondary Reads],
    backup_priority,
    read_only_routing_url,
    seeding_mode_desc,                  -- SQL Server 2016+ only; remove for 2014
    create_date
FROM sys.availability_replicas
ORDER BY replica_server_name;

2 Database Replica State — Full LSN Detail

The most detailed single-DMV view of synchronization state. Shows send queue, redo queue, send rate, redo rate, all LSN watermarks, and lag. Use this when investigating redo lag, a growing send queue, or a replica falling behind during maintenance. The secondary_lag_seconds column gives you the estimated data staleness in seconds for each secondary — invaluable for read-only routing troubleshooting.

SELECT
    DB_NAME(database_id)            AS [Database],
    is_primary_replica,
    synchronization_state_desc,
    database_state_desc,
    is_suspended,
    suspend_reason_desc,
    -- LSN watermarks -- useful for debugging synchronization gaps
    recovery_lsn,
    truncation_lsn,
    last_sent_lsn,
    last_sent_time,
    last_received_lsn,
    last_received_time,
    last_hardened_lsn,
    -- Queue sizes and rates -- your redo lag indicators
    log_send_queue_size             AS [Send Queue (KB)],
    log_send_rate                   AS [Send Rate (KB/s)],
    redo_queue_size                 AS [Redo Queue (KB)],
    redo_rate                       AS [Redo Rate (KB/s)],
    end_of_log_lsn,
    last_commit_lsn,
    last_commit_time,
    secondary_lag_seconds           -- SQL Server 2016+ only
FROM sys.dm_hadr_database_replica_states
ORDER BY is_primary_replica DESC, DB_NAME(database_id);

3 Complete AG Health View — All Replicas and Databases

The most useful of the three status queries. Joins four system objects to give you the AG name, database name, replica server name, whether it is primary or secondary, synchronization state and health, all LSN watermarks, and queue metrics in one result set. This is the query to run when you open a support ticket or sit down to investigate an AG health alert — it tells the complete story in one output.

SELECT
    ar.replica_server_name,
    adc.database_name,
    ag.name                             AS [AG Name],
    CASE HDRS.is_primary_replica
        WHEN 1 THEN 'Primary Replica'
        ELSE 'Secondary Replica'
    END                                 AS [Role],
    HDRS.synchronization_state_desc,
    HDRS.synchronization_health_desc,
    -- LSN watermarks
    HDRS.recovery_lsn,
    HDRS.truncation_lsn,
    HDRS.last_sent_lsn,
    HDRS.last_sent_time,
    HDRS.last_received_lsn,
    HDRS.last_received_time,
    HDRS.last_hardened_lsn,
    HDRS.last_hardened_time,
    HDRS.last_redone_lsn,
    HDRS.last_redone_time,
    -- Queue and rate metrics
    HDRS.log_send_queue_size            AS [Send Queue (KB)],
    HDRS.log_send_rate                  AS [Send Rate (KB/s)],
    HDRS.redo_queue_size                AS [Redo Queue (KB)],
    HDRS.redo_rate                      AS [Redo Rate (KB/s)],
    HDRS.filestream_send_rate,
    HDRS.end_of_log_lsn,
    HDRS.last_commit_lsn,
    HDRS.last_commit_time
FROM sys.dm_hadr_database_replica_states    AS HDRS
INNER JOIN sys.availability_databases_cluster AS adc
    ON  HDRS.group_id          = adc.group_id
    AND HDRS.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = HDRS.group_id
INNER JOIN sys.availability_replicas AS ar
    ON  HDRS.group_id   = ar.group_id
    AND HDRS.replica_id = ar.replica_id
ORDER BY ag.name, HDRS.is_primary_replica DESC, ar.replica_server_name;

4 Cluster State Check for a Specific Replica

Queries the WSFC cluster-level state for a specific replica. Useful when a replica shows as connected in the AG dashboard but the cluster is showing it differently, or when troubleshooting a replica that keeps oscillating between connected and disconnected states. Replace 'PROD-DB2' with the replica server name you want to inspect.

SELECT *
FROM sys.dm_hadr_database_replica_cluster_states
WHERE replica_id = (
    SELECT replica_id
    FROM sys.availability_replicas
    WHERE replica_server_name = 'PROD-DB2'  -- replace with your replica name
);

5 Listener Configuration and IP Snapshot

Two complementary queries. The first shows every listener’s DNS name and the raw IP configuration string from the cluster — useful for confirming which IPs are registered and whether RegisterAllProvidersIP is causing both IPs to show in DNS. The second joins listener to replica to show the routing URL and secondary role connection setting per replica, all in one row per replica.

-- Query 1: Listener DNS name and cluster IP configuration
SELECT
    ag.name                                 AS [AG Name],
    agl.dns_name                            AS [Listener DNS],
    agl.port,
    agl.ip_configuration_string_from_cluster AS [Cluster IP Config]
FROM sys.availability_group_listeners   agl
INNER JOIN sys.availability_groups      ag  ON ag.group_id = agl.group_id
ORDER BY ag.name;

-- Query 2: Listener + replica routing URL and secondary access setting
-- One row per replica showing its listener context
SELECT
    agl.dns_name                            AS [Listener DNS],
    ar.replica_server_name,
    ar.read_only_routing_url,
    ar.secondary_role_allow_connections_desc AS [Secondary Reads Allowed]
FROM sys.availability_replicas              ar
INNER JOIN sys.availability_group_listeners agl
    ON agl.group_id = ar.group_id
ORDER BY agl.dns_name, ar.replica_server_name;

6 Routing List Verification — Simple

The fastest way to confirm read-only routing is configured. Shows for each primary replica who reads get routed to and in what priority order. Run this immediately after configuring routing to confirm the list is correct — it is easier to catch a wrong priority order here than by testing connections.

-- Simple routing list: when this server is primary, reads route to...
SELECT DISTINCT
    ar.replica_server_name          AS [When Primary],
    rl.routing_priority             AS [Priority],
    ar2.replica_server_name         AS [Reads Route To]
FROM sys.availability_read_only_routing_lists   rl
JOIN sys.availability_replicas                  ar
    ON rl.replica_id          = ar.replica_id
JOIN sys.availability_replicas                  ar2
    ON ar2.replica_id         = rl.read_only_replica_id
ORDER BY ar.replica_server_name, rl.routing_priority;

7 Routing List Verification — Full Detail with Listener

The complete routing picture in one query. Includes the AG name, the “when primary” replica, routing priority, the destination replica for reads, whether secondary reads are allowed on the source, and the actual routing URL that the redirect uses. This is the definitive verification query — if read-only routing is not working, run this first and confirm every column looks correct before troubleshooting the connection string.

SELECT
    ag.name                                 AS [AG Name],
    ar.replica_server_name                  AS [When Primary Replica Is],
    rl.routing_priority                     AS [Routing Priority],
    ar2.replica_server_name                 AS [RO Routed To],
    ar.secondary_role_allow_connections_desc AS [Source Secondary Reads],
    ar2.read_only_routing_url               AS [Routing URL Used]
FROM sys.availability_read_only_routing_lists   rl
INNER JOIN sys.availability_replicas            ar
    ON  rl.replica_id          = ar.replica_id
INNER JOIN sys.availability_replicas            ar2
    ON  rl.read_only_replica_id = ar2.replica_id
INNER JOIN sys.availability_groups              ag
    ON  ar.group_id            = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority;

8 Replica Endpoint, Routing URL, and Sync Health Combined

Joins replica configuration to live state in one query — endpoint URL, routing URL, secondary connection setting, and current synchronization health side by side. Useful when verifying a new routing configuration and wanting to confirm the replica is also currently healthy, not just correctly configured.

SELECT
    ar.replica_server_name,
    ar.endpoint_url,
    ar.read_only_routing_url,
    ar.secondary_role_allow_connections_desc AS [Secondary Reads],
    ars.synchronization_health_desc         AS [Sync Health],
    ars.role_desc                           AS [Current Role],
    ars.connected_state_desc                AS [Connected]
FROM sys.availability_replicas              ar
JOIN sys.dm_hadr_availability_replica_states ars
    ON ar.replica_id = ars.replica_id
ORDER BY ars.role_desc DESC, ar.replica_server_name;

9 Configuring Read-Only Routing — 2-Replica Pattern

The complete read-only routing setup for a two-replica AG. Three steps in order: set the secondary connection permission, set the routing URL for each replica, then set the routing list for each replica in its primary role. All three steps are required — missing any one of them causes routing to silently fall back to the primary without an error.

-- ============================================================
-- STEP 1: Allow read-only connections on the secondary role
-- Do this for each replica that should accept read-intent connections
-- ============================================================
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB1'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB2'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- ============================================================
-- STEP 2: Set the read-only routing URL for each replica
-- Use the FQDN and actual SQL Server port (default 1433)
-- This is the address the listener redirects connections TO
-- ============================================================
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB1'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://PROD-DB1.YourDomain.com:1433'));

ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB2'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://PROD-DB2.YourDomain.com:1433'));

-- ============================================================
-- STEP 3: Set the routing list for each replica when it is PRIMARY
-- The list is ordered priority -- first available in the list gets the connection
-- Configure for BOTH replicas so routing works after any failover
-- ============================================================

-- When PROD-DB1 is primary: route reads to PROD-DB2 first, fall back to PROD-DB1
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('PROD-DB2', 'PROD-DB1')));

-- When PROD-DB2 is primary (after failover): route reads to PROD-DB1 first, fall back to PROD-DB2
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB2'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('PROD-DB1', 'PROD-DB2')));
GO

-- ============================================================
-- VERIFY: Run Section 7 query to confirm routing is correct
-- Then test with sqlcmd:
-- sqlcmd -S YourAGListener -d YourDatabase -K ReadOnly -Q "SELECT @@SERVERNAME"
-- Result should be the secondary name, not the primary
-- ============================================================

10 Configuring Read-Only Routing — 3-Replica Pattern

The three-replica pattern — primary, local secondary, and a third replica (for example a remote or Azure DR node). This is the topology from the real-world environment described in the Complete AG Guide: two on-premises nodes plus one remote replica. The routing list for the remote replica shows how to handle a scenario where the remote node may have become primary after a DR failover and needs its own routing list configured.

-- ============================================================
-- STEP 1: Set secondary read access on all three replicas
-- ============================================================
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB1'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB2'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'RPT-DB1'     -- third replica: reporting or DR node
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- ============================================================
-- STEP 2: Set read-only routing URL for each replica
-- ============================================================
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB1'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://PROD-DB1.YourDomain.com:1433'));

ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB2'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://PROD-DB2.YourDomain.com:1433'));

ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'RPT-DB1'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://RPT-DB1.YourDomain.com:1433'));

-- ============================================================
-- STEP 3: Set routing list for each replica when it is primary
-- Priority 1 = preferred read destination
-- The current primary itself is listed last as a fallback only
-- ============================================================

-- When PROD-DB1 is primary: prefer RPT-DB1 for reads, then PROD-DB2, then self
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('RPT-DB1', 'PROD-DB2', 'PROD-DB1')));

-- When PROD-DB2 is primary: prefer RPT-DB1, then PROD-DB1, then self
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB2'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('RPT-DB1', 'PROD-DB1', 'PROD-DB2')));

-- When RPT-DB1 is primary (DR failover scenario): prefer PROD-DB1, then PROD-DB2, then self
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'RPT-DB1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('PROD-DB1', 'PROD-DB2', 'RPT-DB1')));
GO

-- ============================================================
-- LOAD-BALANCED routing across two secondaries (SQL Server 2016+)
-- Use parenthesized set for round-robin distribution
-- When PROD-DB1 is primary: distribute reads between PROD-DB2 and RPT-DB1 evenly
-- Fall back to PROD-DB1 if both secondaries are unavailable
-- ============================================================
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'PROD-DB1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('PROD-DB2', 'RPT-DB1'), 'PROD-DB1')));
GO

11 AG-Aware Backup Job — Prefer Secondary (Ola Hallengren)

The correct pattern for an AG-aware full backup job that prefers to run on a secondary replica. This is the job you put on every replica — it detects its own role at runtime and only executes the backup if this instance is the preferred backup replica. If the secondary is unavailable and the primary is the only replica running, the job still runs on the primary as a fallback.

Uses Ola Hallengren’s DatabaseBackup — the community standard for SQL Server backup maintenance. Install it in a dedicated DBA utility database before using these scripts.

-- ============================================================
-- AG-Aware Full Backup Job Step — Prefer Secondary
-- Deploy this identical job step to ALL replicas
-- The role check determines at runtime which one runs the backup
-- Uses Ola Hallengren DatabaseBackup (https://ola.hallengren.com)
-- ============================================================

DECLARE @IsPreferredBackupReplica BIT = 0;
DECLARE @DatabaseName             NVARCHAR(128) = 'YourDatabase';  -- or use USER_DATABASES

-- Use the built-in function to check preferred backup replica
-- This respects the AUTOMATED_BACKUP_PREFERENCE setting on the AG
-- Returns 1 if this replica should run the backup, 0 if another should
SET @IsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@DatabaseName);

IF @IsPreferredBackupReplica = 0
BEGIN
    PRINT 'This replica is not the preferred backup replica. Job exiting gracefully.';
    -- Job step exits with success -- not an error, just not the right replica
    RETURN;
END

-- This replica is the preferred backup target -- run the backup
-- CopyOnly = 'Y' is critical when running on a secondary replica
-- It ensures the backup does not reset the differential base on the primary
EXECUTE [DBAUtility].dbo.DatabaseBackup
    @Databases          = 'USER_DATABASES',
    @Directory          = '\\YourBackupShare\sqlbackup\',
    @BackupType         = 'FULL',
    @CopyOnly           = 'Y',      -- REQUIRED for secondary full backups
    @Compress           = 'Y',
    @CheckSum           = 'Y',
    @LogToTable         = 'Y',
    @BufferCount        = 50,
    @MaxTransferSize    = 4194304,  -- 4 MB -- optimal for most environments
    @CleanupTime        = 168;      -- 7 days -- adjust for your retention policy

For log backups the same pattern applies but @CopyOnly is not needed — log backups on secondaries are always part of the shared log chain and do not need the copy-only flag.

-- Log backup job step -- same AG-aware check, no CopyOnly needed for logs
DECLARE @IsPreferredBackupReplica BIT;
SET @IsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica('YourDatabase');

IF @IsPreferredBackupReplica = 0
BEGIN
    PRINT 'Not the preferred backup replica. Exiting.';
    RETURN;
END

EXECUTE [DBAUtility].dbo.DatabaseBackup
    @Databases       = 'USER_DATABASES',
    @Directory       = '\\YourBackupShare\sqlbackup\',
    @BackupType      = 'LOG',
    @Compress        = 'Y',
    @CheckSum        = 'Y',
    @LogToTable      = 'Y',
    @CleanupTime     = 48;  -- 2 days for log backups

12 AG-Aware Backup Job — Primary Only Pattern

When you specifically need the backup to run only on the primary — for example differential backups, which cannot run on a secondary replica in SQL Server versions before 2025. Uses @@SERVERNAME matched against sys.availability_replicas with the is_local = 1 filter to identify this instance’s role precisely.

-- ============================================================
-- Primary-only backup job step
-- Uses @@SERVERNAME to identify this instance, then checks its role
-- Deploy to all replicas -- only the current primary will run the backup
-- ============================================================

DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME;
DECLARE @RoleDesc   NVARCHAR(60);

SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas               AS b
    ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
AND   a.is_local             = 1;   -- always filter to local replica

-- role_desc returns uppercase: 'PRIMARY' or 'SECONDARY'
IF @RoleDesc != 'PRIMARY'
BEGIN
    PRINT 'This instance is not the primary replica. Job exiting gracefully.';
    RETURN;
END

-- Primary confirmed -- run backup
-- For primary-only backups, CopyOnly is NOT set so differential chain is maintained
EXECUTE [DBAUtility].dbo.DatabaseBackup
    @Databases          = 'USER_DATABASES',
    @Directory          = '\\YourBackupShare\sqlbackup\',
    @BackupType         = 'FULL',
    @Compress           = 'Y',
    @CheckSum           = 'Y',
    @LogToTable         = 'Y',
    @BufferCount        = 50,
    @MaxTransferSize    = 4194304,
    @CleanupTime        = 168;

13 Using the Built-In fn_hadr_backup_is_preferred_replica

This built-in function is the cleanest and most reliable way to make backup jobs AG-aware. It reads the AUTOMATED_BACKUP_PREFERENCE setting you configured on the AG and returns 1 if this replica should run the backup based on that preference. You do not need to hard-code a role check — the preference logic (PRIMARY, SECONDARY, SECONDARY_ONLY, or NONE) is evaluated automatically.

The pattern in Section 11 uses this function. The table below explains what each AG preference setting means for the function’s return value.

AG Backup Preference	Returns 1 (run backup) on	Best For
PRIMARY	Primary only	When you want all backups on primary (uncommon)
SECONDARY	Secondary if available, primary as fallback	Most environments — offload from primary but still protected if secondary is down
SECONDARY_ONLY	Secondary only — never primary	When primary is heavily loaded and secondary is always available
NONE	All replicas (1 always)	When you manage backup scheduling externally

-- Check the current AG backup preference
SELECT
    name                              AS [AG Name],
    automated_backup_preference_desc  AS [Backup Preference]
FROM sys.availability_groups;

-- Change backup preference on an existing AG
ALTER AVAILABILITY GROUP [YourAG]
SET (AUTOMATED_BACKUP_PREFERENCE = SECONDARY);

-- Test the function directly for a specific database
-- Returns 1 if this replica should run the backup, 0 if not
SELECT
    DB_NAME()                                          AS [Current Database],
    @@SERVERNAME                                       AS [This Server],
    sys.fn_hadr_backup_is_preferred_replica(DB_NAME()) AS [Should Run Backup?];

14 Add an Asynchronous Replica to an Existing AG

The complete sequence for adding a new asynchronous replica to an existing AG — for example adding a DR node in a remote data center or cloud region. Three steps: add the replica on the primary, join the AG on the new secondary, then bring the database into the AG on the new secondary.

The source script showed two approaches. The second, more explicit version is shown here — it is the correct pattern. The first version in the source had missing N'' prefixes and no square brackets around the AG name, which would cause errors if the AG name contains hyphens.

-- ============================================================
-- Run on the PRIMARY replica
-- Adds a new asynchronous secondary replica to an existing AG
-- The new replica uses manual failover and asynchronous commit
-- Typically used for DR replicas or remote read replicas
-- ============================================================
ALTER AVAILABILITY GROUP [YourAG]
ADD REPLICA ON N'RPT-DB1'
WITH (
    ENDPOINT_URL         = N'TCP://RPT-DB1.YourDomain.com:5022',
    FAILOVER_MODE        = MANUAL,
    AVAILABILITY_MODE    = ASYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY      = 50,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);
GO

-- ============================================================
-- Run on the NEW SECONDARY replica (RPT-DB1)
-- Join the instance to the AG
-- ============================================================
ALTER AVAILABILITY GROUP [YourAG] JOIN;
GO

-- ============================================================
-- Run on the NEW SECONDARY replica
-- If using automatic seeding (SQL Server 2016+):
ALTER AVAILABILITY GROUP [YourAG] GRANT CREATE ANY DATABASE;
GO

-- If using manual seeding (backup/restore):
-- 1. Take a full backup on the primary
-- 2. Restore WITH NORECOVERY on the new secondary
-- 3. Then run this to join the database to the AG:
ALTER DATABASE [YourDatabase] SET HADR AVAILABILITY GROUP = [YourAG];
GO

-- ============================================================
-- VERIFY: Run Section 3 health query to confirm the new replica
-- shows SYNCHRONIZED or SYNCHRONIZING synchronization state
-- ============================================================

15 Mirroring Endpoint Setup Reference

Every replica must have a database mirroring endpoint before it can participate in an AG. This is the transport channel — the TCP listener SQL Server uses to ship log records between replicas. Port 5022 is the convention; you can use any unoccupied port. Run this on each instance before creating or joining an AG.

-- ============================================================
-- Create and start the mirroring endpoint
-- Run on EACH replica instance before AG creation or joining
-- Use a consistent port across all replicas -- 5022 is the convention
-- ============================================================

-- Check if endpoint already exists first
SELECT name, state_desc, type_desc
FROM sys.endpoints
WHERE type_desc = 'DATABASE_MIRRORING';

-- Create endpoint only if it does not exist
IF NOT EXISTS (
    SELECT 1 FROM sys.endpoints WHERE type_desc = 'DATABASE_MIRRORING'
)
BEGIN
    CREATE ENDPOINT [Hadr_Endpoint]
        STATE = STARTED
        AS TCP (LISTENER_PORT = 5022)
        FOR DATA_MIRRORING (
            ROLE       = ALL,
            ENCRYPTION = REQUIRED ALGORITHM AES
        );
    PRINT 'Endpoint created and started.';
END
ELSE
BEGIN
    -- Ensure existing endpoint is started
    ALTER ENDPOINT [Hadr_Endpoint] STATE = STARTED;
    PRINT 'Endpoint already exists -- confirmed STARTED.';
END
GO

-- Grant CONNECT to the SQL Server service account
-- Replace with your actual service account
GRANT CONNECT ON ENDPOINT::Hadr_Endpoint TO [YourDomain\SQLServiceAccount];
GO

-- Verify
SELECT name, state_desc, type_desc, port
FROM sys.endpoints
WHERE type_desc = 'DATABASE_MIRRORING';

-- ============================================================
-- To drop and recreate an endpoint (use with caution --
-- dropping the endpoint while an AG is active will disconnect replicas)
-- ============================================================
-- DROP ENDPOINT [Hadr_Endpoint];
-- GO

Common Mistakes This Toolkit Fixes

A few patterns from the source scripts worth calling out explicitly so you do not repeat them:

Mistake	What Happens	Correct Pattern
Role check without is_local = 1	On multi-database AGs the query returns multiple rows; variable assignment is unpredictable; backup may or may not run	Always add AND is_local = 1 to any role check query
Full backup on secondary without @CopyOnly = 'Y'	Resets the differential base on the primary; next differential is unexpectedly large or breaks the restore chain	Always set @CopyOnly = 'Y' for full backups taken on a secondary replica
Routing list set for primary only, not for all possible primaries	After a failover the new primary has no routing list; read-intent connections silently fall back to primary	Set READ_ONLY_ROUTING_LIST for every replica that could become primary
Role comparison case mismatch (‘Secondary’ vs ‘SECONDARY’)	role_desc returns uppercase ‘PRIMARY’ / ‘SECONDARY’; mixed-case comparison silently never matches	Always compare against uppercase: = 'PRIMARY' or = 'SECONDARY'
Using CREATE AVAILABILITY GROUP instead of ALTER when modifying routing	Fails with an error on any AG that already exists	Modifying an existing AG always uses ALTER AVAILABILITY GROUP
Smart/curly quotes in T-SQL (copied from Word or a web page)	Silently fails — SQL Server does not recognize curly quotes as string delimiters	Always use straight single quotes ' in T-SQL; re-type or find-replace before running

References

• Ola Hallengren — SQL Server Backup Solution (DatabaseBackup)
• Microsoft Docs — Configure Read-Only Routing for an Availability Group
• Microsoft Docs — sys.dm_hadr_database_replica_states
• Microsoft Docs — sys.dm_hadr_availability_replica_states
• Microsoft Docs — Offload Supported Backups to Secondary Replicas
• SQLYARD — SQL Server Always On Availability Groups: The Complete DBA Guide
• SQLYARD — SQL Server DBA Health Check Toolkit