Azure EF Core and EF 6 SQL database connection resiliency

Antoine NAFFETAT
Published by Antoine NAFFETAT
Category : .Net / Azure / SQL Server
07/01/2021

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 native solution

 

Introduction

EF Core has two separate solutions for the location of connection resiliency:

  • the first is implementation in the start-up;
  • the second is within the OnConfiguring() method.

It is possible to either use a native policy by selecting only the basic settings, or define an entirely custom policy.

 

Set-up process

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:

  • This retry on failure configuration is a default strategy for SQL connections, and it can be configured as desired for the basic settings (number of retries, time between two attempts, etc.).
  • Microsoft recommends a total retry time (number of attempts × time between retries) of at least 1 minute.

 

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)));
     }
}

 

EF6 native solution

 

Introduction

There are four basic execution strategies with EntityFramework 6:

  • DefaultExecutionStrategy as the name suggests, it’s the default strategy, with no retries.
  • DefaultSQLExecutionStrategy: no retries are attempted, but it does combine all exceptions considered to be transient, to warn users of the possibility of setting up an execution strategy for these errors.
  • DbExecutionStrategy: this strategy has an exponential delay interval type and also an abstract ShouldRetryOn method which can be used to implement the decision strategy.
  • SqlAzureExecutionStrategy: Inherits from DbExecutionStrategy and retries known errors as transient errors.

 

Set-up process

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:

  • The first is the maximum number of retries.
  • The second is the maximum time between the first error and the last retry.

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();
        }
    }
});

 

Conclusion and warning

 

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.

← See all articles