This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(19.2, 2%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
Hello Team,
How can I connect to an Async node using the Listener IP or Listener DNS in an Always on architecture (SQL Server 2016)??
Node1 - Primary
Node2 - Secondary (Sync)
Node3 - Secondary (Async)
Our API's connect to Node1 and Node2 using a DNS listener IP and some API's connect to Node3 via a direct TCP IP,Port
Did you referred to below blog, it shows how to connect to Sec replica which is for Read only
End to End - Using a Listener to Connect to a Secondary Replica (Read-Only Routing)
Hello,
Thanks for you answer, however what if Node2 is Sync and Node3 is Async (possibly behind Node1 and node2). Can we still configure it in the same way??
for ex:
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'Node1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Node2'), ('Node3')));
GO
Will it load balance? or the read only queries will go to Node3 only in case of the Node2 goes down?
@Ashif Shaikh Yes you can do that
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'Node1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Node2'), ('Node3'))); GO. The nodes should be server names hosting sec replica. The replica will be directed in the order the name is given. MS Doc has these details. Quoting
READ_ONLY_ROUTING_LIST = { ('<server_instance>' [ ,...n ] ) | NONE }
Specifies a comma-separated list of server instances that host availability replicas for this availability group that meet the following requirements when running under the secondary role:
The built-in capabilities in SQL Server 2016 (and higher) load balancing of read-only routing will only function as a typical round-robin load balancing algorithm. If you want your other APIs to connect specifically to Node3, this has to be implemented on the network layer, filtering out the source connection (those coming from the other APIs) and redirecting them directly to Node3.
But you also need to modify your DR process in case Node3 becomes a primary replica.
Thanks, could you please provide some more details on how exactly I can proceed with it??
This is dependent on your network devices. Talk to your network engineers because they will be the ones who will implement this.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 78%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECZ%3C/text%3E%3C/svg%3E)
Hi,
If the application's read operation cannot tolerate any degree of data lag, the read operation can only be run on the primary replica.
In SQL Server 2012 and 2014 versions, the read-only workload redirection process will redirect the queries specifically to the first secondary replica defined in the routing list, unless it was not accessible, and then it will direct the connection to the next replica in the routing list. SQL Server 2016 removes this limitation by introducing the native load balancing mechanism. More details please refer this article.
https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/