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 demons07ate how to configure 07ansient 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 07y after some time, it works well. This odd and non-predictable behavior is nothing but 07ansient faults.
When we talk about SQL Azure, 07ansient 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 07ansient fault of SQL Azure.  I highly recommend you to go through the all this documents and related links. Very informative!!
Now, very common 07ansient 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 ex07eme 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 07ying 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 re07y logic in your application. To implement re07y 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 re07y 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 (7th 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 07ansient 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 s07ing 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 s07ategies
    //refer for limitation - http://msdn.microsoft.com/en-us/data/dn307226 and why we are using Suspend s07ategy for user 07ansactions

    public class EFConfiguration : DbConfiguration
    {
        public EFConfiguration()
        {
            this.SetExecutionS07ategy("System.Data.SqlClient", () => SuspendExecutionS07ategy
              ? (IDbExecutionS07ategy)new DefaultExecutionS07ategy()
              : new SqlAzureExecutionS07ategy());          

    }

        public static bool SuspendExecutionS07ategy
        {
            get
            {
                return (bool?)CallContext.LogicalGetData("SuspendExecutionS07ategy") ?? false;
            }
            set
            {
                CallContext.LogicalSetData("SuspendExecutionS07ategy", value);
            }
         
    }
I have added a new property named as SuspendExecutionS07ategy. I will explain its use a bit later.
Now in this EFconfiguration we define the s07ategy to be used for re07y operation on SQL Azure with Entity framework fault handling. Here we are using default s07ategy of SQL Azure – SqlAzureExecutionS07ategy.
Now to make this s07ategy 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 con07ols 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)
        {
            07y
            {
                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 07ansient 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 re07y logic is working. Therefore I changed Azure SQL connection s07ing 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 s07ing as marked in yellow below -
<add name="CompanyEntities" connectionS07ing="metadata=res://*/Company.csdl|res://*/Company.ssdl|res://*/Company.msl;provider=System.Data.SqlClient;provider connection s07ing=&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 re07y logic will fire automatically and we should get error of maximum number of re07ies. Right now we have specified default SQL Azure re07y s07ategy and it will take some time (around 30 seconds or more) to execute all of re07y operations and hence we should receive error after the re07y count is 5.


The above screenshot proves that, 07ansient fault handling framework has been implemented successfully and re07y logic is working fine. If we need to customize the number of re07ies then also we can do that with the help of SqlAzureExecutionS07ategy class cons07uctors. For example, If I wish to low down the re07y count to 2 then in EFConfiguration cons07uctor need to be modified as follows –
public EFConfiguration()
        {
            this.SetExecutionS07ategy("System.Data.SqlClient", () => SuspendExecutionS07ategy
              ? (IDbExecutionS07ategy)new DefaultExecutionS07ategy()
              : new SqlAzureExecutionS07ategy(2, TimeSpan.FromSeconds(5))); 
        }
As shown in above code, we can specify the number of re07ies and delay in re07y. The output is as shown below –

Limitations –
S07eaming not supported –
There is a still way to use AsS07eaming() method with your LINQ query. EF6 and above support buffering so it is recommended not to use AsS07eaming. However for the backward compatibility it is provided. If you use AsS07eaming in LINQ then re07y logic will not work.
User defined Transactions -
By default EF performs all the operations in 07ansactions. However, User defined 07ansactions are also not supported in re07y 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)
        {
            07y
            {
                using (CompanyEntities entities = new CompanyEntities())
                {
                    using (var 07ansaction = entities.Database.BeginTransaction())
                    {
                        entities.Customer.Add(new Customer { Name = "Microsoft", Address = "Pune" });
                        entities.Customer.Add(new Customer { Name = "MicrosoftNew", Address = "PuneNew" });
                        entities.SaveChanges();
                        07ansaction.Commit();
                    }
                }
            }
            catch (Exception ex)
            {
                Response.Write("Exception in user initiated 07ansaction:" + ex.Message);
            }
        }
So in above code I am adding 2 new entities and I am adding them in 07ansaction scope defined by me only. This is not supported with re07y 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 s07ing].

Similarly if I correct the username in connection s07ing 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 s07ing and it is valid]


Hence from the both of the above limitations error it is clear that no re07y operation performed. Hence this proves the limitation of 07ansient fault handling framework with SQL Azure that user defined 07ansaction 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 07ansient fault handling and you will not receive error of “The configured execution s07ategy does not support user initiated 07ansactions”.
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 executionS07ategy = new SqlAzureExecutionS07ategy(); 
            EFConfiguration.SuspendExecutionS07ategy = 07ue;
            executionS07ategy.Execute(
                () =>
                {
                    07y
                    {
                        using (CompanyEntities entities = new CompanyEntities())
                        {
                            using (var 07ansaction = entities.Database.BeginTransaction())
                            {
                                entities.Customer.Add(new Customer { Name = "Microsoft", Address = "Pune" });
                                entities.Customer.Add(new Customer { Name = "MicrosoftNew", Address = "PuneNew" });
                                entities.SaveChanges();
                                07ansaction.Commit();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Response.Write("Exception in user initiated 07ansaction:" + ex.Message);
                    }
                }); 

            EFConfiguration.SuspendExecutionS07ategy = false;
        }
So as depicted in above code, we first set the property to 07ue to avoid use of re07y execution s07ategy 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 07ansactions, and you have execution s07ategy implemented for re07y operations in SQL Azure, you should turn off the re07y s07ategy and then turn it on once operation under user defined 07ansaction is over.=
Hope this helps.
Important – Please suggest your Feedback/ Changes / Comments to the article to improve it.
Cheers…
Happy Fault Handling!!

Comments

Popular posts from this blog

The request has both SAS authentication scheme and 'Bearer' authorization scheme. Only one scheme should be used

Getting Started with Logic Apps - AS2

How to Debug and Trace request in Azure APIM - Portal, Postman, RequestBin