AlwaysOn Multi-Subnet Failover and SSIS

Updated 2012-05-11: Updated the .NET Framework update link to point to 4.0.3, as it has some additional updates for AlwaysOn.

If you plan on using Multi-Subnet Failover Clustering, note that the SQL Native Client 11.0 OLEDB provider does not support the new connection string key words (MultiSubnetFailover=True). Instead you should use the ADO.NET or ODBC Source and Destination components.

Details can be found in the SQL Server 2012 Release Notes.

MultiSubnetFailover=True Not Supported by NET Framework 3.5 or OLEDB

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 “Sample PowerShell Script to Disable RegisterAllProvidersIP and Reduce TTL”, 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.

Driver support summary for AlwaysOn:

 

Driver Multi-Subnet Failover ApplicationIntent Read-Only Routing Multi-Subnet Failover: Faster Single Subnet Endpoint Failover Multi-Subnet Failover: Named Instance Resolution For SQL Clustered Instances
SQL Native Client 11.0 ODBC Yes Yes Yes Yes Yes
SQL Native Client 11.0 OLEDB No Yes Yes No No
ADO.NET with .NET Framework 4.0 Update 4.0.3 Yes Yes Yes Yes Yes
ADO.NET with .NET Framework 3.5 No Yes Yes Yes Yes
Microsoft JDBC driver 4.0 for SQL Server Yes Yes Yes Yes Yes

 

As mentioned on the SSIS Team Blog, the SQL Native Client OLEDB provider is being deprecated, but still provides the best interoperability with SSIS components in the SQL Server 2012 release.

One Response to “AlwaysOn Multi-Subnet Failover and SSIS”

  1. Jonlee says:

    HI

    Is this table still valid? Just curious as there is an HF for 3.5SP1 that references multi-subnet failover.
    http://support.microsoft.com/kb/2654347

    Thanks

    JL

Leave a Reply

You must be logged in to post a comment.