When using a serverless Azure SQL database via an Azure Function, the “Internal .Net Framework Data Provider error 6 in SQL Azure” error can sometimes occur. Yet if you re-run the query a short time later, it works normally.
This error occurs when the SQL database is idle, and therefore the SQL connection fails before the database is active.
To solve this problem, the idea is to put an execution strategy for failure conditions in place, together with a restart policy. These strategies will follow a rule to re-run the query after a given time.
If you are using Entity Framework 6 or EF Core, a native solution already exists. Otherwise, the transient fault handling application block created by Microsoft can be used, if you are using ADO.net. In this article, we will look at the set-up for Entity Framework 6 and EF Core.
Note: this article only deals with connection resiliency for Azure SQL databases, but it is obviously advisable to also check the connection resiliency for any other data service, such as Azure Service Bus, Azure Storage Service or Azure Caching Service.
EF Core has two separate solutions for the location of connection resiliency:
It is possible to either use a native policy by selecting only the basic settings, or define an entirely custom policy.
First of all, here is the code to set up a restart policy in the start-up:
services.AddDbContext<MyDbContext>(options => { options.UseSqlServer(Configuration["ConnectionString"], sqlServerOptionsAction: sqlOptions => { sqlOptions.EnableRetryOnFailure( maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(60), errorNumbersToAdd: null); }); });
Alternatively, within the OnConfiguring() method:
builder.UseSqlServer(connectionString, sqlServerOptionsAction: sqlOptions => { sqlOptions.EnableRetryOnFailure( maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(60), errorNumbersToAdd: null); });
NOTE:
As stated previously, we can also create a custom execution strategy:
public class CustomExecutionStrategy : ExecutionStrategy { public CustomExecutionStrategy(DbContext context) : base(context, 10, TimeSpan.FromSeconds(60)) { } public CustomExecutionStrategy(ExecutionStrategyDependencies dependencies) : base(context, 10, TimeSpan.FromSeconds(60)) { } public CustomExecutionStrategy(DbContext context, int maxRetryCount, TimeSpan maxRetryDelay) : base(context, 10, TimeSpan.FromSeconds(60)) { } protected override bool ShouldRetryOn(Exception exception) { return exception.GetType() == typeof(InvalidOperationException); } }
NOTE: In our case, we are trying to work around the “Internal .Net Framework Data Provider error 6 in SQL Azure” error. This error is shown by an exception called InvalidOperationException, found in the override of the ShouldRetryOn method above.
The following code shows how to apply the custom execution strategy to the dbset in the OnConfiguring() function.
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { optionsBuilder.UseSqlServer("Server=localhost\\SQLEXPRESS;Database=master;Trusted_Connection=True;", builder => builder.ExecutionStrategy(c => new CustomExecutionStrategy(c))); } }
There are four basic execution strategies with EntityFramework 6:
Each strategy is put in place in the same way, by initializing it with the SetExecutionStrategy method in the DbConfiguration class:
public class MyConfiguration : DbConfiguration { public MyConfiguration() { SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy(5, TimeSpan.FromSeconds(60))); } }
In the example, the execution strategy defines two parameters:
This strategy does however have one limitation, which is that it does not work if the user encapsulates the calls within a single transaction. For example, the following code encapsulates two SaveChanges() within one transaction.
using (DemoEntities objContext = GetDemoEntities()) { using (TransactionScope objTransaction = new TransactionScope()) { Demo1(objContext); Demo2(objContext); objTransaction.Complete(); } } public void Demo1(DemoEntities objContext) { Demo1 objDemo1 = new Demo1(); objDemo1.Title = "NEW TITLE 1"; objContext.Demo1.Add(objDemo1); objContext.SaveChanges(); } public void Demo2(DemoEntities objContext) { Demo2 objDemo2 = new Demo2(); objDemo2.Title = "NEW TITLE 2"; objContext.Demo2.Add(objDemo2); objContext.SaveChanges(); }
If one of the two calls generates an error, no change will take place and the EF will not apply the execution strategy. To solve this problem, one execution strategy should be instantiated manually, then executed for the entire transaction:
var executionStrategy = new SqlAzureExecutionStrategy(); executionStrategy.Execute(() => { using (DemoEntities objContext = GetDemoEntities()) { using (TransactionScope objTransaction = new TransactionScope()) { Demo1(objContext); Demo2(objContext); objTransaction.Complete(); } } });
A retry-on-error strategy is effective in making the connection between an application and its Azure SQL database reliable, by eliminating transient connectivity errors.
However, attempting too many retries or too long an interval between them can result in a great many problems. For example, if the error is caused by the data itself (wrong type, constraints not respected, etc.), any fresh attempt with the same data is guaranteed to also fail. Detection and thus diagnosis will simply be delayed. In certain (more extreme) cases, errors could accumulate and, by a snowball effect, ultimately jam the system completely.
Changing the interval type might prevent potential contention issues in some situations; thus an exponential strategy leaves longer and longer intervals between each retry.
Lastly, it is also possible to set up “circuit breakers”. This is an improvement on the strategy which enables an application, once a certain number of events has occurred, to run some other kind of action rather than continue to repeat the same thing over and over.