Setup SQL Server Connection Resiliency with Entity Framework

Tested on:
Microsoft SQL Server 13.00.5292
Entity Framework 6.2.0

When an application execute too many operations against SQL Server, a transient fault could occur.
Transient fault in SQL Server could occurred in self-hosted or cloud SQL Server. It could caused by many reasons and extremely (if not impossible) to debug.
You might encountered error messages like:

“Database * on server * is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of *.”

Other similar messages also point to transient fault errors.

Microsoft suggested solution to transient fault is to retry the executed operation, often dubbed as database connection resiliency. To set this up in Entity Framework, follow this step:

  1. Set EF DbConfiguration using code-based configuration, see this post.

  2. In the constructor, add following code

    SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy(10, TimeSpan.FromSeconds(50)));
    

  3. The first parameter of SqlAzureExecutionStrategy is the maximum number of retry and second parameter is the delay in milliseconds between retries.

Once set, EF will retry the faulted operation automatically. Consider performance when deciding how many tries and delay time to configure. The longer the try or delay time will increase operation time.

References:
Microsoft
Sql Server Backup Academy

Advertisements

Programmatically Configure EF DbConfiguration

Tested on:
Entity Framework 6.2.0

There are few ways to set EF configuration, this describes using code-based DbConfiguration.

  1. Create a class inherit from DbConfiguration.
    using System.Data.Entity;
    
    namespace InfinityDataModel
    {
        public class InfinityConfiguration : DbConfiguration
        {
            public InfinityConfiguration()
            {
                // Sample configuration
                SetDefaultConnectionFactory(new LocalDbConnectionFactory("InfinityDb"));
            }
        }
    }
    

  2. The class must be in same assembly as your the Entity Framework Data Model.

  3. In the constructor, set the configuration you wanted. For all possible configurations, see here.
  4. There is no need to do anything else. EF will load your configuration class when initialized.

References:
Microsoft