2

I inherited a few SQL Servers (2014) with AlwaysOn AGs on AWS. I am totally new to this and we are talking live production environments. 2 different setups. Both have 2 servers each with one AG, so pretty simple. However, one set has a listener created and the other doesn't.

The one that doesn't have a listener is working fine. The one with the listener has issues at failover.

The GIS application accessing this server cannot connect to it after a failover. Once we failed back over to the original primary, it worked fine again. It doesn't use the listener to connect, instead it uses the cluster name.

I tried to have them use the listener but they said they couldn't even connect to that. I assume the listener hasn't been set up correctly.

Can I just remove the listener since it is not used anyways?

But everything I read says that you need to use a listener, yet the servers without a listener are working fine. I'm so confused.

Can someone point me in the right direction, please?

  • The listener is the correct approach, but it's real function is to be able to direct intent read only connections to the proper secondary. Are you using multiple subnets? Is the listener available to ANY machine other than local to the cluster? – Jonathan Fite Mar 9 '17 at 19:59
  • I'd disagree with your assessment on the function for listeners there @JonathanFite. Rather it is primarly used to allow transparent redirection of traffic in the event of a failover, as well as allowing for read traffic redirection (provided read routing has been configured in SQL and the app passes along the correct connection string). – Nic Mar 9 '17 at 20:38
  • @Nic, I'm not saying that it shouldn't be setup, but the Cluster Name provides the same seamless redirection to the primary node as the listener does. The only thing it does differently is that it can redirect based on read-only. During a failover, you have the same need for the app to wait and retry it's connection. – Jonathan Fite Mar 9 '17 at 21:03
  • @Jonathan-Fite, yes, there are 2 subnets. I'm not sure if the listener is available to other machines. The environment is set up highly secure; I can only access the databases by RDPing into the servers (from my laptop only). Users can only access them through specifically configured AWS workspaces (which I'm not familiar with). How do I check? – Susie Mar 9 '17 at 21:15
  • @JonathanFite I responded to your comments, you'll want to read the answer I provided. – Sean Gallardy - Retired User Mar 10 '17 at 15:51
2

However, one set has a listener created and the other doesn't.

Well that doesn't help you application stay available! You can absolutely have an availability group without a listener, but the useful of such setups are low.

(From Comments) The listener is the correct approach, but it's real function is to be able to direct intent read only connections to the proper secondary.

That's not quite correct. The listener always points to the primary replica and the main purpose of it is to facilitate transparent failover so that the connection strings do not need to change. One additional use is in read only routing as you've said but the primary use is actually for transparent failover.

(From Comments) ... but the Cluster Name provides the same seamless redirection to the primary node as the listener does.

The CNO has no idea about what an availability group is, which should be primary, etc. The CNO absolutely DOES NOT always point to the primary replica. You can connect using it only when the core cluster resources (which holds the CNO) happens to be owned by the same node as the AG primary but if it isn't - and the CNO doesn't follow the AG around - then it'll fail. Here's an example: CNO on SQL2016AGN3 and AG on SQL2016AGN1

The one that doesn't have a listener is working fine. The one with the listener has issues at failover.

The one without the listener might be working fine... does it still work fine on a failover? Are they using some other DNS alias to "act" like the listener may.

The one with the listener, what does their connection string look like? If it isn't using the listener name in the connection string then of course it isn't going to work... they need to point it to the right place, that's why the listener exists!

The connection to the listener may also fail if there are multiple subnets, older client libraries are used, or certain keywords aren't in the connection string. If there are multiple subnets, the client driver should be something that supports the MultiSubnetFailover keyword and this should be set to TRUE.

It doesn't use the listener to connect, instead it uses the cluster name.

Well, that's 50% of the problem.

The GIS application accessing this server cannot connect to it after a failover.

If they update their connection string to use the listener and set MultiSubnetFailover = True then I'm betting it'll work... assuming the client library used to connect supports it.

I tried to have them use the listener but they said they couldn't even connect to that. I assume the listener hasn't been set up correctly.

If SQL Server created the listener for you, I highly doubt it is setup incorrectly... I won't rule out edge cases though.

Can I just remove the listener since it is not used anyways?

I would do the opposite. Have the GIS team change their connection string to the listener and set multisubnetfailover. It should, then, work... again with previously said assumptions.

But everything I read says that you need to use a listener, yet the servers without a listener are working fine. I'm so confused.

Yes, you do want to use the listener. The fact that it is working is either because there is some document that says "always have this on node3" or you've been getting lucky that it stays working. They might have used the replica name directly in the connection string also... that would allow it to connect. Depending on the settings for the secondary role it may or may not still connect and work properly... just depends on said settings.

| improve this answer | |
  • Thank you so much for the thorough explanation! And yes, the problem we were having is that the cluster was pointing at the secondary. It makes sense. I guess we have been lucky with the other servers. I'll go ahead and create a listener on that cluster too. – Susie Mar 10 '17 at 15:22

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.