0

We have a SQL Server setup with Availability Groups, in the cluster we have four nodes, two in one datacenter and two in another datacenter.

There are four AGs on these cluster nodes, each primarily on one of the four nodes, with one other node in the same datacenter for synchronous HA and the other two for asynchronous in the other datacenter for DR.

We have one listener for each AG.

One AG seems to automatically failover at certain times due to various Network issues, however the AG seems to failover fine - the HA secondary becomes primary and all databases come online however applications seem unable to connect and we see various connection error type messages such as,

Cannot connect to SQL Server instance 'HH-SQL-D11' :

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 -

The semaphore timeout period has expired.) : The semaphore timeout period has expired [121] (requires acknowledgement)

and

named pipes provider error 40 the network path was not found

Yet when we failover manually the connection is fine and works as expected. Other AGs on the cluster have automatically failed over and have worked fine. I have checked various things like the SQL Instance is up, TCP/IP and Named pipes enabled, remote connections enabled. I have asked the networks team to check things like firewalls and they say they are all ok.

I have tried to read the cluster logs but I am not sure what to look for in here and I am at a lost as to what to look at to try and fix this issue. Its a problem as it means that HA is not operating and if it does failover automatically applications connecting will not work.

  • What about you ? After that automatic failover, can you connect in SSMS using the listener ? – Dominique Boucher Jul 20 at 17:05
  • I cannot connect to the listener using SSMS or even ping it or its IP address through a cmd prompt. Weirdly though it seems as if my monitoring tool (Spotlight) can connect. – Garry Nov 9 at 15:17
0

If you cannot reach the listener from SSMS, then it seems to be an issue with networking or name resolution (DNS).

You can try do to a NSLOOKUP of your listener and make sure it point at the primary node. If not, it means you have an issue with DNS update. Your windows admin should be able to help you.

If it points to the right server, but you cannot ping it or connect to it, then I would provide that information to the network team, asking them to assist with this specific situation.

| improve this answer | |

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.