Provides a Db Provider Factory that uses the Microsoft Transient Fault Handling library to allow for reliable SQL Azure connections when using Entity Framework 4 or Entity Framework 5 Code First, Linq 2 SQL, Massive or anything based on ADO.NET (if it uses a DB provider factory).
If you are using Entity Framework 6 then you don't need this library and can instead use the built-in transient protection.
Note: This library does not support Entity Framework Database First due to a limitation in Entity Framework. If you are using Database First then we recommend you upgrade to Entity Framework and use the in-built transient protection.
To use the provider:
Install-Package ReliableDbProvider
Register the reliable providers in your web.config
or app.config
(this shows how to register the standard Sql Azure provider - see below for a custom implementation):
<system.data>
<DbProviderFactories>
<add name="Sql Azure Reliable Provider" invariant="ReliableDbProvider.SqlAzure" description="Reliable Db Provider for SQL Azure" type="ReliableDbProvider.SqlAzure.SqlAzureProvider, ReliableDbProvider" />
<add name="Sql Azure Reliable Provider With Timeout Retries" invariant="ReliableDbProvider.SqlAzureWithTimeoutRetries" description="Reliable Db Provider for SQL Azure with Timeout Retries" type="ReliableDbProvider.SqlAzureWithTimeoutRetries.SqlAzureProvider, ReliableDbProvider" />
</DbProviderFactories>
</system.data>
Set the provider name of your connection string to match the invariant
of the provider:
<connectionStrings>
<connectionString name="Name" connectionString="ConnectionString" providerName="ReliableDbProvider.SqlAzure" />
</connectionStrings>
Use the connection string name when initialising the context (note: if you use the Azure Web Sites connection string replacement it replaces the provider name so you have to use the other approach about to be mentioned) or pass into the context a connection created using the provider, e.g.:
var connection = ReliableDbProvider.SqlAzure.SqlAzureProvider.Instance.CreateConnection();
connection.ConnectionString = ConfigurationManager.ConnectionStrings["Name"].ConnectionString;
If you would like to perform an action when a retry occurs then you can using:
ReliableDbProvider.SqlAzure.SqlAzureProvider.CommandRetry += (sender, args) => ...;
ReliableDbProvider.SqlAzure.SqlAzureProvider.ConnectionRetry += (sender, args) => ...;
It's possible for Timeout exceptions to be both a transient error caused by Azure and a legitimate timeout caused by unoptimised queries so we've included a transient error detection strategy that detects these timeout exceptions as a transient error and retries. To use it simply change your provider from ReliableDbProvider.SqlAzure.SqlAzureProvider
to ReliableDbProvider.SqlAzureWithTimeoutRetries.SqlAzureProvider
(provider name from ReliableDbProvider.SqlAzure
to ReliableDbProvider.SqlAzureWithTimeoutRetries
).
There are a few things to note:
ReliableDbProvider.SqlAzure
provider first and then add the one that detects timeouts as transient errors only after you experience timeout errors that you are sure are caused by SQL Azure and not your codeIf you want to customise the transient error detection strategy, the retry strategies or other aspects then you can create your own provider.
Extend ReliableSqlClientProvider
, e.g.:
public class MyReliableProvider : ReliableSqlClientProvider<ATransientErrorDetectionStrategy>
{
public static readonly MyReliableProvider Instance = new MyReliableProvider();
public static event EventHandler<RetryingEventArgs> CommandRetry;
public static event EventHandler<RetryingEventArgs> ConnectionRetry;
protected override RetryStrategy GetCommandRetryStrategy()
{
return /* command retry strategy, e.g. ReliableDbProvider.SqlAzure.RetryStrategies.DefaultCommandStrategy */;
}
protected override RetryStrategy GetConnectionRetryStrategy()
{
return /* connection retry strategy, e.g. ReliableDbProvider.SqlAzure.RetryStrategies.DefaultConnectionStrategy */;
}
protected override DbConnection GetConnection(ReliableSqlConnection connection)
{
connection.CommandRetryPolicy.Retrying += CommandRetry;
connection.ConnectionRetryPolicy.Retrying += ConnectionRetry;
return new MyReliableConnection(connection);
}
}
Extend ReliableSqlDbConnection
; specifying the Db Provider you created as the factory, e.g.:
public class MyReliableConnection : ReliableSqlDbConnection
{
public MyReliableConnection(ReliableSqlConnection connection) : base(connection) { }
protected override DbProviderFactory GetProviderFactory()
{
return MyReliableProvider.Instance;
}
}
Add the provider to your web.config
/app.config
, e.g.:
<system.data>
<DbProviderFactories>
<add name="My Reliable Provider" invariant="MyAssemblyBaseNamespace.MyReliableProviderNamespace" description="Reliable Db Provider for something..." type="MyAssemblyBaseNamespace.MyReliableProviderNamespace.MyReliableProvider, MyAssembly" />
</DbProviderFactories>
</system.data>
Set the provider name of your connection string to match the invariant
of your new provider:
<connectionStrings>
<connectionString name="Name" connectionString="ConnectionString" providerName="MyAssemblyBaseNamespace.MyReliableProviderNamespace" />
</connectionStrings>
If you want to contribute to this library then you need to:
ReliableDbProviderTests
and grant the user running the NUnit runner dbowner
access.
Database
ConnectionString in App.config
, but note: you may also need to change the service name to stop / start in Config\DbTestBase.cs
ReliableDbProvider.Tests
project with your NUnit test runner of choice