locked
How to retry Data service queries on SQL Azure RRS feed

  • Question

  • I have a WCF data service based on EF Code First on top of SQL Azure db and my DbContext type is used with DataService. Like below from a example

    public class CustomerDataService : DataService<CustomerOrderContext>

    The problem is if I want to retry for queries that may be caused by sporadic SQL Azure problems, how do I do that?

    I don't seem to be able to find any extension points to add a retry logic.

    It's also interesting that I don't even need to expose any DbSet<T> in my DbContext type, WCF data service seems to be able to find the entity type classes defined within the context to make them entity sets. (I just need to give them permissions) This makes me wonder whether there is some hidden logic that hooks up with Code First so that the extension points become more impossible.

    Wednesday, June 6, 2012 3:53 AM

Answers

  • up to best of my knowledge there is not such hooks exposed where you can inject your own logic during query execution 

    If it limited to some particular entity the you can go for ServiceOperation where you can put your try catch logic, but service operation has it`s own limitation when used with client library so I won`t suggest :(

    Thanks,Ashwini

     

    • Marked as answer by Wosim Friday, June 8, 2012 12:11 AM
    Wednesday, June 6, 2012 6:16 PM

All replies

  • you can leverage "OnStartProcessingRequest" virtual function provided by DataService<T> base class from which your service is derived.

     private bool TryRefreshConnection(DbConnection Connection, int retryCount)
            {
                bool isSuccess = false;
    
                for (int i = 0; i < retryCount; i++)
                {
                    try
                    {
                        Connection.Open();
                        isSuccess = true;
                        Connection.Close();//Never miss it 
                        break;
                    }
                    catch (System.Exception)
                    {
                    }
                }
    
                return isSuccess;
            }
    
            protected override void OnStartProcessingRequest(ProcessRequestArgs args)
            {
                TryRefreshConnection(this.CurrentDataSource.Connection, 5);
            }
      

    But i would suggest not go go by this path and try to fix the Problem at the very root ,

    There is no worth of hiding such problem after all you are paying for Azure Services and there should`t be such frequent hiccups, 

    Thanks , Ashwini



    • Edited by Ashwini47 Wednesday, June 6, 2012 10:14 AM
    Wednesday, June 6, 2012 10:13 AM
  • Thanks Ashwini,

    The suggestion above can help with reducing the SQL Azure connection problem. But I am not too worried about them now because sometime last year around Aug/Sept time frame, there was a connection pool related fix that I believe will solve most of the common SQL Azure connection problems.

    My concern is more on the query execution part.

    If a query gets throttled or if some Sql Azure cluster failed over or a random SQL Azure bug, what can be the best way to retry that query before service throwing the error out to the client.

    Wednesday, June 6, 2012 5:07 PM
  • up to best of my knowledge there is not such hooks exposed where you can inject your own logic during query execution 

    If it limited to some particular entity the you can go for ServiceOperation where you can put your try catch logic, but service operation has it`s own limitation when used with client library so I won`t suggest :(

    Thanks,Ashwini

     

    • Marked as answer by Wosim Friday, June 8, 2012 12:11 AM
    Wednesday, June 6, 2012 6:16 PM
  • That's really a pity.

    I hope the product will open up more, otherwise, people may have to look at more flexible and newer solutions such as WebAPI.

    I will keep this question open for 1-2 days and mark it answered later, in case there are different opinions or other suggestions.

    Thanks.

    • Edited by Wosim Wednesday, June 6, 2012 9:13 PM
    Wednesday, June 6, 2012 9:12 PM
  • Hi Wosim 

    I understand your view point , bit I really don`t see much value fixing database layer problems at other layers and esp when you are paying for DB services (Azure) with 99.99% SLA. Also Architectural rule of thumb is Don`t try to fix the problem that is not yours so if Azure went wrong then claim your SLA, 

    Another important thing is that WebApi look like ODATA service but it does not fully confirm it and solves only a small subset of problem when compared to OData (check out these two posts) 

    But at last if you want to fix it any how only thing you can do is to write a wrapper over data driver used by entity framework however ROI of this won`t be much.

    But let if someone else could find some hack kind of thing 

    Regards

    Ashwini

    Thursday, June 7, 2012 5:30 AM