Does SQL Server route connections to a secondary replica which has commit mode as synchronous in case of an AG with multiple secondary nodes?

Question

If I have an AG with 4 different nodes as below:

1. Primary Node
2. Secondary Node 1 - Asynchronous Commit - Read Only Yes
3. Secondary Node 2 - Synchronous Commit - Read Only No
4. Secondary Node 3 - Synchronous Commit - Read Only Yes

In this case, if a connection to secondary node is required from an application, will the SQL Server route it to Node 1 or Node 3 ? Node 2 is out of question because it's Read Only is set to NO, isn't it ?

Ideally it should be routed to Node 3 because it has synchronous commit and data will be more consistent with the primary. Is that's what will happen here?

Answer 1

It will depend on your routing list.

SQL Server 2104 and SQL 2012 read-only routing directed traffic to the first available replica in the routing list, unless it was not accessible, and then it would direct the connection to the next replica in the routing list. When you have multiple secondary replicas available for read, it is not possible to spread the read load across those replicas.

In SQL Server 2016 you can configure load-balancing across a set of read-only replicas.

Details Here: https://blogs.msdn.microsoft.com/alwaysonpro/2016/05/02/sql-server-2016-alwayson-availability-group-enhancements-load-balance-read-only-routing/