Ado.net Connection Pool Concurency Issue With Sql Azure Or How We Cannot Scale Out Our Web App

We are having issues to scale out our ASP.NET Web API on Azure because of our SQL Azure DB. The issue is that our Web/Business SQL Azure DB supports a maximum of 180 concurrent req

Solution 1:

Thanks Tim, I think you're right. There are 2 main design issues in our code. 1st, we do not close the connection fast enough. We let our DI(Autofac) dealing with opening and closing the connection rather than dealing with this on a per-method basis. 2nd, some of our queries are very very badly written on top of EF, which creates an even worse mess. We are completely replacing EF with Dapper so we can gain complete control of our insert/update queries. Once we've achieved that, we should be able to get normal performance for our writes(~30-60ms). Once we have reach that point, I guess this is just pure probabilities that the likelihood of having more than 180 concurrent requests to the DB is low enough to consider it a non-problem. Mind due, inside our repository, I guess we could easily wrap our request inside a retry mechanism that would deal with those rare events. What do you think?