A multi-subnet FCI by default enables the RegisterAllProvidersIP cluster resource for its network name. In a multi-subnet configuration, both the online and offline IP addresses of the network name will be registered at the DNS server. The client application then retrieves all registered IP addresses from the DNS server and attempts to connect to the addresses either in order or in parallel. This means that client recovery time in multi-subnet failovers no longer depend on DNS update latencies. By default, the client tries the IP addresses in order. When the client uses the new optional MultiSubnetFailover=True parameter in its connection string, it will instead try the IP addresses simultaneously and connects to the first server that responds. This can help minimize the client recovery latency when failovers occur. For more information, see AlwaysOn Client Connectivity (SQL Server) and Create or Configure an Availability Group Listener (SQL Server).
With legacy client libraries or third party data providers, you cannot use the MultiSubnetFailover parameter in your connection string. To help ensure that your client application works optimally with multi-subnet FCI in SQL Server 2012, try to adjust the connection timeout in the client connection string by 21 seconds for each additional IP address. This ensures that the client’s reconnection attempt does not timeout before it is able to cycle through all IP addresses in your multi-subnet FCI.
The default client connection time-out period for SQL Server Management Studio and sqlcmd is 15 seconds.
https://msdn.microsoft.com/en-us/library/ff878716(v=sql.110).aspx
Linked Servers/OPENROWSET use OLE DB and OLE DB in the SQL Server Native Client does not support the MultiSubnetFailover keyword.
Try a System DSN using ODBC with MultiSubnetFailover specified and then creating the linked server to use this .
Other option is to increase the timeout of the linked server to give it time to work around all the registered IP addresses.
Use the following guidelines to connect to a server in an availability group or SQL Server 2012 Failover Cluster Instance:
- Use the MultiSubnetFailover=True connection property when connecting to a single subnet or multi-subnet; it will improve performance for both.
- To connect to an availability group, specify the availability group listener of the availability group as the server in your connection string.
- Connecting to a SQL Server instance configured with more than 64 IP addresses will cause a connection failure.
- Behavior of an application that uses the MultiSubnetFailover connection property is not affected based on the type of authentication: SQL Server Authentication, Kerberos Authentication, or Windows Authentication.
- Increase the value of Connect Timeout to accommodate for failover time and reduce application connection retry attempts.
- Distributed transactions are not supported.
If read-only routing is not in effect, connecting to a secondary replica location will fail in the following situations:
- If the secondary replica location is not configured to accept connections.
- If an application uses ApplicationIntent=ReadWrite (discussed below) and the secondary replica location is configured for read-only access.
https://msdn.microsoft.com/en-us/library/hh205662(v=vs.110).aspx
Bottom Line:
1. Upgrade clients where possible
2. Set the multisubnetfailover option if using SQLClient in .NET 4.0 and above client libraries and applications
3. Extend connection timeouts to 21 seconds per subnet for legacy connections that cannot be upgraded
4. If the application uses a library that cannot be upgraded, but supports ODBC, then upgrade ODBC and create an ODBC connection that uses the MultiSubmetFailover=True and have application connect using the ODBC connection.
5. If the application uses SQL Client, then can create a SQL Native Client alias that points to primary node (can use a powershell script to change)
6. If all else fails, connect the application to the primary node (will have to be manually changed on failover)
7. Not recommended, but can set RegisterAllProvidersIP to off for the cluster (this could cause some clients to not be able to connect for 20 minutes depending on HostRecordTTL setting)
Additional References:
http://www.madeiradata.com/make-sure-clients-can-connect-multi-subnet-cluster/
http://johnlouros.com/blog/leveraging-multi-subnet-failover
https://blogs.technet.microsoft.com/sqlpfeil/2014/03/31/sql-alias-powershell-script/
http://sqlperformance.com/2013/11/system-configuration/ag-connectivity
Use the following guidelines to connect to a server in an availability group or SQL Server 2012 Failover Cluster Instance:
- Use the MultiSubnetFailover=True connection property when connecting to a single subnet or multi-subnet; it will improve performance for both.
- To connect to an availability group, specify the availability group listener of the availability group as the server in your connection string.
- Connecting to a SQL Server instance configured with more than 64 IP addresses will cause a connection failure.
- Behavior of an application that uses the MultiSubnetFailover connection property is not affected based on the type of authentication: SQL Server Authentication, Kerberos Authentication, or Windows Authentication.
- Increase the value of Connect Timeout to accommodate for failover time and reduce application connection retry attempts.
- Distributed transactions are not supported.
If read-only routing is not in effect, connecting to a secondary replica location will fail in the following situations:
- If the secondary replica location is not configured to accept connections.
- If an application uses ApplicationIntent=ReadWrite (discussed below) and the secondary replica location is configured for read-only access.
https://msdn.microsoft.com/en-us/library/hh205662(v=vs.110).aspx
Bottom Line:
1. Upgrade clients where possible
2. Set the multisubnetfailover option if using SQLClient in .NET 4.0 and above client libraries and applications
3. Extend connection timeouts to 21 seconds per subnet for legacy connections that cannot be upgraded
4. If the application uses a library that cannot be upgraded, but supports ODBC, then upgrade ODBC and create an ODBC connection that uses the MultiSubmetFailover=True and have application connect using the ODBC connection.
5. If the application uses SQL Client, then can create a SQL Native Client alias that points to primary node (can use a powershell script to change)
6. If all else fails, connect the application to the primary node (will have to be manually changed on failover)
7. Not recommended, but can set RegisterAllProvidersIP to off for the cluster (this could cause some clients to not be able to connect for 20 minutes depending on HostRecordTTL setting)
Additional References:
http://www.madeiradata.com/make-sure-clients-can-connect-multi-subnet-cluster/
http://johnlouros.com/blog/leveraging-multi-subnet-failover
https://blogs.technet.microsoft.com/sqlpfeil/2014/03/31/sql-alias-powershell-script/
http://sqlperformance.com/2013/11/system-configuration/ag-connectivity
No comments:
Post a Comment