MSSQL 2012 HA Group DNS Issue

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.

Problem Description

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.

Solution

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.

Explanation

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.