Sql-server – Always On read-intent

availability-groupssql-server-2016

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

Best Answer

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

Related Question