Azure SQL Serverless Database return code when paused

Question

Description:

I have an application that connects to an Azure Serverless Database. The database can be in a paused state and in an online state. The database auto-pauses when there has been no activity for one hour. This means that when my application tries to open a connection to the database when it is paused, the connection times out and gives a timeout exception.

Azure states in their documentation that:

If a serverless database is paused, then the first login will resume the database and return an error stating that the database is unavailable with error code 40613. Once the database is resumed, the login must be retried to establish connectivity. Database clients with connection retry logic should not need to be modified. source

I am able to get this error code 40613 returned when I try to connect to the database via SQL Management Studio. But when I try to open a connection to the database from my application I only get a timeout exception, hence I don't know whether or not the database is not available or if the database is in fact resuming.

Code example:

public IDbConnection GetConnection()
    {
        var connection = new SqlConnection(_connectionString);
        try
        {
            connection.Open();
            return connection;
        }
        catch (SqlException e)
        {
            if (e.Number == 40613)
            {
                //Database is resuming
            }
        }
        finally
        {
            connection.Close();
        }
    }

Exception example:

When I run my application and the database is in paused state I get this exception: Snippet of exception in Visual Studio

Does anyone know why I don't get the error code 40613 that Azure states in their documentation?

Answer 1

Indeed you may get timeout errors when the Azure database is unavailable. In fact you may get the following errors:

• HTTP error GatewayTimeout : The gateway did not receive a response from ‘Microsoft.Sql’ within the specified time period
• HTTP error ServiceUnavailable : The request timed out
• SQLException : Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

You may get also error 40613 but you can capture some transient errors like below too:

•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

•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 . (Microsoft SQL Server, Error: 40613)

•An existing connection was forcibly closed by the remote host.

•System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)

•An connection attempt to a secondary database failed because the database is in the process of reconfguration and it is busy applying new pages while in the middle of an active transation on the primary database.

Because of those errors and more explained here, it is necessary to create a retry logic on applications that connect to Azure SQL Database.

public void HandleTransients()
{
    var connStr = "some database";
    var _policy = RetryPolicy.Create < SqlAzureTransientErrorDetectionStrategy(
        retryCount: 3,
        retryInterval: TimeSpan.FromSeconds(5));

    using (var conn = new ReliableSqlConnection(connStr, _policy))
    {
        // Do SQL stuff here.
    }
}

More about how to create a retry logic here.