Database connection fails for AlwaysOn SQL Cluster using MultiSubnetFailover
Article ID: CTX214738
Updated On:
Description
Issue:
Database connection fails for AlwaysOn SQL Cluster using MultiSubnetFailover
Details:
XenDesktop Database Connection String includes the MultiSubnetFailover flag.
Environment
Resolution
SQL requires the MultiSubnetFailoverString to be included in the database connection string passed by XenDesktop.
Example :
Server=$ServerName;Initial Catalog=$DBName;Integrated Security=True;MultiSubnetFailover=TrueSee: http://support.citrix.com/article/CTX216504
PVS users should reference:
http://support.citrix.com/article/CTX201203
https://docs.citrix.com/en-us/provisioning/7-6/pvs-ha-wrapper/pvs-ha-sql-always-on.html
As a quick test, to see if this is the cause:
asnp citrix*
get-brokerservicestatus
Status should show "OK".
2. Failover the AlwaysOn cluster
3. Verify the Xendesktop site has lost connection to the database. In the same elevated PowerShell, run:
get-brokerservicestatus
You should be receiving errors similar to "There was a problem communicating with the Delegated Administration Service."
4. Open an elevated CMD and register the DNS record for the Cluster Listener by running ipconfig /registerdns from the SQL Listener.
5. Test the site to see if the database has been reconnected. in the same elevated PowerShellrun :
get-brokerservicestatus
Status should show "OK".
If this works correctly, follow the guidance from Microsoft to be sure the configuration for MultiSubnetFailover is correct.
MultiSubnetFailover is a new connection string keyword used to enable faster failover with AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances in SQL Server 2012. The following three sub-features are enabled when MultiSubnetFailover=True is set in connection string:
-
Faster multi-subnet failover to a multi-subnet listener for an AlwaysOn Availability Group or Failover Cluster Instances.
-
Faster single subnet failover to a single subnet listener for an AlwaysOn Availability Group or Failover Cluster Instances.
-
Named instance resolution to a multi-subnet AlwaysOn Failover Cluster Instance.
MultiSubnetFailover=True Not Supported by NET Framework 3.5 or OLEDB
Issue: If your Availability Group or Failover Cluster Instance has a listener name (known as the network name or Client Access Point in the WSFC Cluster Manager) depending on multiple IP addresses from different subnets, and you are using either ADO.NET with .NET Framework 3.5SP1 or SQL Native Client 11.0 OLEDB, potentially 50% of your client-connection requests to the availability group listener will hit a connection timeout.
Workarounds: We recommend that you do one of the following tasks.
-
If do not have the permission to manipulate cluster resources, change your connection timeout to 30 seconds (this value results in a 20-second TCP timeout period plus a 10-second buffer).
Pros: If a cross-subnet failover occurs, client recovery time is short.
Cons: Half of the client connections will take more than 20 seconds
-
If you have the permission to manipulate cluster resources, the more recommended approach is to set the network name of your availability group listener to RegisterAllProvidersIP=0. For more information, see "RegisterAllProvidersIP Setting” later in this section.
Pros: You do not need to increase your client-connection timeout value.
Cons: If a cross-subnet failover occurs, the client recovery time could be 15 minutes or longer, depending on your HostRecordTTL setting and the setting of your cross-site DNS/AD replication schedule.
When you use SQL Server Management Studio, Transact-SQL, or PowerShell to create an availability group listener, the Client Access Point is created in WSFC with the RegisterAllProvidersIP property set to 1 (true). The effect of this property value depends on the client connection string, as follows:
-
Connection strings that set MultiSubnetFailover to true
AlwaysOn Availability Groups sets the RegisterAllProvidersIP property to 1 in order to reduce re-connection time after a failover for clients whose client connection strings specify MultiSubnetFailover = True, as recommended. Note that to take advantage of the listener multi-subnet feature, your clients might require a data provider that supports the MultiSubnetFailover keyword. For information about driver support for multi-subnet failover, see AlwaysOn Client Connectivity (SQL Server).
For information about multi-subnet clustering, see SQL Server Multi-Subnet Clustering (SQL Server).
Tip When RegisterAllProvidersIP = 1, if you run the WSFC Validate a Configuration Wizard on the WSFC cluster, the wizard generates the following warning message: "The RegisterAllProviderIP property for network name 'Name:<network_name>' is set to 1 For the current cluster configuration this value should be set to 0." Please ignore this message.
-
Connection strings that do not set MultiSubnetFailover to true
When RegisterAllProvidersIP = 1, any clients whose connection strings do not use MultiSubnetFailover = True, will experience high latency connections. This occurs because these clients attempt connections to all IPs sequentially. In contrast, if RegisterAllProvidersIP is changed to 0, the active IP address is registered in the Client Access Point in the WSFC cluster, reducing latency for legacy clients. Therefore, if you have legacy clients that that need to connect to an availability group listener and cannot use the MultiSubnetFailover property, we recommend that you change RegisterAllProvidersIP to 0.
Important
When you create an availability group listener through the WSFC cluster (Failover Cluster Manager GUI), RegisterAllProvidersIP will be 0 (false) by default.
By default, clients cache cluster DNS records for 20 minutes. By reducing HostRecordTTL, the Time to Live (TTL), for the cached record, legacy clients may reconnect more quickly. However, reducing the HostRecordTTL setting may also result in increased traffic to the DN servers.
Problem Cause
DNS was not updating with the SQL cluster listener IP.Issue/Introduction
Additional Information
