July 30, 2013 at 1:03 pm
Hello,
Does anyone know if SSMS is able to use the MultiSubnetFailover parameter to connect to an AG listener?
I tried listing it in the advanced connection properties and it didn't work (Management Studio version 11.0.2100.60). We have a multisubnet availablity group with a listener and after much effort, we are able to connect via SQLCMD with the -M and -l30 flags, but I can't connect to the server via the listener name through management studio.
I can connect to the individual servers, but the listener name times out even if I set the connection timeout to 60 seconds. It seems odd that SSMS wouldn't support the new connection parameters.
Thanks!
Jason
October 19, 2013 at 4:55 pm
Great question. I am trying to accomplish the same thing.
EDIT:
In my case, I had changed the RegisterAllProvidersIP setting and then restarted the listener and found later that it was not connecting. After restarting the AG, I was able to connect through SSMS.
Oops
October 19, 2013 at 5:20 pm
Now it is back to not working again with listener name.
Connected through SSMS to the active listener IP and it succeeded.
March 21, 2014 at 9:41 am
I am in the same boat, Using Management Studio to connect on multi subnet AG listener works 50-50.
Is there any solution for this?
May 9, 2014 at 1:32 am
Have the same problem with connecting via SSMS across a Availibility Group Multi-Subnet , works randomly , has anyone a fix
July 2, 2014 at 5:14 am
I successfully connected to a AG on a multisubnet using the multisubnetfailover=true connection string parameter on "Additional Connection Parameters" tab. Unfortunately this tab is only available if you make a new connection using "File > Connect Object Explorer", I can't persist this option in the registered servers, at least with the Sql Server 2012, thus making it difficult to create new connections.
If anyone bypass this restriction, could you share how?
Thank you
July 7, 2014 at 3:45 am
I had some of these problems when we initially set up our SQL AG. We have a 3 node configuration - two on one subnet (a primary with a secondary readable), the third on a DR subnet.
When testing fail over to the DR subnet we had all sorts of problems connecting to the SQL AG through the listener. The problem turned out to be related to the DNS registration of the listener after fail over.
I ran Windows Cluster validation which flagged problems with the HostRecordTTL and RegisterAllProvidersIP settings. These settings need to be changed from their defaults to support multi-subnet clusters. The HostRecordTTL (time to live) is important because your client uses this value to know when to get a fresh copy of the DNS entry rather than using what is in cache.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply