I've recently taken over administration of an availability group comprising two nodes, synchronous commit mode.
The configuration of the group looks like this:
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
For anyone else needing help understanding this, I found the following helpful: