3

I've recently taken over administration of an availability group comprising two nodes, synchronous commit mode.

The configuration of the group looks like this: Availability group properties

My understanding is that, with the Readable Secondary option set to Yes on both replicas, any connection string with applicationIntent=ReadOnly will route to Node1.

Likewise, if I change Node2's Readable Secondary option to 'Read-intent', any connection string with applicationIntent=ReadOnly will route to Node2.

Why then, does this connection string route to Node2 when both nodes are set to 'Readable Secondary = Yes':

'Data Source=redacted.domain.com; Initial Catalog= MyDatabase; ApplicationIntent=ReadOnly; User Id=User; Password=********; MultiSubnetFailover=True'

Basically, changing the 'ReadOnly' parameter to 'ReadWrite' causes the connection to go to Node1. Changing back to 'ReadOnly' causes the connection to route to Node2. How is this possible without the 'Readable Secondary= Read-intent' option in place?

EDIT: output of SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is", rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO 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

output

For anyone else needing help understanding this, I found the following helpful: enter image description here

1 Answer 1

5

My understanding is that, with the Readable Secondary option set to Yes on both replicas, any connection string with applicationIntent=ReadOnly will route to Node1

No it should first go to node 2 because the Node 2 is read only replica and Node 1 is primary replica which will server to read write operations. As per BOL

Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role)

What is output of

SELECT ag.name as "Availability Group", ar.replica_server_name as "When 
  Primary Replica Is",
rl.routing_priority as "Routing Priority", 
ar2.replica_server_name as "RO 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

So from output you can see when Node 1 is primary replica read only connections will go to Node 2 as first preference and vice versa

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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