August 17, 2018 at 4:13 pm
Hello
We have a situation where we need to setup AlwaysOn configuration on 2 data centers
When I create a Listener providing 2 IP addresses of the subnets, there are 2 DNS records created for both Primary and Secondary.
is it normal to create 2 DNS records?
when pinging or connecting the application should connect to the Primary replica, but sometimes it just routes to the secondary replica which we are not ok with.
is it ok to delete the DNS records of the secondary replica from DNS manager, what effects do we have during the failover?
Any help is appreciated.
Thanks.
August 19, 2018 at 7:29 am
Yes, that is normal. I believe by design AlwaysOn will scan one DNS entry at a time to determine what is active.
Do you mean 2 IP addresses per primary and secondary? I believe this is to account for the multi-subnet capability that you are trying to do.
There is a flag that was new with SQL Native Client Driver 12 called "multisubnetfailover", which will direct the application to scan both DNS entries in parallel. Setting this to "yes" on your app connection string may resolve your issues.
Read up on it here:
Also, off topic - make sure your cluster level timeouts are configured per Microsoft recommendations. In the past I've run into weird AlwaysOn errors that, in the end, had to do with our cluster configuration and not our alwayson configuration. This is VERY IMPORTANT for multisubnet AG configurations.
August 19, 2018 at 10:56 am
We have a requirement that we are not allowed to make any changes in the application connection string.
Does setting RegisterAllProvidersIP to 0 similar to MultiSubnetFailover=True?
August 19, 2018 at 10:59 am
I don't believe so. Unfortunately I believe this is the only workaround to the behavior. You could try though, I have no experience with the "RegisterAllProvidersIP" flag.
You could however create a powershell script and configure it as a "failover resource." In the event of an AG failover, you can actually program something at the cluster level to run. I had to do this before because I once worked with a company that implemented multisubnet AG's but also had replicated domain controllers that replicated in a 15 minute interval. Problem we had was the applications on the domain controller that was not yet updated weren't able to connect until the DNS replicated, so I had to create a powershell script that would automatically make the DNS changes on the non replicated DNS server to fix the issue.
I imagine you would have to go a similar route here, but I would ofcourse test it.
September 6, 2018 at 5:56 am
SQLAddict01 - Sunday, August 19, 2018 10:56 AMWe have a requirement that we are not allowed to make any changes in the application connection string.Does setting RegisterAllProvidersIP to 0 similar to MultiSubnetFailover=True?
No theyre not similar.
RegisterAllProvidersIP is a cluster resource setting that dictates whether upon failover one or all ips are registered.
This should be set to 0
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 7, 2018 at 3:18 am
If I understand you correctly, after setting up AlwaysOn you have two aliases of the same name, one pointing to the local subnet, one pointing to secondary subnet? This happened in my environment as well, because we set up a multi-subnet AG. In our case though, if routed to the secondary IP (DNS will round robin if there are two DNS entries of the same name) the connection would fail because the secondary IP is offline (which makes sense as you are only ever active in one). To get around this problem we deleted the secondary alias. This is fine for us because we are running in a asynchronous failover mode, so in the evnt of a failure, we would manually change the DNS alias to point to the right IP/subnet.
Hope this helps.
September 9, 2018 at 8:26 pm
SQLAddict01 - Friday, August 17, 2018 4:13 PMHelloWe have a situation where we need to setup AlwaysOn configuration on 2 data centers
When I create a Listener providing 2 IP addresses of the subnets, there are 2 DNS records created for both Primary and Secondary.
is it normal to create 2 DNS records?
when pinging or connecting the application should connect to the Primary replica, but sometimes it just routes to the secondary replica which we are not ok with.
is it ok to delete the DNS records of the secondary replica from DNS manager, what effects do we have during the failover?
Any help is appreciated.
Thanks.
If you do nslookup on your listener you should get two IP's one from each subnet. Now it depends how your AD and DNS are set. If the CNO has the capability to update DNS on adhoc basis i.e when a failover happens it should have enough permissions to update DNS record for the listener for 2nd ip then you shouldn't have to change any applications or connection strings as long as they are connect to the DNS alias or listener. If the permissions are not set right , your best bet is to get secondary ip removed from the listener ( at that point nslookup should return only 1 ip) and manually update DNS incase of failover. Hope this helps.
September 10, 2018 at 8:47 am
>> Does setting RegisterAllProvidersIP to 0 similar to MultiSubnetFailover=True <<
I think it's the opposite. If your clients don't default to MultiSubnetFailover=True, and you cannot change connection strings, then I think right answer is to set RegisterAllProvidersIP = 0 on your listeners. Pretty sure this will require you take the AG offline briefly and then back online for the change to take. Our AGs now have two cross-subnet listeners: one configured with RegisterAllProvidersIP = 1 for newer clients and one with RegisterAllProvidersIP = 0 for legacy clients. It is working ok.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply