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?

  • What version of SQL Server you are using? Reason I am asking, there was a change made in SQL 2016. Commented May 26, 2017 at 18:02
  • We are using 2012 SP3
    – karun_r
    Commented May 26, 2017 at 18:09
  • My impression is that, if you have properly configured your read-only routing list, your listener will route in the order that you specify ROR 1 ROR2
    – swasheck
    Commented May 26, 2017 at 18:18
  • This Microsoft Blog clearly explains how to do that using TSQL
    – Shanky
    Commented May 26, 2017 at 18:25
  • @Shanky The link explains how to configure this. Thanks.
    – karun_r
    Commented May 26, 2017 at 18:52

1 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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.