0

We are managing our SQL Always On listeners records in 2 active directory servers (they are syncing each other) and we came across an issue which we couldn't pinpoint the cause for.We are testing MSSQL failovers on our windows DB servers and after the failover we check the DNS manager to see that the IP for the relevant listener changed (our DB servers are on multiple subnets).

In some cases (once every few failovers) we see that the listener record duplicates ,meaning we have 2 records for the same listener pointing to different DB servers and not 1 pointing to the primary DB as it should be.

We have checked the availability group parameters which might affect this :

PublishPTRRecords is set to true for all availability groups and for the cluster.

HostRecordTTL is set to 15 seconds.

RegisterAllProvidersIP is set to 0 to assure that only a single IP will be available for each listener.

I would like to ask your opinion regarding possible causes for this issue or a way to monitor the DNS records change process which might allow me to debug .

Thanks

enter image description here

Improve this question

2 Answers 2

Reset to default
0

RegisterAllProvidersIP is set to 0 to assure that only a single IP will be available for each listener.

This doesn't ensure only a single IP, it means it will only register the IP address that comes online in the or configuration for the network name. If you have multiple DNS server and multiple subnets then there will potentially be a different IP address registered for each combination. It's then up to DNS to propagate the changes (removal, addition, etc.) in a timely manner. There is also the chance that for whatever reason the cluster is unable to properly access DNS to make changes.

What you're seeing is expected with your setup. I'd advise you to use multisubnetfailover=true in your connection string and get rid of RegisterAllProvidersIP=0 and instead set it to 1. You will no longer have these issues.

Improve this answer
4
  • Hi Sean, regarding RegisterAllProvidersIP you are correct (i just used another way to explain it) . I still don't think this is expected with my setup since my DNS servers knows about each other and sync the records so at any given time only the IP of the online resource is showing in the DNS manager. this issue happens randomly which shows me that there is a local issue and something permanent. regarding multisubnetfailover=true we are using it in our app connection string but i would rather not force the consumer to use it because then i need to make sure each of them use it. thanks
    – Yishai
    Commented Mar 26 at 11:48
  • It's expected that it will sometimes happen. That's how asynchronous systems work together, especially when various items are at play. You might not like it, but it's a fact of the infrastructure architecture. By not using the keyword on clients, you're choosing a poor architecture and thus you'll always be in a similar situation. It's your choice, if you choose to do it the hard way, I can't stop you. @Yishai
    – Sean Gallardy
    Commented Mar 26 at 12:52
  • Thanks Sean for you help. i added my solution for the issue
    – Yishai
    Commented Mar 28 at 6:56
  • Sadly it's not a solution, it will happen again.
    – Sean Gallardy
    Commented Mar 28 at 9:39
0

I ended up finding the issue myself. I have 3 DB servers and i only configured the default DNS setting pointing to our AD servers on 2 of them so the 3rd one updated the ADs through an AWS resolver rule which i guess created some kind of race condition with DNS updates.

Once i configured the 3rd server as well the duplication didn't reproduce anymore.

Improve this answer
1
  • There is a chance it will happen again due to the settings and configuration.
    – Sean Gallardy
    Commented Mar 28 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.