Always ON READ ONLY ROUTING from SQL Server Management Studio

Hi Mates,

Today we are back with a simple topic on Always ON. We know there is a feature Read ONLY Routing which was there from SQL Serve 2012.

However we can't create it from GUI until SSMS 17.4 I believe .

In this post we will explore on how to configure from GUI and as a add on I will share few interesting scenarios as well.

Below is the Server information: (This is not multi subnet setup) I changed the server names

NODE1: NODE1\NONPROD

NODE2: NODE2\NONPROD

Always on Availability Group: AONPROD

LISTENER NAME: AONLISTENER

once we right click on the listener name & go to properties  we will find the below information.

So we have 3 tabs on the top under server Instance we have our 2 servers populated already.

Now we have to work on other 2 tabs. In the Read-only Routing URL we need to pass the FQDN or IP address: port combination

And in Read-only routing list we need to populate like this.

So for  NODE1\NONPROD it should be NODE2\NONPROD and then NODE1\NONPROD
and for NODE2\NONPROD it should be NODE1\NONPROD and then NODE2\NONPROD

This means when the NODE1 is primary the read requests should first move on to NODE2 and if it is not available then it should land on NODE1.

In the similar fashion when NODE2 is primary the read request should first pass on to NODE1 & then to NODE2.

if you see in the bottom We have routing list for NODE1\NONPROD

under this Available replicas tab is empty whereas under Read-only routing list it will be

this means for NODE1\NONPROD  we will see NODE2\NONPROD & NODE1\NONPROD
and for NODE2\NONPROD it is NODE1\NONPROD and NODE2\NONPROD

once we populate the above fields we are done with our task and we can test out the configuration by running the below query.

SELECT  ag.name AS [Availability Group]
       ,ar.replica_server_name AS [When Primary Replica Is]
       ,rl.routing_priority AS [Routing Priority]
       ,CASE
            WHEN ar.replica_server_name = ar2.replica_server_name
                THEN 'Read-Only on primary' + '('+ ar.replica_server_name + ')'
            ELSE ar2.replica_server_name
        END AS [Read-Only Routed To]
       ,ar.secondary_role_allow_connections_desc
       ,ar2.read_only_routing_url
FROM    sys.availability_read_only_routing_lists rl
        INNER JOIN sys.availability_replicas ar
            ON rl.replica_id = ar.replica_id
        INNER JOIN sys.availability_replicas ar2
            ON rl.read_only_replica_id = ar2.replica_id
        INNER JOIN sys.availability_groups ag
            ON ar.group_id = ag.group_id
ORDER BY ag.name
       ,ar.replica_server_name
       ,rl.routing_priority

NOTE: I really find hard time when configuring this as I made a small mistake and kept on getting the below error while testing the Application Intent Readonly.

Could you try & let me know under comments if you get the same error?

Now  as mentioned above the interesting scenario is  why we have 2 options under connections in primary role. In what way will it make any difference?

when we configure the default option ALLOW ALL CONNECTIONS and if you fire any select statement(provided that you configured Routing) like the below while connecting with LISTENER you will be redirected to primary server. This can be checked with select @@SERVERNAME

use test
select top 5  * from test

select @@servername

When we configure only ALLOW READ/WRITE CONNECTIONS and perform the same test you will get the below error

This happens irrespective of your Readable secondary settings (NO,READ-INTENT ONLY and YES).

So I left this to readers and please feel free to share your comments.

I have 2 questions one why we got the Login timed out expired(image shown above) and the Error message 979 when we operate connections in Primary role under Allow read/write connections

When I left the above questions to the readers few people pinged me and shared their answers.

However many of them are not correct hence I want to share them.

Question 1: Login timed out expired while connecting to secondary?

This happens if you don't specify the right ports. In my case I was using named instance and didn't assign static port either so it was configured with dynamic port.

your FQDN should be TCP:// IPaddress: [YOUR INSTANCE PORT NO]  (NOT 1433 or 5022)

Question 2: Target Database unavailable for reading and getting failed with MSG 979 when you configure your primary replica settings as ALLOW READ/WRITE CONNECTIONS irrespective of settings for the secondary replica(NO,READ-INTENT ONLY and YES). when we  specify  APPLICATIONINTENT=READONLY

To overcome this you should use default database as one of the ALWAYS ON configured databases while connecting to SSMS

If you still have questions you can reach me on chiranjeevivamsy@gmail.com.

Have a Nice Weekend friends.