SQL Azure will throttle your connections to protect resources in the data centre.  When SQL Azure does this is non-deterministic – or at least, Microsoft cannot specifically tell you when it will do this because there are many complicated factors. It could be the number of concurrent requests, the CPU or IO usages, the weather – who knows!  In the end it doesn’t really matter – you, THE DEVELOPER, must but be aware of this and deal with it.

SQL Throttling is manifested by a dropped connection.  This may appear as a Native Error 10054 when you try to open a connection or some other error if the connection is dropped after being opened.

To cope with this throttle a retry will normally be all that is required.  At the connection and command level this is easy but it can be complicated by unstructured code.  So, having a nice clean data layer will make the solution much easier to implement, but this applies equally to ‘messy’ code :)

We reused the Retry Policy mechanism that is provided with the Storage Client.  In the old sample storage client the syntax was a lot simpler but in the interest of less confusion I wont talk about that.  The new November SDK works just as well, but you need to do a tad more work.  The following sample is a connection manager that is used by the data layer.

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.WindowsAzure.StorageClient;

 

namespace MyApp.DataLayer

{

  public class ConnectionManager

  {

 

    /// <summary>

    /// Maximum number of retries

    /// </summary>

    private const int MAX_RETRIES = 3;

    /// <summary>

    /// The retry policy for operations that can be retried (sql etc)

    /// </summary>

 

    private RetryPolicy RetryPolicy { get; set; }

 

    /// <summary>

    /// Wait 5 seconds between retries

    /// </summary>

    private const int WAIT_BETWEEN_RETRIES = 5000;

 

 

    public ConnectionManager()

    {

      this.RetryPolicy = RetryPolicies.Retry(MAX_RETRIES, TimeSpan.FromSeconds(5));

    }

 

 

    private SqlConnection OpenConnection(string connectionString)

    {

      SqlConnection con = null;

      try

      {

        DieHard.RequestWithRetry(RetryPolicy, () =>

            {

 

              SqlConnection connection = new SqlConnection(connectionString);

              connection.Open();

              con = connection;

            });

        return con;

      }

      catch (Exception ex)

      {

        throw new Exception("Error connecting to " + connectionString, ex);

      }

    }

  }

}

The interesting part is the DieHard helper class.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.WindowsAzure.StorageClient;

using System.Threading;

 

namespace MyApp.DataLayer

{

  public static class DieHard

  {

    public static T RequestWithRetry<T>(RetryPolicy retryPolicy, Func<T> action)

    {

      ShouldRetry shouldRetry = retryPolicy();

 

      int retryCount = 0;

      while (true)

      {

        TimeSpan delay = TimeSpan.FromSeconds(0);

 

        try

        {

          return action();

        }

        catch (Exception ex)

        {

          if (!shouldRetry(retryCount, ex, out delay))

            throw;

        }

        retryCount++;

        Thread.Sleep(delay);

      }

    }

 

    public static void RequestWithRetry(RetryPolicy retryPolicy, Action action)

    {

      ShouldRetry shouldRetry = retryPolicy();

 

      int retryCount = 0;

      while (true)

      {

        TimeSpan delay = TimeSpan.FromSeconds(0);

 

        try

        {

          action();

          return;

        }

        catch (Exception ex)

        {

          if (!shouldRetry(retryCount, ex, out delay))

            throw;

        }

 

        retryCount++;

        Thread.Sleep(delay);

      }

    }

  }

}

DieHard has 2 variants of the same Retry invoker – 1 for void actions and the other for a generic return value.

There is a couple of points to mention.  The timeout and the number of retries to attempt may vary depending on your situation and other factors – don’t use my 5 second 5 retries settings as the best possible option for you.  You need to be aware of transactions.  Retries should encapsulate the transaction as dropping a connection will rollback the transaction.  So, except for this example of retrying the open connection you should use the retry mechanism at the higher domain level.

Enjoy!