Webjob Errors since move to Azure Sql v12

Question

Each morning at 4am a Scheduled Task creates around 500 messages on a message queue. Each message is an id of an email to send. Each message is picked up and a url created and sent via await HttpClient.GetAsync(url) The url target then creates and sends the email. This has worked well for months.

I've just upgraded to SQL Azure v12 and all is now not well.

The very first messages to be processed (after 2 minutes running time) threw a

"System.Threading.Tasks.TaskCanceledException"

I'm also seeing

"System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

and a couple of

"The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=6; handshake=426; [Login] initialization=0; authentication=0;"

The webjob that sends the request to the api is awaiting a response. I'm wondering if because it's async, while awaiting the response the thread is freed to go off and processes another queue item - and therefore creates another api request, essentially this hits the api again and again until there are so many requests being processed by the api all the theads are in use - and that I might be better off NOT making the webjob async because then it (the 'trapped' thread) would send a request only after the first request completes? Is that right? edit: actually the IIS logs suggest that the api requests are not all happening at once. So my question is "what should I look at next? Are these common SQL v12 errors or is the recent upgrade a red-herring?"

just to clarify, the webjob that fires in response to the queue message simply does: using (HttpClient client = new HttpClient()) { response = await client.GetAsync(url); }

and hits the web api of an Always On standard tier azure website. Database DTU % is about 25% while this happens.

edit:

"Gateway no longer provides retry logic in V12 Before version V12, Azure SQL Database had a gateway that acted as a proxy to buffer all interactions between the database and your client program. The gateway provided automated retry logic for some transient errors.

V12 eliminated the gateway. Now your program must more fully handle transient errors."

adding DbConfiguration class for SqlAzureExecutionStrategy. Will so how it runs tonight.

Neil Thompson Neil Thompson · Accepted Answer · 2015-08-02T09:15:21

Adding the EF retry config class fixed the transient errors. The cancelled tasks are a different issue (new question)

//https://msdn.microsoft.com/en-us/data/jj680699
public class SqlAzureConfiguration : DbConfiguration
{
    public SqlAzureConfiguration()
    {
        this.SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy());
    }
}

and in web.config (because I have multiple contexts)

<entityFramework codeConfigurationType="Abc.DataService.SqlAzureConfiguration, Abc.DataService">

Webjob Errors since move to Azure Sql v12

1 Answers