AD DNS manager records duplication with SQL always on failover cluster

Question

Hi,

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

Answer 1

Hi @Yishai Neeman,

Thanks for your information. In a Multi-Subnet configuration, both the online and offline IP addresses of the network name will be registered at the DNS server. The client application retrieves all registered IP addresses from the DNS server and attempts to connect to the addresses either in order or in parallel. This means that client recovery time in multi-subnet failovers no longer depend on DNS update latencies. By default, the client tries the IP addresses in order.

You can optimize availability group failover to a different subnet by setting MultiSubnetFailover to “True” or “Yes”, it will try the IP address simultaneously and connects to the first server that responds, hope this article can help you understand well. If your connection doesn’t support this parameter, please check out this article.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications