Compartilhar via

Supporting SQL Server 2012 AlwaysOn® in .NET Framework 4 (by Luiz Fernando Santos)

  • Artigo

One of the most common questions I get from customers is which version of .NET supports SQL Server 2012 AlwaysOn® connectivity.  For those who are not familiar with SQL Server 2012, this is its new and exciting high availability feature.  In a nutshell, it enhances the capabilities of Database Mirroring by providing an integrated set of options including automatic and manual failover of a group of databases and by adding support to multi-site clustering across different subnets, which enables fast cross-datacenter failover.

AlwaysOn® also enables workload-based connectivity through SQL Server Availability Groups.  In this case, applications intending to connect in order to only retrieve data can be rerouted to read-only copies of the database in another server instance, called secondary, offloading the main one, called primary.  For more information on AlwaysOn®, please refer to https://go.microsoft.com/fwlink/?LinkID=232281&clcid=0x409)

From the application standpoint, AlwaysOn® does not require any special API.  The only requirement is a couple of new connection string keywords (ApplicationIntent and MultiSubnetFailover) in order to inform the application’s intent (ReadOnly or ReadWrite) and if the application is connecting to an AlwaysOn® Availability Group cluster.

More information on using these keywords can be found in MSDN, at https://go.microsoft.com/fwlink/?LinkID=242787&clcid=0x409.

On October 19th, 2011, Microsoft released the Update 4.0.2 for the Microsoft .NET Framework 4 (found at https://support.microsoft.com/kb/2544514), which includes support for AlwaysOn® connectivity.  If you want to use this new high availability feature of SQL Server 2012, you need to apply this update in order to properly set the new connection string properties to enable direct connectivity to secondary servers and to fast failover between availability group clusters.

Now, it’s very important to have in mind that this update is not required if you plan to connect to a regular DB Mirroring infrastructure or failover cluster or even to a standalone server.

Luiz Fernando Santos

ADO.NET PM

Comments

  • Anonymous
    April 17, 2014
    Hi Luiz, I'm using Microsoft .NET Framework Version:4.0.30319 and it keeps showing me that the Keyword multisubnetfailover is not supported, it comes without saying but does this version actually supports this? My SQL Cluster AlwaysOn is Windows Server 2012 R2 and the client is W2008 R2. Do you have any comments for that? This is my connection string <add name="<listener>" connectionString="Server=tcp:<server>,1433;Database=<DB>;Integrated Security=SSPI;MultiSubnetFailover=True"/> </connectionStrings> [HttpException (0x80004005): Keyword not supported: 'multisubnetfailover'.]   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +11792384   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +141   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +4941237

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1016

  • Anonymous
    February 05, 2016
    Just in case anyone else stumbles on this like I did.... I was also running into the same issue Ricardo reported below. Even though we were running the full version of .NET 4.0.30319 - the System.Data.dll file did not have the new properties (MultiSubNetFailOver, ApplicationIntent) included. Once I installed the .NET Framework 4.5 - the assemblies in the .NET 4.0.30319 folder were updated and now included the new SqlStringConnectionBuilder properties. So, install .NET Framework 4.5 and that should resolve the issue.