The only non guessable setting was the “port” part of the Read-Only Routing URL. Luckily there’s a script to calculate it here …
https://docs.microsoft.com/en-us/archive/blogs/mattn/calculating-read_only_routing_url-for-alwayson
(Make sure you run this on each node as the ports may be wildly different)
The other relevant settings for me were:-
- Readable Secondary: Read-intent only (on each replica)
- Listener Port: 1433 (the default, just for simplicity sake)
To test read-only routing I ran this from a different SQL Server:-
sqlcmd -S ListenerName -d DatabaseName -E
-q "SELECT @@SERVERNAME;" -K ReadOnly
Which returned the name of the secondary replica. I then ran it again without the “-K ReadOnly” which returned the name of the primary replica.
Database Consultant 