One of the main benefits of SQL Server AlwaysOn Availability Group is being able
to scale out read-only workload to secondary replicas. By default, Read-Only Routing is
not automatically enabled when you build your availability group which most of you done by SQL Server Management Studio GUI. Read-only routing can only be configured using Transact-SQL or PowerShell command. In this blog post I'm going to demonstrate how to configure read-only routing using Transact-SQL.
Let's say you already configured an availability group using the GUI wizard in SSMS. In this example I have two availability replicas.
1. Execute ALTER AVAILABILITY GROUP MODIFY REPLICA statements in order to allow secondary role read-only connections and specify read-only routing URL for each replica:
2. For each replica that you want to support read-only routing when it is the primary replica, you need to specify a read-only routing list. A given read-only routing list takes effect only when the local replica is running under the primary role:
3. Execute below query to verify the read-only routing list was set properly:
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;
4. Test read-only routing using SQLCMD with the –K readonly parameter, along with the listener name and the database name in the availability group. The output shows the secondary replica receiving read connections according to read-only routing list:
