Sending reports to the secondary - configuring and testing Always On read-only routing

#The goal

Reporting queries and read-only API calls are competing with OLTP on the primary replica, while a fully synchronised secondary sits idle. Read-only routing lets those clients keep connecting to the same AG listener; SQL Server redirects any connection that declares ApplicationIntent=ReadOnly to a readable secondary, and follows the AG through failovers.

Routing needs three things in place:

1. Each secondary allows read-only connections and has a read-only routing URL (where clients should be sent when it is secondary).
2. Each replica that can become primary has a read-only routing list (which secondaries to send reads to when it is primary).
3. Clients connect to the listener, over TCP, to a database in the AG, with ApplicationIntent=ReadOnly.

The examples use AG AG1, listener AG1-LSN, replicas SQL01, SQL02, SQL03 in contoso.com, and database SalesDB.

Warning

Basic availability groups (Standard edition) do not support readable secondaries. Readable secondaries also usually need to be licensed. Check your licensing position before you send production reads to them.

#1. Check the current state

Run on the primary:

SELECT  ag.name                                   AS ag_name,
        ar.replica_server_name,
        ars.role_desc,
        ar.availability_mode_desc,
        ar.secondary_role_allow_connections_desc  AS readable_as_secondary,
        ar.read_only_routing_url
FROM    sys.availability_groups ag
JOIN    sys.availability_replicas ar           ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name;

SELECT dns_name, port FROM sys.availability_group_listeners;

You need a listener row, and you need to know the TCP port each instance actually listens on. For a named instance on a dynamic port the routing URL breaks after the port changes, so pin a static port first. To see the port of the instance you are connected to over TCP:

SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID;

#2. Make the secondaries readable and give each a routing URL

ALLOW_CONNECTIONS = READ_ONLY accepts only connections that declare read intent. ALL also accepts normal connections, which is rarely what you want for reporting.

-- Run on the primary. Repeat for every replica, including the current primary,
-- so the settings are already in place after a failover.
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL01'
  WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,
                        READ_ONLY_ROUTING_URL = N'TCP://SQL01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02'
  WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,
                        READ_ONLY_ROUTING_URL = N'TCP://SQL02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL03'
  WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,
                        READ_ONLY_ROUTING_URL = N'TCP://SQL03.contoso.com:1433'));

Use a name the clients can resolve. If application servers cannot resolve SQL02.contoso.com (different DNS zone, cloud VNet, NAT), the redirect fails even though the listener works.

#3. Set a routing list on every replica that can be primary

The URL must exist before you reference the replica in a list. The list only takes effect while that replica is primary.

-- When SQL01 is primary: send reads to SQL02, then SQL03, then SQL01 itself as a last resort
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL01'
  WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'SQL02', N'SQL03', N'SQL01')));

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02'
  WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'SQL01', N'SQL03', N'SQL02')));

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL03'
  WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'SQL01', N'SQL02', N'SQL03')));

Putting the replica's own name last means read-intent connections still land on the primary if every secondary is down, instead of failing. Leave it out if you would rather reports fail than load the primary.

#Load-balance across secondaries (SQL Server 2016+)

One level of nested parentheses turns a set of replicas into a round-robin group. Entries after the group are only tried if nobody in the group is available.

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL01'
  WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ((N'SQL02', N'SQL03'), N'SQL01')));

Load balancing is per new connection, not per query. Connection pools that keep connections open will stay on whichever secondary they first reached.

#4. Verify the routing table

SELECT  pr.replica_server_name  AS when_primary_is,
        rl.routing_priority,
        sr.replica_server_name  AS route_reads_to,
        sr.read_only_routing_url
FROM    sys.availability_read_only_routing_lists rl
JOIN    sys.availability_replicas pr ON pr.replica_id = rl.replica_id
JOIN    sys.availability_replicas sr ON sr.replica_id = rl.read_only_replica_id
ORDER BY when_primary_is, rl.routing_priority;

Replicas in the same load-balanced group share a routing_priority value.

#5. Test from a client machine

Test from an application server, not from a replica, so DNS and firewall paths match production. -K ReadOnly sets the application intent and -M enables multi-subnet failover.

# Expect a secondary's name
sqlcmd -S tcp:AG1-LSN.contoso.com,1433 -d SalesDB -E -K ReadOnly -M -Q "SELECT @@SERVERNAME AS routed_to, DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS updateability"

# Expect the primary's name
sqlcmd -S tcp:AG1-LSN.contoso.com,1433 -d SalesDB -E -M -Q "SELECT @@SERVERNAME"

Run the read-only test several times when you use a load-balanced group; the server name should alternate.

Application connection strings need the same pieces:

Server=tcp:AG1-LSN.contoso.com,1433;Database=SalesDB;Integrated Security=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;Encrypt=True

The same check from PowerShell with the SqlServer module, passing the full connection string:

$cs = 'Server=tcp:AG1-LSN.contoso.com,1433;Database=SalesDB;Integrated Security=True;' +
      'ApplicationIntent=ReadOnly;MultiSubnetFailover=True;Encrypt=True;TrustServerCertificate=True'
1..4 | ForEach-Object { Invoke-Sqlcmd -ConnectionString $cs -Query "SELECT @@SERVERNAME AS routed_to" }

#When reads still go to the primary

#What changes for queries on a secondary

• Data can lag the primary, even on a synchronous replica, because redo is asynchronous. Check secondary_lag_seconds in sys.dm_hadr_database_replica_states (SQL Server 2016 and later) before sending time-sensitive reads.
• Queries on a readable secondary run under snapshot isolation. Enabling readable secondaries adds row-versioning overhead on the primary and increases tempdb use on the secondaries.
• Long-running reports on a secondary can delay redo and hold back ghost cleanup on the primary. Watch redo_queue_size.
• Statistics needed by secondary queries are created as temporary statistics in tempdb and disappear on restart or failover.

SELECT  ar.replica_server_name, DB_NAME(drs.database_id) AS db,
        drs.synchronization_state_desc, drs.redo_queue_size, drs.secondary_lag_seconds
FROM    sys.dm_hadr_database_replica_states drs
JOIN    sys.availability_replicas ar ON ar.replica_id = drs.replica_id
ORDER BY db, ar.replica_server_name;   -- run on the primary to see every replica

#Optional: redirect read/write connections that hit a secondary

From SQL Server 2019, a replica can send ordinary (read/write) connections that reach it while it is secondary to the current primary. This helps applications that connect to an instance name instead of the listener. The secondary must allow ALL connections.

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02'
  WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02'
  WITH (PRIMARY_ROLE (READ_WRITE_ROUTING_URL = N'TCP://SQL02.contoso.com:1433'));

Set READ_WRITE_ROUTING_URL on every replica. Using the listener remains the better fix.

#Prevent it next time

• Keep the URL and routing-list scripts in source control and apply them to every replica, so a failover or a rebuilt node does not silently drop routing.
• Add the step 4 query to your post-failover checklist and to replica rebuild runbooks.
• Alert on secondary_lag_seconds and redo_queue_size for secondaries that serve reads.
• Ask application teams to log the server that served a request (@@SERVERNAME) so you can see where read traffic actually goes.