Developers sometimes ask me about “database is not currently available” error when using Azure SQL and Entity Framework 6. In this article I explain why happens this error and how to solve it easily.

Case Study

Let’s start with the real life example from our customer:

„… we detected an error in our logs: „Database ‘XXX’ on server ‘XXX’ is not currently available.”. It happens usually once a week and sometimes it happens more times during a night. Should we worry about it? We want to move the database from US region do EU and move all databases under elastic pool, so that maybe we do not have to solve this issue now…“

Theory about Azure SQL

Azure SQL service is a typical PaaS service so that developer doesn’t have to worry about the platform. OS updates, security updates and other things are automatically solved on the provider side. The problem is that sometimes SQL Server reconfiguration is needed and in this case short downtime occurs. It happens during updating OS or when an issue occurred (process or load balancing failure). Most of the downtimes are very short (threshold about 60 seconds). Most of the developers do not know about this issue and just a few developers experienced this error but they do not solve it because the error is exceptional.

Solution

If you want to solve this issue, you must implement retry logic. If you use Entity Framework 6 or EF Core, the solution is very easy. Developers in Microsoft know about this behavior and they prepared easy solution in ORMs.

Connection Resiliency / Retry Logic (EF 6)

In case of EF 6 you must allow Execution Strategy and choose SqlAzureExecutionStrategy. Complete configuration looks like this:

public class MyConfiguration : DbConfiguration 
{ 
    public MyConfiguration() 
    { 
        SetExecutionStrategy( 
            "System.Data.SqlClient", 
            () => new SqlAzureExecutionStrategy(1, TimeSpan.FromSeconds(30))); 
    } 
}

Source and another information you can find in article:

• Entity Framework Connection Resiliency and Retry Logic – EF6 onwards.aspx

Connection Resiliency (EF Core)

If you work with EF Core, you can analogicaly configure your project in the place where you register all services.

// Startup.cs from any ASP.NET Core Web API
public class Startup
{
    // Other code ...
    public IServiceProvider ConfigureServices(IServiceCollection services)
    {
        // ...
        services.AddDbContext(options =>
        {
            options.UseSqlServer(Configuration["ConnectionString"],
            sqlServerOptionsAction: sqlOptions =>
            {
                sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null);
            });
        });
    }
//...
}

Source and another information you can find in article:

• Implementing resilient Entity Framework Core SQL connections

As you can see in examples, implementation is a piece of cake.