I've recently taken over administration of an availability group comprising two nodes, synchronous commit mode.
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",
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