Home Dashboard Directory Help

Listener for ReadOnlyRouting by PrinceLucifer



Sign in
to vote
Type: Suggestion
ID: 772204
Opened: 11/26/2012 3:07:00 AM
Access Restriction: Public


Consider this scenario:
You have an AG with two replicas, one primary, one for ReadOnly access.
Your clients are bound to only have access to specific IP ranges.
e.g. Server A has IP Server B has, the VNN for the AG is set to, all in the same /23 subnet. The clients by firewall rules only allowed to access 10.1.2.x IP addresses.
Now that scenario works perfect for standard access via the VNN, as you can specify any IP there that the cluster can bring online. Unfortunately you can't do the same for the ROR partner.

The workaround currently is to assign each server a static 10.1.2.x IP address, which could pose several problems along the way.
Sign in to post a comment.
Posted by USXrsloan on 5/2/2014 at 7:47 AM
SAy you have 3 SQL servers in an AG, SQL1, SQL2 and SQL3
SQL1 is Primary, SQL2 and SQL3 read-only secondary that accepts all connections.
There is a listener with DNS name SQL-Prod that the applications connect to.
There are also several report users. They need to connect to SQL2 and fail over to SQL3.

Due to the large number of users changing the connection strings to ApplicationIntent=Readonly is unfeasible or, for older applications, not supported.

What the original poster and myself are requesting is that you add the ability to create a Read Only Listener, that will have its own IP address. The only difference between this and a regular listener is that the users that connect to the Read Only listener will be redirected to the first secondary node set up in read-only routing instead of the primary. If the node goes offline then the clients will be redirected to the other nodes.

I am surprised more people have not requested this. It would make using the read-only secondaries easier to use as reporting servers.
Posted by PrinceLucifer on 11/29/2012 at 10:10 PM
Right now ROR only supports one secondary anyway. (All apps that connect with ApplicationIntent=ReadOnly will end up on the same node...) That one should hold the listener
Posted by Microsoft on 11/29/2012 at 9:55 PM
Thanks for the suggestion.

Notice that there can be multiple readable secondaries, should the ReadOnly Listener bind to any of them (e.g. randomly)?

Is the problem with using ReadOnly Intent and the AG Listener that read-only connections are routed to the first available secondary (not load balanced) or something else?
Sign in to post a workaround.