2

As per my understanding when all the secondary read only replicas are shut down, the primary replica is the one that will take all the read write requests and read-only requests as well.

But when I was validating this scenario yesterday, my primary instance didn't take read-only requests from my application server; however, it did take write requests.

My question here is: Will the primary take read-only requests whenever read-only secondaries are not available?

This is an Always On 3-node environment. The configuration properties are:

As shown, all nodes are configured to allow all connections when they are primary.

For all read requests, we are using the listener name and application intent= read only. For all write requests we use the listener name. We are not using the node name explicitly anywhere. I just tested in my acceptance environment with my application.

migrated from stackoverflow.com Aug 4 '17 at 13:51

This question came from our site for professional and enthusiast programmers.

2

Make sure you include the primary in the read_only_routing_list

ALTER AVAILABILITY GROUP  aggroup
MODIFY REPLICA ON 'node1'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST= ('node2','node3','node1')
)
); 

Do the same for the other 2 nodes. and you can test using this query

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
1

As per my understanding when all the secondary read only replicas are shut down primary replica is the one which will take all the read write requests and READ ONLY REQUESTS as well.

Correct.

But when I am validating this scenario yesterday my primary instance didn't take read only requests from my application server, however it took write requests.

You should have a stern talking to it then. In all seriousness, we'll need to see your testing setup as I can only reproduce this in scenarios where I purposely mess up DNS.

So my question here is will primary takes read only requests whenever read only secondaries are not available??

Yes, it should. We'd need a network trace to really dig down deep (from the app and the server side) and see what's happening if you're not having this behavior.

The main point is that even when we use read only routing, the client will always contact the primary first. Period. The only time this wouldn't happen is if the client connection string pointed specifically to a node name instead of the listener name. Assuming that isn't the case (you made no mention if you're using node or listener name or how the test was run [could you add that please?]) it will always connect to the primary first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.