If I understand your question correctly, you wanted to load-balance between primary and secondary replicas for connections with read-only intent.
By default, for connections with ApplicationIntent=ReadOnly
parameter are routed to readable secondary replicas.
The way connections are routed to readable secondary replicas are defined at Read-Only Routing List based on their priority.
I have altered my AG Routing list for the above requirement and it is working fine:
USE [master]
GO
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'NODE1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'NODE2',N'NODE1')))
GO
NODE1 is my primary replica and NODE2 is secondary replica. The connection string contains the following connection parameters:
ApplicationIntent=ReadOnly;Initial Catalog = TestDB;
Note: By default, connections are not load-balanced between NODE2 and NODE1, even if you specify load-balancing on read-only routing list. This works, only when, NODE2 is down and it will look for next secondary replica with highest priority.
So, as it is working based on routing list, if first node is not available, connection will be routed to next node. Load balancing between, secondary and primary is not working, even if configured as a load-balancer format in routing list as long as NODE2 is up and running.
To load-balance between NODE2 and NODE3 (both are readable secondary replicas), alter the AG as follows:
USE [master]
GO
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'NODE1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'NODE2',N'NODE3'), NODE1))
GO
In this case, it will load-balance between, NODE2 and NODE3 and if both the nodes are not available, it will route to NODE1 (primary replica
).
I hope this is helpful to you, check my page for understanding read-only routing.