Read-Only Routing for Availability Group Replicas

Your report still reaches the primary even though a readable secondary is available. Read-only routing connects the application's read intent with a configured availability group destination.

Test Read-Only Routing With a Real Availability Group

This example requires an existing SQL Server availability group with an operational listener and readable replicas. It cannot be verified on an ordinary standalone instance. Use a test group with approved reporting data and known replica names.

The sample names are ReportingAg, NODEA, NODEB, and NODEC. ReportDb belongs to that group, and ReportingListener is its listener. Replace those names and addresses with the actual test configuration before executing changes.

I begin routing investigations by checking where a new application connection actually landed. I also inspect the database named in that connection. A listener address alone does not establish that the request qualified for reporting redirection.

Readable-secondary support also depends on the availability group capabilities and licensing. A basic availability group does not provide the same reporting configuration as a full group. Confirm those prerequisites before planning to move reporting traffic.

Connect Through the Listener With Read Intent

The client connects to the listener and names an availability database. Its connection string sets ApplicationIntent=ReadOnly. The current primary then evaluates the configured routing destinations for that read-intent connection.

ApplicationIntent expresses workload intent; it does not grant database permissions. Use a reporting identity with only the necessary read access. A connection redirected to the primary must not unexpectedly acquire broad write permissions.

The following C# fragment uses the official Microsoft SqlClient connection-string builder. It shows configuration for an application that already references Microsoft.Data.SqlClient. Supply the real listener name and database through the application's approved configuration.

// C#
var builder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder
{
    DataSource = "ReportingListener",
    InitialCatalog = "ReportDb",
    IntegratedSecurity = true,
    Encrypt = true,
    TrustServerCertificate = false,
    ApplicationIntent = Microsoft.Data.SqlClient.ApplicationIntent.ReadOnly,
    MultiSubnetFailover = true
};
string reportingConnectionString = builder.ConnectionString;

MultiSubnetFailover concerns listener connection behavior and should match the supported listener deployment. It does not select a reporting replica by itself. Keep certificate validation configured for the names reached by the actual connection path.

Directly connecting to a named secondary bypasses listener-based routing selection. Omitting the availability database or read-intent property changes the request. Compare effective application settings rather than assuming the displayed connection label tells the whole story.

Give Each Replica Its Database Engine Address

READ_ONLY_ROUTING_URL identifies a replica's database engine TCP address for redirected clients. It is not the availability group data-movement endpoint. Confusing those ports produces a configuration that looks complete but cannot carry the report connection.

Configure every replica that can serve as a readable destination. The following changes belong to the approved test group and require suitable availability group permissions. The addresses use example hostnames and must resolve from the application computer. Each MODIFY REPLICA statement changes one option, so the connection setting and the URL are separate statements.

ALTER AVAILABILITY GROUP [ReportingAg]
MODIFY REPLICA ON N'NODEA'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [ReportingAg]
MODIFY REPLICA ON N'NODEA'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://nodea.example.test:1433'));

ALTER AVAILABILITY GROUP [ReportingAg]
MODIFY REPLICA ON N'NODEB'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [ReportingAg]
MODIFY REPLICA ON N'NODEB'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://nodeb.example.test:1433'));

ALTER AVAILABILITY GROUP [ReportingAg]
MODIFY REPLICA ON N'NODEC'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [ReportingAg]
MODIFY REPLICA ON N'NODEC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://nodec.example.test:1433'));

Use the actual database engine port for each instance, including named instances. Do not assume every replica listens on 1433. Verify client reachability, authentication, and certificates against each configured destination.

The URL takes effect when that replica is in the secondary role. A primary-role routing list is a separate configuration. Both sides are required to redirect eligible connections successfully.

Configure Priority and Balanced Sets for Every Primary Role

A sequential routing list tries eligible destinations in the configured order. SQL Server 2016 and later also support a load-balanced set expressed with one nested parenthesis level. That grouping applies to new connections, rather than individual statements.

The following sets NODEB and NODEC as a balanced reporting set when NODEA is primary. The later entries configure the corresponding behavior after either other replica becomes primary. Each list contains that current primary as its final fallback. The primary role already allows all connections by default, so each statement sets only the list.

ALTER AVAILABILITY GROUP [ReportingAg]
MODIFY REPLICA ON N'NODEA'
WITH (PRIMARY_ROLE
    (READ_ONLY_ROUTING_LIST = ((N'NODEB', N'NODEC'), N'NODEA')));

ALTER AVAILABILITY GROUP [ReportingAg]
MODIFY REPLICA ON N'NODEB'
WITH (PRIMARY_ROLE
    (READ_ONLY_ROUTING_LIST = ((N'NODEA', N'NODEC'), N'NODEB')));

ALTER AVAILABILITY GROUP [ReportingAg]
MODIFY REPLICA ON N'NODEC'
WITH (PRIMARY_ROLE
    (READ_ONLY_ROUTING_LIST = ((N'NODEA', N'NODEB'), N'NODEC')));

Primary fallback is a business availability decision. It keeps reports possible when no eligible secondary is available, but returns reporting load to the primary. Omit that fallback only after deciding how the application should handle unavailable reporting replicas.

Routing does not divide work according to CPU utilization or query duration. Connection pooling also keeps existing physical connections attached to their selected replica. A balanced configuration therefore does not promise equally sized reporting workloads.

Plan lists for every permitted failover destination. A carefully configured current primary can leave reporting broken after failover if the next primary lacks its list. Testing only today's topology misses that failure mode.

Inspect the Saved Read-Only Routing Configuration

Join routing-list metadata with replica metadata to display the owner and destination names. Destinations sharing the same routing priority represent a balanced set. The destination URL and allowed-connection setting help explain whether that entry is usable.

SELECT ag.name AS GroupName,
       owner.replica_server_name AS WhenPrimary,
       rl.routing_priority,
       target.replica_server_name AS Destination,
       target.read_only_routing_url,
       target.secondary_role_allow_connections_desc
FROM sys.availability_read_only_routing_lists AS rl
JOIN sys.availability_replicas AS owner
    ON owner.replica_id = rl.replica_id
JOIN sys.availability_replicas AS target
    ON target.replica_id = rl.read_only_replica_id
JOIN sys.availability_groups AS ag ON ag.group_id = owner.group_id
ORDER BY GroupName, WhenPrimary, rl.routing_priority, Destination;

Appropriate metadata and performance permissions are required for a complete view. SQL Server 2022 and later use VIEW SERVER PERFORMANCE STATE for the routing-list catalog. Record missing visibility rather than interpreting hidden rows as absent configuration.

Compare saved values with the intended topology before opening application tests. Check hostname resolution and network ports from the actual application location. Successful replica communication for data movement does not prove client connectivity to the routing URL.

Verify Destination and Reporting Semantics

Run the following query through a newly opened reporting connection. It identifies the connected instance and whether the selected database is locally primary. Verify several new physical connections rather than repeatedly executing through one pooled connection. A database outside any availability group returns NULL in the last column.

SELECT CONVERT(nvarchar(128), SERVERPROPERTY('ServerName')) AS ConnectedServer,
       DB_NAME() AS ConnectedDatabase,
       sys.fn_hadr_is_primary_replica(DB_NAME()) AS IsLocalPrimary;

Does the report tolerate replica lag after the user's latest update? A readable secondary can present data behind the primary. Routing a report correctly does not guarantee immediate read-after-write consistency.

Application credentials must work at every eligible destination. Synchronize the necessary login mapping or use the approved contained-user design. An accurate routing URL cannot repair a missing login or a mismatched security identifier.

Record the effective pool configuration when testing read-only routing. Separate write-intent and read-intent connection configurations so application code selects the intended workload path. Observe connection establishment rather than expecting an existing connection to move after each query.

Exercise an approved failover and repeat the fresh-connection checks. Also test unavailable destinations and the chosen fallback behavior. Read-only routing is verified when connection destination, permissions, freshness expectations, and failover behavior all match the design.

Related reading on this blog: Query Store Feature for Secondary Replicas and Always On Listener Not Coming Online: Failed to Create New NBT Interface, Status 1450.

A readable replica is not a routed workload, it is a destination that needs an explicit and tested connection policy.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.