3

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. – SqlWorldWide May 26 '17 at 18:02
  • We are using 2012 SP3 – karun_r May 26 '17 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 May 26 '17 at 18:18
  • This Microsoft Blog clearly explains how to do that using TSQL – Shanky May 26 '17 at 18:25
  • @Shanky The link explains how to configure this. Thanks. – karun_r May 26 '17 at 18:52
3

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, privacy policy and cookie policy

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