18

I currently use SQL Azure and Entity SQL in my application.

e.g.

Entities model = new Entities();
db_Item item = model.db_Item.First();

Now I want to use the Transient Fault Handling out of the Enterprise Library but there are no examples or solutions that I can find that would allow me to do something like override the Entities class, so I don't have to update my code in hundreds of places.

Could someone please provide more information on how this could be done?

Adam
  • 16,089
  • 6
  • 66
  • 109
  • 2
    I've created a library which allows you to configure Entity Framework to retry using the Fault Handling block without needing to change every database call - generally you will only need to change your config file and possibly one or two lines of code. This allows you to use it for Entity Framework < v6 or Linq To Sql. https://github.com/robdmoore/ReliableDbProvider – Robert Moore Jun 17 '13 at 16:25

5 Answers5

19

Going through what I have so far.

  1. The Entity Framework does not provide access to the connection open and section where the SQL is sent to the server, hence it is currently impossibile to provide retry logic around this area.

  2. The EF team are aware of this shortfall and are planning on actually integrating retry logic into EF for possibily version 6.

  3. As per Case #3 of [1] you can send a SQL command to the database on the OnContextCreated. This however means for EVERY single DB call you make to the DB, you will have to make 2. I wouldn't recommend this in hardly any situation unless you don't care about performance.

  4. The only viable option so far is implementing retry logic in the form of the Enterprise Library Transient Fault Handling Application Block [2] around every call you make to the database. In existing applications this is extremely tedious.

  5. When I get time I am looking further into the source code of EF to see if anything further can be done, while we wait for EF 6. I would keep an eye on [3]

  6. Some hope, it is currently under review by the EF team. [4]

Update: 2013-11-14

Just thought I would update this post to let everyone know that EF6 has been released and supports connection resiliency out of the box. https://www.nuget.org/packages/EntityFramework/

No need for workarounds any more.

Update: 2013-03-23

EF 6 Alpha 3 released with Connection Resiliency - http://entityframework.codeplex.com/wikipage?title=Connection%20Resiliency%20Spec

Update: 2012-11-04

The EF team have officially announced it is planned for EF 6. [4]

[1] http://blogs.msdn.com/b/appfabriccat/archive/2010/12/11/sql-azure-and-entity-framework-connection-fault-handling.aspx

[2] http://msdn.microsoft.com/en-us/library/hh680934(v=pandp.50).aspx

[3] http://entityframework.codeplex.com/wikipage?title=Roadmap

[4] http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/2426525-automatically-perform-retry-logic-for-sql-azure

Adam
  • 16,089
  • 6
  • 66
  • 109
  • We are actually going with your bullet point #3: opening a connection and sending a SQL command to the DB on OnContextCreated. Why? First of all, you are wrong. This will NOT send extra queries on every database call you make. It will only send extra (nearly-instantaneous) queries every time you instantiate a new Context. If you use the same context for multiple queries (as you should), then it keeps the connection open (as you should) and doesn't send any additional queries. We've actually seen a performance *increase* because of this. In my experience, #3 is a good (albeit partial) solution. – Jaxidian Jan 17 '13 at 15:46
  • 1
    I think you are missing the point. You need to send a SQL query to the DB to see if the connection is alive as SQL Azure could have dropped it at their end. Hence you must do this for EVERY query. If you don't you are just back to square 1 of not knowing if the connection is open before sending the request. SQL queries are always a performance hit. In a single transaction of a user waiting 1.1 seconds compared to 1.0 seconds doesn't matter. The difference is I am talking on the scale of up to a 1,000 requests per second. 100ms is a lot of time. – Adam Jan 19 '13 at 05:41
11

Thankfully it is pretty simple with the new Transient Fault Handling Application block. Everything you need can be found here:

http://geekswithblogs.net/ScottKlein/archive/2012/01/27/understanding-sql-azure-throttling-and-implementing-retry-logic.aspx

and in video form:

http://channel9.msdn.com/Shows/Cloud+Cover/Episode-68-Throttling-in-SQL-Azure-with-Scott-Klein

An example from the above links:

using (NorthwindEntities dc = new NorthwindEntities())
{
    RetryPolicy myPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(3);
    Employee e1 = myPolicy.ExecuteAction<Employee>(() =>
        (from x in dc.Employees
            where x.LastName == "King"
            select x).First());
}

As you can see, all you need to do is create a RetryPolicy and call its ExecuteAction function with a query wrapped in an action.

**EDIT

Example context overrides:

private RetryPolicy m_RetryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(....

public override int SaveChanges()
{
    return m_RetryPolicy.ExecuteAction<int>(() =>
    {
        return base.SaveChanges();
    });
}

// Pass anonymous query func in here
public T AutoRetryQuery<T>(Func<T> query)
{
    return m_RetryPolicy.ExecuteAction<T>(query);
}
Joshua Smith
  • 119
  • 4
  • 5
    This requires a vast amount of code rewriting if applying to an existing project, which is where the need to apply a retry at the actual connection open point in EF is required. +1 for the example though. – Adam Oct 27 '12 at 03:11
  • 1
    The connection's state can change after opening, it could fail during a query or save, at which point you'd want to catch that and retry. I don't see a way of not modifying code outside of connection opening. You can override save changes to require no code changing, but a query you would need to wrap in a retry block still. – Joshua Smith Oct 29 '12 at 21:49
  • I should rephrase, at the point it has obtained a connection handle from the pool and implemented a query on it. That is the point it needs to be wrapped around. As per my update just then, the EF team have now officially announced they have planned providing automatic retries. – Adam Nov 06 '12 at 10:35
  • Microsoft also don't really support this any more - their recommendation is the built-in retry logic in EF (they don't offer anything officially outside of EF) – O'Rooney Jun 22 '16 at 06:04
10

Since this seems one of the most popular questions on SO about Azure transient handling, I'll add this answer here.

Entity Framework does indeed have resiliency code built in (per Adam's answer)

BUT:

1) You must add code to activate it, manually

public class MyConfiguration : DbConfiguration
{
    public MyConfiguration()
    {
        this.SetExecutionStrategy(
            "System.Data.SqlClient",
            () => new SqlAzureExecutionStrategy());

        this.SetTransactionHandler(
            SqlProviderServices.ProviderInvariantName, 
            () => new CommitFailureHandler()); 
    }
...

The first method call activates retries, the second call sets a handler to avoid duplicate updates when retries happen.

Note: This class will be found and instantiated automatically, as discussed here: https://msdn.microsoft.com/en-us/library/jj680699(v=vs.113).aspx. Just make sure the class is in the same assembly as your DbContext class and has a public constructor with no parameters.

2) The built-in SqlAzureExecutionStrategy is not good enough. It doesn't cover all the transient errors. This is not surprising when you consider that the SQL Server team is working independently of Entity Framework, so they are unlikely to ever be completely in synch on what transient errors are possible. It's also difficult to figure that out yourself.

The solution we used, backed by a suggestion from another software company, is to create our own Execution Strategy, which retries every SqlException and TimeoutException, except for a few that we whitelist as not worth retrying (such as permission denied).

public class WhiteListSqlAzureExecutionStrategy : DbExecutionStrategy
{
    public WhiteListSqlAzureExecutionStrategy()
    {
    }

    protected override bool ShouldRetryOn(Exception exception)
    {  
        var sqlException = exception as SqlException;

        // If this is an SqlException then we want to always retry
        // Unless the all the exception types are in the white list. 
        // With those errors there is no point in retrying.
        if (sqlException != null)
        {
            var retry = false;
            foreach (SqlError err in sqlException.Errors)
            {
                // Exception white list.
                switch (err.Number)
                {
                    // Primary Key violation
                    // https://msdn.microsoft.com/en-us/library/ms151757(v=sql.100).aspx
                    case 2627:

                    // Constraint violation
                    case 547:

                    // Invalid column name, We have seen this happen when the Snapshot helper runs for a column 'CreatedOn'
                    // This is not one of our columns and it appears to be using our execution strategy.
                    // An invalid column is also something that probably doesn't get resolved by retries.
                    case 207:
                        break;

                    // The server principal "username" is not able to access the database "dbname" under the current security context
                    // May occur when using restricted user - Entity Framework wants to access master for something
                    // probably not transient
                    case 916:
                        break;

                    // XXX permission denied on object. (XXX = select, etc)
                    // Should not occur if db access is correct, but occurred when using restricted user - EF accessing __MigrationHistory
                    case 229:
                        break;

                    // Invalid object name 'xxx'.
                    // Occurs at startup because Entity Framework looks for EdmMetadata, an old table
                    // (Perhaps only if it can't access __MigrationHistory?)
                    case 208:
                        break;

                    default:
                        retry = true;
                        break;
                }
            }
            return retry;
        }

        if (exception is TimeoutException)
        {
            return true;
        }

        return false;
    }       
}

3) There used to be a kind of bug where EF would run the retries N^2 times instead of N, which made for much longer delays than you'd expect. (It's supposed to take up to about 26 seconds, but the bug made it take minutes.) However, this isn't so bad because in reality SQL Azure regularly has unavailability for more than a minute :( https://entityframework.codeplex.com/workitem/2849

4) If you haven't been doing so already, you really need to dispose of your DbContext after it's used. It seems this is the point that the CommitFailureHandler runs it's purging to tidy up the __TransactionHistory table; if you don't dispose, this table will grow forever (although see next point).

5) You should probably call ClearTransactionHistory somewhere in your startup or in a background thread, to clear any leftovers in __TransactionHistory.

O'Rooney
  • 2,878
  • 2
  • 27
  • 41
  • How do we use the MyConfiguration class with our context? – Dawood Awan Apr 03 '17 at 06:26
  • 1
    It's used automatically, the Entity Framework code finds your class derived from DbConfiguration and creates one. See https://msdn.microsoft.com/en-us/library/jj680699(v=vs.113).aspx – O'Rooney Apr 03 '17 at 23:15
  • Do you know of any resources that go into more depth about the __TransactionHistory table? Such as when it gets populated, why it might not be cleared etc? –  Aug 11 '17 at 13:07
  • Sorry, no. I think I did find some MS blog posts before, but they seem quite cagey about it so I can't find them again. – O'Rooney Aug 14 '17 at 01:48
  • But anyway, it gets a new row added at the start of every transaction. This row should be deleted at the end of the transaction (when the context is disposed off), but may not be if the connection is lost; in this case it must be cleaned up by something else e.g. your own manual call to ClearTransactionHistory. – O'Rooney Aug 14 '17 at 01:58
1

I was also having same issue, In my case I am using LINQ-To-SQL(I believe same applies to Entity framework also)and this is what I am planning to do.

Abstract the Linq-to-sql queries using repository pattern and wrap the Linq-to-sql code which generates database request with the retry mechanism.

For that I would suggest to create a method called ExecuteWithRetry(Action action) and then you invoke this method like this ExecuteWithRetry(()=> { db_Item item = model.db_Item.First(); });

The method could be implemented this way

private void  ExecuteWithRetry(Action action)
{
  var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), 
  TimeSpan.FromSeconds(2));

  // Define your retry policy using the retry strategy and the Windows Azure storage
  // transient fault detection strategy.
  var retryPolicy =
  new RetryPolicy<StorageTransientErrorDetectionStrategy>(retryStrategy);

  // Receive notifications about retries.
  retryPolicy.Retrying += (sender, args) =>
  {
    // Log details of the retry.
    var msg = String.Format("Retry - Count:{0}, Delay:{1}, Exception:{2}",
        args.CurrentRetryCount, args.Delay, args.LastException);
    Trace.WriteLine(msg, "Information");
  };

  try
  {
    this.retryPolicy.ExecuteAction(action);
  }
  catch (Exception ex)
  {
     Trace.TraceError(ex.TraceInformation());
    throw;
  }
 }

I am also going to try this approach, hoping that it will work

Rajeesh
  • 4,377
  • 4
  • 26
  • 29
  • It will work however that is a large amount of code change. I was hoping to place the retry at the actual connection point, when it opens. I have been doing research into and hae found out the reasons why it is so difficult. I am just doing more research to see if I can actually get down deep enough to make the change. – Adam Oct 04 '12 at 08:57
  • 1
    @Adam Trasnsient Fault can occur for many reasons, not only because of connection issue. Here is list of possible scenarios http://windowsazurecat.com/2010/10/best-practices-for-handling-transient-conditions-in-sql-azure-client-applications/ . So IMHO retrying at connection level won't solve it completely. – Rajeesh Oct 04 '12 at 09:03
  • Well not directly at the connection, but at the point where the SQL is sent, plus the surrounding open connnection, looking for particular error codes. – Adam Oct 04 '12 at 12:34
0

Actually you want to use extension methods provided by MS. Ref: http://msdn.microsoft.com/en-us/library/hh680906(v=pandp.50).aspx

"In addition, the SqlCommandExtensions and SqlConnectionExtensions classes provide a set of extension methods that enable .NET developers to open SQL Azure database connections and invoke the SQL commands. These extension methods are useful in the event that you are unable to adapt your code to take advantage of the ReliableSqlConnection class. For example, you may be using the Enterprise Library Data Access Application Block or Entity Framework that use SqlConnection instances internally. In this case, the extension methods help you add the retry capabilities offered by the Transient Fault Handling Application Block to the existing code without major re-work.

"

John E
  • 166
  • 1
  • 10
  • 1
    This still doesn't give me the information I require. How am I to override the internal connection open in the Entities class – Adam Sep 12 '12 at 15:26