Fun With READ_ONLY_ROUTING_LIST Syntax
I finally am getting to work with SQL Server 2016 and spent the past two days building out an Availability Group to test the new read-only replica functionality. This allows connections with an ApplicationIntent of ReadOnly to round-robin between all instances in the READ_ONLY_ROUTING_LIST, load balancing the read-only activity.
For my test, I built out three replicas and set all three to be readable secondaries. Using Microsoft's documentation on configuring read-only routing, I coded the following:
ALTER AVAILABILITY GROUP SKREEBYHA MODIFY REPLICA ON 'AGNODE1\SKREEBYAG' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST=('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG') ) );
To test the new setup, I used the SQLCMD examples from Eric Russo's blog post on Load Balancing 101. Running the following command:
SQLCMD -S SKREEBYLIST -K ReadOnly -d testdb -Q "SELECT @@SERVERNAME"
returned AGNODE2\SKREEBYAG in each case. I could not figure out what I was doing wrong. After much frustration, I noticed the routing check query at the bottom of Eric's post:
SELECT ar_Primary.replica_server_name 'Primary', ar_secondary.replica_server_name 'Route_to', ror.routing_priority 'Priority' FROM sys.availability_read_only_routing_lists ror INNER JOIN sys.availability_replicas ar_Primary ON ror.replica_id = ar_Primary.replica_id INNER JOIN sys.availability_replicas ar_secondary ON ror.read_only_replica_id = ar_secondary.replica_id INNER JOIN sys.availability_groups ag ON ag.group_id = ar_Primary.group_id WHERE ag.name = 'SKREEBYHA' ORDER BY 1,3
Running it returned the following results:
So, in technical terms, something was screwy. Looking at Eric's post again, I noticed his third example for setting a routing list:
ALTER AVAILABILITY GROUP SQL2016AG MODIFY REPLICA ON 'MyNode3' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST= (('MyNode4','MyNode1','MyNode2')) ) )
Notice the second set of parentheses around the READ_ONLY_ROUTING_LIST value. Based on this, I changed my code to:
ALTER AVAILABILITY GROUP SKREEBYHA MODIFY REPLICA ON 'AGNODE1\SKREEBYAG' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST=(('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG')) ) );
and, voila, the routing list check query returned the following:
Rerunning the SQLCMD test, the read-only connections round-robined between AGNODE2 and AGNODE3.
Many thanks to Eric Russo (b|t) for a great blog post. I hope this saves you some frustration when working with read-only routing.