4

I'm experimenting with creating a 2-node SQL Server Availability Group following Guy Glantser's excellent guide at MadeiraData where he describes a configuration with no AD domain or internal DNS. Note that I'm building everything on AWS EC2 instances. Everything is working well (including the certificates and logins, the AG is fine and can fail over successfully) however I cannot get the AG Listener to function properly.

Guy's instructions do not mention adding a reference to the Listener in the HOSTS files, however, I have done so.

I can connect to the Listener via the name OR its IP address ONLY from whichever is the Primary AG node. I cannot connect to the Listener (on an unused IP address and listening on port 1433) from another node or another machine - timeout. When I failover the AG, I can then only connect to the Listener on the "current" Primary, and the "former" Primary cannot reach it anymore. I've been testing the connections with SSMS, UDL files and telnet to port 1433.

Am I missing something fundamental about AGs, or as I suspect, am I running into an issue with the Listener and DNS/IP addressing inside an AWS VPC?

I'm running Windows Server 2016 Datacenter edition AMI instances and SQL Server 2016 Evaluation Edition SP1.

1

Can you please review the AWS documentation on how to properly set up AG on their environment? http://docs.aws.amazon.com/quickstart/latest/sql/part3.html

There are some specific steps that need to be done for this to work. You may also want to read about the MultisubnetFailover (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery) as without using this feature it may take several minutes (depending on how much you set up your HostRecordTTL setting) for the DNS setting to be updated and the listener name start pointing to the new active node IP. On my tests, without changing the HostRecordTTL and the multisubnetfailover it was taking up to 15 minutes for the listener name to start working again after a failover so I had to play with these parameters to have something that would work for all my client applications

  • Thanks for the reply, and I have spent time on that first document you listed, but note the scenario I'm testing involves no domain controllers, no AD and no DNS servers. Only local HOSTS files. I'm testing potential solutions for a client of mine who has built a multi-region topology in AWS including multiple SQL Server instances but without ANY domain controller presence. – ShamusD Jun 27 '17 at 15:27
  • 1
    Yeah, but the concept is the same. If you look at the Windows cluster service you will notice that you have the 2 IP addresses from 2 different subnets there if this was set up the way AWS recommends on that page. Only 1 IP is up at a time. when a failover occurs one IP goes down and the other up. in your host file, you should add both IP's to the listener name, you can test using NSLOOKUP that you are returning 2 IP's for that name. Then connect using the MultisubnetFailover=true. SSMS/client will try the first IP upon failure will try the second which should work. – Felipe Ferreira Jun 27 '17 at 15:49
  • Unfortunately I'm seeing the same behaviour with 2 subnets, 2 listener IP addresses, 2 listener entries in all HOSTS files and the MultisubnetFailover setting. The only node that can connect to the listener is whatever the Primary node for the AG is at the time. NSLOOKUP doesn't seem to consider HOSTS files, however I can ping the listener and it successfully resolves to a HOSTS entry. – ShamusD Jun 27 '17 at 18:29

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.