Sql-server – AG listener issues with ApplicationIntent=ReadOnly

availability-groupslistenersql serversql-server-2016

Need help in resolving a specific issue connect AG listener with ApplicationIntent=ReadOnly. We have 3-node cluster with AlwaysOn AG and synchronous replica. All 3-nodes are in same data center (Rackspace Dallas). I have verified that:

We are connecting to Listener Name.
The Read-Only routing list (READ_ONLY_ROUTING_LIST) is defined.
The Routing URL (READ_ONLY_ROUTING_URL) of each instance has a proper FQDN and port combination.
ApplicationIntent is specified in the connection string.
The Sync_State is SYNCHRONIZED or SYNCHORNIZING for the secondary replica.
The secondary replicas are set to allow connections.
The initial catalogue is provided in connection string.

The AG listener with read-only intent connections works fine if I RDP into the servers and connect from any one node to the AG listener via SSMS. We host our servers in Rackspace. However, if I use the outside network (from my office location) to connect to AG listener (using public facing IP), the regular connection works fine but not the readonly intent ones.

Ruled out drivers and SSMS version issues as I checked this on SQL server 2008 R2, 2012 and 2016 with respective SSMS versions. .NET run time on all machine is 4.0.3+ so that is not an issue.

All 3-nodes are connected via a private switch for replication (192.168.5.x subnet), and all 3-nodes are accessible via public facing IP (192.168.99.x subnet through firewall) as well. Can access all of the nodes via respective IPs from our office location (outside Rackspace). The AG listener is configured to respond on (192.168.5.x and (192.168.99.x) for redundancy. Can ping one node to another via HostName, IP and FQDN as well.

The only thing that is missing is a DNS entry for the AG listener, which I am going to create to rule out the issue. However, I am connecting using IP address from our office location to the data center.

Best Answer

The reason that this fails outside of the internal Rackspace location is due to the URL Endpoints being set to a value that is not able to be connected through from your local environment.

I discuss this process at a high level in this blog post, however to quickly recap the point that needs to be made here:

The endpoint url is the address where the connection will be routed in order to connect and run their queries. [roughly speaking]

The URL is active directory specific FQDN, so I cannot use it as is from ourside the the racksapce domain. The ROR URL is some thing like "tcp://1234-db1.abc.intensive.int:1433"

This means that the client driver is going to be re-pointed to this address; if the address is not reachable then the driver won't be able to connect and you'll have a problem - which you happened to run into.

In your example, the client driver will get back "tcp://1234-db1.abc.intensive.int:1433" and attempt to connect to it like it would any other instance of SQL Server. Since it can't (as you've stated) you won't be able to be routed and your connection should then be on the primary. What I did was I changed the ROR URL for each node to public IP address instead of the FQDN/ Host-name, so when the request is sent to AG listener from outside it knows where to hand-off in the public facing IP. It looks like, if we have to connect from outside I have to use public IP (Eg: 72.32.XX.XX) as opposed to tcp://1234-db1.abc.intensive.int:1433 in the ROR.

While the IP may or may not need to be public (different architectures at different companies' may or may not require different things), it seems in your case it does... though I'm not a networking architecture guru so I can't comment on how/why this was implemented or any technical challenges observed.

Also when we deploy the application in the same domain as the db servers Active Directory domain "abc.intensive.int" would that pose a problem when apps try to connect to the db AG listener?

The domain shouldn't make a difference here, the real difference is whether or not your DNS is setup where it can do the lookups required to get the information (IIRC this would be a reverse lookup zone but don't quote me on that). If you can resolve the DNS name, it will work fine, if you can't it obviously won't... this is more of a question for your DNS/AD admins as they hold all the information to that kingdom.

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.

Sql-server – Availability Group: ReadIntent connection failing

Script to create read only rounting:

set up read-only intent routing

Test it:

sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername" -Kreadonly 
sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername"

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com \instance'
with (SECONDARY_ROLE(ALLOW_CONNECTIONS = all));-- not read_only

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com\instance'
with (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'tcp://node1.DevDomain.com:49598'));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instance'
with (SECONDARY_ROLE(ALLOW_CONNECTIONS = all));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instance'
with (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'tcp://node2.DevDomain.com\instance:49877'));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com\instance'
with (
        PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (
                'node1.DevDomain.com\instance'
                ,'node2.DevDomain.com\instance'
                ))
        );

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instanced'
with (
        PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (
                'node2.DevDomain.com\instancee'
                ,'node1.DevDomain.com\instance'
                ))
        );
go

Best Answer

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

Sql-server – Availability Group: ReadIntent connection failing

Related Question