First published on MSDN on Oct 18, 2016
This is a follow up to my first post Finding What Availability Group Listeners Applications Are Using To Connect , where a question was posed about knowing which connections have been read only routed.
Background Information
How it Works (Brief Overview)
How Read Only Routing works is simple and we'll take a very high level look at how it works.
We need to configure the primary and secondary role values for the availability group, this includes the endpoint and urls. Once this is setup if we give enough information in our connection string (Listener name, database, ApplicationIntent) then we can be re-routed to a readable secondary replica! That's great, but to understand why the following works we should understand how this redirection works.
Let's walk through at a high level how this works.
- We create a connection through the driver
- The driver connects to SQL Server through the listener and passes the relevant information
- SQL Server notices that we have read only routing setup and checks the configuration
- SQL server reports back to the driver that we have a read only secondary that can be used
- SQL Server sends the new secondary information to the driver
- The driver creates a new connection to the secondary with the given information
The packet capture on the right shows the original conversation went between the client (192.168.1.10) and Node2 (primary listener, 192.168.1.33). Then we see a packet that tells the driver to read only route to the different node (Node1) and we can see the connection change from talking to 192.168.1.33 to now talking to 192.168.1.50. This illustrates the high-level overview given above of the transparent client redirection for read only routing.
Making Changes to Find Connections That Were Read Only Routed
Now that we understand how it works, what can we do to expose this? There are no extended events to tell you *where* the connection was routed to, just where it was successfully read only routed or not. We, however, can use a fact of the above process to expose this information to us by using different ports.
Why does this work? It works because we reply with the new endpoint that the driver should connect to. Since SQL Server can listen on multiple ports and IP addresses, we can have SQL Server listen on a second port on the same IP address (for less complexity) that we only use for read only routing connections.
Step 1 – Configure Additional Ports
To set this up, we can go to the SQL Server Configuration Manager and tell SQL Server to listen on another port. In my lab environment, I set this on Node2 to listen on the additional port of 2433 whereas I have Node1 listen on the additional port of 22022. This was done to show the difference and is not required in your environment. Once we add the additional port numbers are added it will require a restart of the SQL Server Services. This will look something a such:
You can see that we can add ports by separating them with a comma. In this case, I'm going to listen on all available addresses on port 1433 AND port 22022.
Step 2 – Configure Read Only Routing
As you can see in the picture to the left, I've used the new ports as the read only routing endpoint url. This is what will be given back to the client driver and what the driver will then use to open up the new connection. Since this port is different than the default (1433 on both instances) we can easily conclude which connections were read only routed to the node and which connections were directly connected to the node.
Please note that anyone who knows these values can easily put the port number in the connection string and directly connect… we're using the fact that these endpoint urls aren't directly exposed anywhere and most end users won't be investigating or know about SQL Server endpoints and ports. Since read only routing happens transparently, this should be of no concern or knowledge to the end user – it should just happen.
Step 3 – Test
Now that we're set this up, let's check to see if it works! Opening SSMS and creating a new connection, then running a quick query to check the server name. During this I also captured a network trace to show the change described in the how it works overview.
Here we can see being Read Only Routed to Node 2 by being on port 2433. 
This is what it looks like when a connection was read only routed to Node1.
Wrapping Up
This should have explained a little more about how the transparent redirect works with read only routing and how we can leverage it for our administrative and troubleshooting purposes!