Transient Fault Handling with SQL Azure and Entity Framework step by step guide

Communication errors are always a pain!! When you are working with cloud platform like Azure, communication errors makes you feel the pain even more!!

Thanks that Transient Fault Handling framework exists!!

Alright guys, so here I am going demonstrate how to configure transient fault handling application block of Enterprise Library 6 (and above hopefully, if they don’t change in next versionsJ) with SQL Azure and Entity framework. This going to be a long article that is what I think. So be ready!!

What are Transient Faults or Transient Errors?

These are mainly network issues. If you perform an operation in Azure and sometimes it does not work however if you try after some time, it works well. This odd and non-predictable behavior is nothing but transient faults.

When we talk about SQL Azure, transient faults are common. The article Azure SQL Database Resource Management - http://msdn.microsoft.com/library/azure/dn338083.aspx mentions the scenarios in which you may face transient fault of SQL Azure.  I highly recommend you to go through the all this documents and related links. Very informative!!

Now, very common transient error of SQL Azure is throttling.

What is throttling?

Throttle is very common term used in computing and networking. The dictionary meaning of throttle is to kill or stop something. Throttling can be of any type like, network throttling, bandwidth throttling, and email throttling and so on.  Throttling can be sometime good and sometime bad. When it is bad for you, it is your responsibility to handle it. Throttling is good when the database server engine is experiencing extreme load and it is well above the desired load. Then throttling brings down consumption of resource and frees some of the load of database engine. This is done via blocking the connections or denying the new connections to SQL Azure database engine. It is bad when you are trying to connect to SQL Azure and throttling occurs and you get connectivity errors with SQL Azure. To understand more about SQL Azure throttling refer to the link - http://msdn.microsoft.com/en-US/library/azure/dn338079.aspx.

Solution -

The solution is simple and you are advised to implement retry logic in your application. To implement retry logic in SQL Azure and entity framework, we use Transient Fault Handling block. It is simple and recommended to always use Transient Fault Handling framework to deal with SQL Azure connectivity errors and have retry logic in application.

Implementation –

Please note that, I am using Transient Fault Handling Block version 6.0.1304. This is the one available as of today (7^th July 2014). Also I am using Azure SDK 2.3 and visual stiudio 2013 for this sample.

First let’s create a server in SQL Azure and add a database in it. I created simple database named as Company and added only one table in it named as Customer with 3 columns and sample data as shown in below screenshot.

To demo Transient Fault Handling with SQL Azure, let’s create a cloud service application in Visual Studio with one web role in it. Let’s add reference of transient fault handling DLL’s using nuget. Go to Tools->Nuget Package Manager->Package Manager Console in visual studio. Run the following command in console to install Transient Fault Handling block –

Install-Package EnterpriseLibrary.TransientFaultHandling

 

This will add all the necessary DLL’s reference in web role project. Now we need to add EDMX in web role project. Therefore add new EDMX item, make connection to SQL Azure database (in my case it will be Company database) –  

This adds all DLL’s related to entity framework as shown below –

Also the connection string to SQL Azure gets added to web.config file.

Add a class named as EFConfiguration in web role project with following code –

// EF follows a Code based Configration model and will look for a class that

    // derives from DbConfiguration for executing any Connection Resiliency strategies

    //refer for limitation - http://msdn.microsoft.com/en-us/data/dn307226 and why we are using Suspend strategy for user transactions

    public class EFConfiguration : DbConfiguration

    {

        public EFConfiguration()

        {

            this.SetExecutionStrategy("System.Data.SqlClient", () => SuspendExecutionStrategy

              ? (IDbExecutionStrategy)new DefaultExecutionStrategy()

              : new SqlAzureExecutionStrategy());          

    }

        public static bool SuspendExecutionStrategy

        {

            get

            {

                return (bool?)CallContext.LogicalGetData("SuspendExecutionStrategy") ?? false;

            }

            set

            {

                CallContext.LogicalSetData("SuspendExecutionStrategy", value);

            }

        }  

    }

I have added a new property named as SuspendExecutionStrategy. I will explain its use a bit later.

Now in this EFconfiguration we define the strategy to be used for retry operation on SQL Azure with Entity framework fault handling. Here we are using default strategy of SQL Azure – SqlAzureExecutionStrategy.

Now to make this strategy registered, in application_start method of global.asax add following code block –

DbConfiguration.SetConfiguration(new EFConfiguration());

Then I added a web page named as TransientDemo.aspx in web role project and added few controls on to it as shown below –  

The grid view is just a placeholder and in this example it will not populate anything.

On Transient Demo button click event add following code -

protected void btnTransientDemo_Click(object sender, EventArgs e)

        {

            try

            {

                CompanyEntities entities = new CompanyEntities();

                gdvCustomers.DataSource = entities.Customer.ToList();

                gdvCustomers.DataBind();

            }

            catch (Exception ex)

            {

                Response.Write("Exception :" + ex.Message);

            }

        }

Now important step. I need to have a way by which I can generate transient error on SQL Azure and unfortunately we can’t do it. However I need a way by which at least I can know if my retry logic is working. Therefore I changed Azure SQL connection string to have incorrect username so Therefore in web.config file of web role application I changed the actual username to something incorrect username for EDMX connection string as marked in yellow below -

<add name="CompanyEntities" connectionString="metadata=res://*/Company.csdl|res://*/Company.ssdl|res://*/Company.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=myserver.database.windows.net,1433;initial catalog=Company;persist security info=True;user id=IncorrectUsername;password=correctpasssword;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Now let’s run the application. Click on TransientDemo button. This will call the code written above and connection to the SQL Azure will not be made due to wrong username provided. Hence retry logic will fire automatically and we should get error of maximum number of retries. Right now we have specified default SQL Azure retry strategy and it will take some time (around 30 seconds or more) to execute all of retry operations and hence we should receive error after the retry count is 5.

The above screenshot proves that, transient fault handling framework has been implemented successfully and retry logic is working fine. If we need to customize the number of retries then also we can do that with the help of SqlAzureExecutionStrategy class constructors. For example, If I wish to low down the retry count to 2 then in EFConfiguration constructor need to be modified as follows –

public EFConfiguration()

        {

            this.SetExecutionStrategy("System.Data.SqlClient", () => SuspendExecutionStrategy

              ? (IDbExecutionStrategy)new DefaultExecutionStrategy()

              : new SqlAzureExecutionStrategy(2, TimeSpan.FromSeconds(5))); 

        }

As shown in above code, we can specify the number of retries and delay in retry. The output is as shown below –

Limitations –

Streaming not supported –

There is a still way to use AsStreaming() method with your LINQ query. EF6 and above support buffering so it is recommended not to use AsStreaming. However for the backward compatibility it is provided. If you use AsStreaming in LINQ then retry logic will not work.

User defined Transactions -

By default EF performs all the operations in transactions. However, User defined transactions are also not supported in retry logic. I will show you the demo of this. We have another button added in our UI called as Limitation Demo button as shown in one of the above screenshot. On button click event add following code –

protected void btnLimitationDemo_Click(object sender, EventArgs e)

        {

            try

            {

                using (CompanyEntities entities = new CompanyEntities())

                {

                    using (var transaction = entities.Database.BeginTransaction())

                    {

                        entities.Customer.Add(new Customer { Name = "Microsoft", Address = "Pune" });

                        entities.Customer.Add(new Customer { Name = "MicrosoftNew", Address = "PuneNew" });

                        entities.SaveChanges();

                        transaction.Commit();

                    }

                }

            }

            catch (Exception ex)

            {

                Response.Write("Exception in user initiated transaction:" + ex.Message);

            }

        }

So in above code I am adding 2 new entities and I am adding them in transaction scope defined by me only. This is not supported with retry logic. So if I run the applications and click on LimitationDemo button then see the error below I get –

[Please remember I still have incorrect username in web.config connection string].

Similarly if I correct the username in connection string in web.config file and then if I click on Limitation Demo button observer the error I receive –

[Please remember in this case, I have correct username and password configured in connection string and it is valid]

Hence from the both of the above limitations error it is clear that no retry operation performed. Hence this proves the limitation of transient fault handling framework with SQL Azure that user defined transaction cannot be used.

Workaround for using User Defined Transaction in Transient Fault Handling –

It’s simple and I have already implemented it. It is just matter of setting a property now to make use of transient fault handling and you will not receive error of “The configured execution strategy does not support user initiated transactions”.

I added another button on the same page named as Workaround Demo and on button click added following code –

[Please remember I still have correct username and password configured in my web config].

protected void btnWorkAround_Click(object sender, EventArgs e)

        {

            var executionStrategy = new SqlAzureExecutionStrategy(); 

            EFConfiguration.SuspendExecutionStrategy = true;

            executionStrategy.Execute(

                () =>

                {

                    try

                    {

                        using (CompanyEntities entities = new CompanyEntities())

                        {

                            using (var transaction = entities.Database.BeginTransaction())

                            {

                                entities.Customer.Add(new Customer { Name = "Microsoft", Address = "Pune" });

                                entities.Customer.Add(new Customer { Name = "MicrosoftNew", Address = "PuneNew" });

                                entities.SaveChanges();

                                transaction.Commit();

                            }

                        }

                    }

                    catch (Exception ex)

                    {

                        Response.Write("Exception in user initiated transaction:" + ex.Message);

                    }

                }); 

            EFConfiguration.SuspendExecutionStrategy = false;

        }

So as depicted in above code, we first set the property to true to avoid use of retry execution strategy and then we set to false. If I run the application and click on Workaround button then 2 new records gets added in my DB table without any error.

Hence whenever you wish to have user defined transactions, and you have execution strategy implemented for retry operations in SQL Azure, you should turn off the retry strategy and then turn it on once operation under user defined transaction is over.=

Hope this helps.

Important – Please suggest your Feedback/ Changes / Comments to the article to improve it.

Cheers…

Happy Fault Handling!!