There are things which sometime we only learn from experience and this is one of those things. Recently I was involved in the designing and implementation of SQL Server 2014 AAG DR solution for one of the client and issue encountered when client was not able to connect to SQL Server via Listener name,
It was found that just setting multisubnetfailover paramater value "true" was not sufficient and there is something more to it. Basically legacy applications sometime do not support multisubnetfailover and to make sure they work with AAG Listener, we need to look at few things
You need to use powershell to amend these settings
1. Make sure below parameters are set rightly for AlwaysOn listener
Import-Module FailoverClusters
Start-ClusterResource <LISTENER RESOURCE NAME>
HostRecordTTL 300
RegisterAllProvidersIP 0
Import-Module FailoverClusters
Get-ClusterResource <LISTENER RESOURCE NAME>| Get-ClusterParameter
2. When RegisterAllProvidersIP is set to 1 (default value), two subnet entries will be created in the DNS.Client need to use the MultiSubnetFailover = true property
Please note RegisterAllProvidersIP value need to be set to 0 for the legacy application which does not support multisubnetfailover property.For example, there are cases where excel 2010 is used and that is why this value need to be adjusted to 0 for this environment.
For latest application, we should leave the RegisterAllProvidersIP value to 1
It was found that just setting multisubnetfailover paramater value "true" was not sufficient and there is something more to it. Basically legacy applications sometime do not support multisubnetfailover and to make sure they work with AAG Listener, we need to look at few things
You need to use powershell to amend these settings
1. Make sure below parameters are set rightly for AlwaysOn listener
Import-Module FailoverClusters
Start-ClusterResource <LISTENER RESOURCE NAME>
HostRecordTTL 300
RegisterAllProvidersIP 0
Import-Module FailoverClusters
Get-ClusterResource <LISTENER RESOURCE NAME>| Get-ClusterParameter
2. When RegisterAllProvidersIP is set to 1 (default value), two subnet entries will be created in the DNS.Client need to use the MultiSubnetFailover = true property
Please note RegisterAllProvidersIP value need to be set to 0 for the legacy application which does not support multisubnetfailover property.For example, there are cases where excel 2010 is used and that is why this value need to be adjusted to 0 for this environment.
For latest application, we should leave the RegisterAllProvidersIP value to 1
