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="data
source=myserver.database.windows.net,1433;initial catalog=Company;persist
security info=True;user
id=IncorrectUsername;password=correctpasssword;MultipleActiveResultSets=True;App=EntityFramework"" 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
Post a Comment