When a SQL Listener is created on a Multi Subnet environment 2 x ip addresses are assigned.
AG Name - SQLAG1
AG Listener Name - SQLAG1-LI
10.0.0.1
10.0.10.1
I have given the ip addresses above for example.
In DNS there are 2 x entries for the SQLAG1-LI one for each of the IP addresses.
A web application is a connection to the Listener but it is resolving to the incorrect IP address of the server that does not own the group.
It has always worked for me in the past so never had to question how does DNS determine how to resolve to the correct IP or rath the ip of the current owner of the group.
Configure the client connection string to include MultiSubnetFailover=True so the client will try each of the addresses.
Under the 'Multi-Subnet Failovers' heading in the doc at https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover?view=sql-server-ver15 :
This is because the client driver will attempt to open up a TCP socket for each IP address in parallel associated with the availability group. The client driver will wait for the first IP to respond with success and once it does, will then use it for the connection.
-Eddie
Eddie Wuerch
MCM: SQL
October 14, 2020 at 4:06 pm
This was a massive help thanks Eddie.
October 16, 2020 at 2:11 pm
You might also want to specify a connection timeout in your connection strings as well, if you start getting timeouts. I've had to do that as well as add the multi-subnet failover attribute. The default timeout is just barely not long enough in our situation. I don't recall if the default is 30 or 60 seconds, but in my case specifying the timeout and giving it more than 60 seconds eliminates intermittent timeouts that I was receiving.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply