Handling exceptions by using retries (SQL Database)

Using a SQL Database in the cloud can be useful. You get a completely managed database without any prior configuration. It scales well and can be used from within your cloud or onpremise applications.
However, there is one thing different in the cloud than running a SQL database on-premise: latency. Because the physical distance between your database and application server is determined by the infrastructure in the datacenter, you get a higher latency than you would in an on-premise environment. Because of this, you will be more likely to experience timeouts when connecting to a database.
These types of errors are transient, meaning those errors will often go away after some time. For these types of errors, it makes sense to implement retry logic. This basically means that you inspect the error code that’s returned from SQL Server, determine whether it’s transient, wait for a set amount of time, and then try again to access the database.
The amount of time you wait before trying the action can be flexible. Maybe you want to retry three times, such as after one second, then two seconds, and then five seconds. Windows Azure offers the transient fault handling framework to help you with creating retry logic in your own applications. The following code shows an example of a retry strategy that retries on deadlocks and timeouts:

class MyRetryStrategy : ITransientErrorDetectionStrategy
{
 public bool IsTransient(Exception ex)
 {
 if (ex != null && ex is SqlException)
 {
 foreach (SqlError error in (ex as SqlException).Errors)
 { 
Objective 1.4: Implement data storage in Windows Azure CHAPTER 1 73
 switch (error.Number)
 {
 case 1205:
 System.Diagnostics.Debug.WriteLine("SQL Error: Deadlock
condition. Retrying...");
 return true;
 case -2:
                      System.Diagnostics.Debug.WriteLine("SQL Error: Timeout expired.
Retrying...");
 return true;
 }
 }
 }
 // For all others, do not retry.
  return false;
 }
}

You can use the retry strategy when executing a query from ADO.NET:

RetryPolicy retry = new RetryPolicy(5, new TimeSpan(0, 0, 5));
using (SqlConnection connection = new SqlConnection())
{
 connection.OpenWithRetry(retry);
 SqlCommand command = new SqlCommand("");
 command.Connection = connection;
 command.CommandTimeout = CommandTimeout;
 SqlDataReader reader = command..ExecuteReaderWithRetry(retry);
 while (reader.Read())
 {
 // process data
 }
}

When working on your application or service in Visual Studio you work with a lot of files. Some of those files contain code; others contain markup or configuration settings.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s