Verify SQL Server read-only routing from the application

Adding ApplicationIntent=ReadOnly to a connection string does not prove that reports moved off the primary. Routing depends on the listener, the named database, replica configuration, and the client's connection path. The first useful test is to ask the newly opened connection where it actually landed.

Verify the destination through the real connection

Execute this probe through the reporting application's connection, not through an unrelated SSMS session.

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

For an availability-group database, IsPrimaryReplica distinguishes the primary from a secondary. NULL can mean the database is not participating or cannot be resolved in the relevant context; do not treat it as a successful secondary result. Record the server and database alongside the application request or connection test.

A routing-capable client normally connects to the availability-group listener, names a database in that group, and specifies ApplicationIntent=ReadOnly. For example, Server=tcp:listener.example,1433;Database=ReportingDb;ApplicationIntent=ReadOnly illustrates the routing-related portion of a connection string. It is not a complete credential or TLS configuration.

Connecting directly to a replica hostname bypasses the listener's routing decision. Omitting the intended database can also prevent the expected route. Verify the exact effective connection string after configuration overrides, while excluding secrets from logs.

The routed destination must be reachable from the client. A successful TCP connection to the listener alone does not prove that the client can reach a replica's routing URL and port. Check DNS, firewall paths, certificate trust, and driver support for the actual target rather than only the initial listener.

Inspect configuration for every possible primary

Read-only routing lists describe where connections should go when a particular replica owns the primary role. The following read-only metadata query displays those source-to-target relationships for an operator with appropriate visibility.

SELECT
    ag.name AS AvailabilityGroup,
    source.replica_server_name AS WhenPrimary,
    rl.routing_priority,
    target.replica_server_name AS RouteTo,
    target.read_only_routing_url
FROM sys.availability_read_only_routing_lists AS rl
JOIN sys.availability_replicas AS source
    ON source.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 = source.group_id
ORDER BY ag.name, source.replica_server_name, rl.routing_priority;

Review each replica that can become primary, not just today's primary. A configuration that works until failover may simply be missing the routing list for the new role owner. Targets also need readable-secondary configuration and a valid routing URL.

A routing list can prioritize destinations, and supported configurations can group targets for connection-level distribution. Routing happens when a connection is established; it does not move an already-running query between replicas. Connection pooling can therefore make distribution look uneven and can preserve existing connections until they are closed or broken.

Define whether falling back to the primary is acceptable when reporting secondaries are unavailable. That is a workload policy, not an automatic sign of success. If fallback is permitted, monitor it because it can return heavy reporting load to the transactional server during an incident.

ApplicationIntent is not a substitute for database permissions. A connection on a read-write primary can still have write privileges granted to its principal. Use an appropriately restricted reporting identity and do not depend on the connection-string label as an authorization boundary.

Test freshness and role changes as part of the contract

Readable secondaries replay changes and can lag behind the primary. Even synchronous commit does not mean every committed change is already redone and visible to a read on the secondary. A screen that writes on the primary and immediately reads from reporting may temporarily fail to find its own change.

Decide which operations tolerate that delay. Keep immediate read-after-write paths on an appropriate consistent route, or expose a clear freshness requirement with a tested waiting or reconciliation strategy. Monitor redo progress and report latency as well as connection success.

Test the actual application across a planned failover, a temporarily unavailable target, and a pool containing older connections. Confirm the destination again after reconnecting. Handle transient connection failures with bounded retries appropriate to the read operation.

Finally, account for secondary capacity. Reporting consumes CPU, memory, and I/O that also support redo. A route that reaches a secondary correctly can still harm freshness under heavy load. Validate report throughput and redo lag together, and retain the destination probe as part of operational diagnostics so routing remains observable after future configuration changes.

Technical references: Microsoft Learn: Read-only routing configuration · Microsoft Learn: Readable secondary replicas.