SQL Server 2012 Multi-Subnet Cluster + SQL HA setup + adding nodes

SQL Server 2012 Multi-Subnet Cluster

Powershell commands

#List different cluster resources
PS C:\>Get-ClusterResource | Select Name, ResourceType
#List parameters and their values of the SQL Server Network name
PS C:\>Get-ClusterResource "SQL Network Name (SQLMULTISUBCLUS)" | Get-ClusterParameter
#Set parameter value
PS C:\>Get-ClusterResource "SQL Network Name (SQLMULTISUBCLUS)" | Set-ClusterParameter HostRecordTTL 300

The first line is simply to retrieve all of the cluster resource names in the cluster. We want to know the cluster resource name of the virtual server name of the SQL Server instance, in this case, SQL Server (MSSQLSERVER). The next line simply grabs all the cluster resource parameters for the cluster resource named SQL Server (MSSQLSERVER). The parameter that we want to modify is HostRecordTTL. As I've previously mentioned, the default value is 1200 seconds. The third line simply changes the HostRecordTTL parameter value to 300.

Once the cluster parameter value has been changed, the client applications will no longer have to wait for 20 minutes in order to get the name resolution correctly and, thus, minimizing "perceived" downtime.

Another thing that we need to look into is how the virtual server name gets registered in the DNS server. Microsoft DNS is usually implemented with Active Directory integration which means that an update in the DNS made in one Active Directory site in a subnet may take a while to replicate to another Active Directory-integrated DNS server on another site with a different subnet. This can be addressed by registering all of the virtual IP addresses for the virtual server name on all of the DNS servers even when the virtual IP address is not online. This gives the DNS client an opportunity to store all of the possible IP addresses for a virtual server name. However, this assumes that the client application is smart enough to retry a connection against all available IP addresses for a given virtual server name. The default behavior of a cluster resource name is to only register the virtual IP address that successfully comes online. In our example, the virtual IP address 172.16.0.113 will only be registered in the DNS server within that subnet when it successfully goes online. The same thing is true with the virtual IP address 192.168.0.113. This results in having just one entry per DNS server per subnet for the virtual server name instead of two. To modify this behavior, run this code:


#List parameters and their values of the SQL Server Network name
PS C:\>Get-ClusterResource "SQL Network Name (SQLMULTISUBCLUS)" | Get-ClusterParameter
#Set parameter value
PS C:\>Get-ClusterResource "SQL Network Name (SQLMULTISUBCLUS)" | Set-ClusterParameter RegisterAllProvidersIP 1