Scenario & Environment
Multi-Subnet MSSQL 2012 Cluster with HA Group, spanned across 3 different sites. Site A and Site B are in Synchronous Commit mode, Site C is in Asynchronous Commit Mode. Operating System is Server 2012 R2. SQL Network Name (common DNS name for cluster) is vcl-sqllistener.maaz.local. Site A Node IP Address is 10.148.1.27, Site B Node IP Address is 10.149.1.27, and Site C Node IP Address is 10.158.16.27.
Ideally, vcl-sqllistener.maaz.local should be resolved to 10.148.1.27 when Node 1 is active, should be resolved to 10.149.1.27 when Node B is active whereas it should be resolved to 10.158.16.27 when Node C is active. Assuming that Site A/Node 1 is active, the DNS was not always being resolved into 10.148.1.27. Instead, it was being resolved into alternate IPs each time, i.e. for first ping Node 1 was being resolved, for second ping Node 2 was being resolved and for third ping, Node 3 was being resolved. When DNS entries for Forward lookup zones was checked there were 3 entries created. At first I though it is DNS Server’s maneuver for some reason however, I was wrong.
Open Powershell with highest privilege on any of the SQL Node (or even any other server on same domain) and apply this command:
Get-ClusterResource "VCL-SQLListener" | Set-ClusterParameter RegisterAllProvidersIP 0
where VCL-SQLListener is your SQL Network Name.
SQL Server HA Group (I believe when created and hence by default) assumes that your application is smart enough to try resolving into correct IP and remember the current HA state of the cluster. Thus, it is set to “RegisterAllProvidersIP 1” parameter. This makes cluster creating DNS entry for all nodes in the DNS Server. The Powershell command mentioned up there will set this to false and will create only one entry for active node only.
Read this great article for more details.