AG: read-only routing list query
T-SQL query listing, per replica, the read-only replicas, their routing URL and priority in an availability group.
Query the read-only routing lists of an availability group. For each replica, it shows the read-only replicas that read-intent connections are routed to, with their routing URL and priority.
Routing list query
--Routing list
SELECT AVGSrc.replica_server_name AS SourceReplica
, AVGRepl.replica_server_name AS ReadOnlyReplica
, AVGRepl.read_only_routing_url AS RoutingURL
, AVGRL.routing_priority AS RoutingPriority
FROM sys.availability_read_only_routing_lists AVGRL
INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
ORDER BY SourceReplica