SQL Server Clusterless Read Scale Availability Group Setup

Learn how to setup an SQL Server Clusterless Read Scale Availability group. Use the listener for client connections and see how to fail over the availability group. See the redirection in action for read only requests on the primary being redirected automatically to the secondary.

TSQL to return the read-only routing configuration

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