Connection resiliency

[rowanmiller]

No description provided.

[michaelaird]

Is there any progress on this? This is a very important feature for anyone using SQL Azure.

[christiannagel]

I would like to know this as well - will there be a replacement for SetExecutionStrategy, SqlAzureExecutionStrategy? Thanks!

[rowanmiller]

Hey, there will be something along the lines of execution strategies from EF6 but it will likely look a little different. We haven't started work on this item yet.

[michaelaird]

Glad to hear it's coming. Is this on the roadmap for 1.0? if not, i would suggest you explicitly state that SQL Azure is not a supported database.

[julielerman]

Ditto the roadmap question. We've got a client that is moving from ado.net to Azure SQL Server and right to EF7 would be perfect. I believe that the ONLY thing that would stop them from using EF7 is the lack of transient connection handling built in. Thanks

[rowanmiller]

It's not something our team will be tackling before the 7.0.0 release (purely due to time constraints). We haven't done any planning of what order we will tackle features after that release yet. We will do that planning (and share it publically) closer to the 7.0.0 release.

Sorry for the not so helpful response... but it's the honest one 😄

[julielerman]

thanks. and passed on. I know it will get in there eventually. I don't think people should make "lack of feature X is showstopper and therefore we can never use EF" decisions based on the initial RTM.

[ErikEJ]

@julielerman NET 4,6,1 contains a number of connection reciliency features, that you can use with EF7 https://msdn.microsoft.com/en-us/library/dn632678.aspx
http://blogs.msdn.com/b/dotnet/archive/2015/11/30/net-framework-4-6-1-is-now-available.aspx

[julielerman]

haha yep. I told them to just use stuff we had before EF6's built in support. Now I'll give them an actual link! :)

[ErikEJ]

But the transinent retry feature is new in 4,6,1

[julielerman]

oh oh oh! yay, duh (4.6.1) & thanks. How do you keep up with so many things?

[bmrpatel]

This is great! So adding ConnectRetryCount and ConnectRetryInterval to connectionString will do the magic? Its off topic but technically this will add connection resiliency to Linq to Sql as well. Correct? 👍

[michaelaird]

I believe the change in 4.6.1 was specific to connection.open() and not related to any of the other transient failures you will frequently experience with SQL Azure.

I'm fine with waiting for this feature before moving to EF 7 but I think it's important to explicitly state that SQL Azure is not supported by EF7 RTM. Otherwise, people are going to have a very bad experience with it.

[ArieJones]

@michaelaird is correct. There are a lot of other transient failures that need to be taken into consideration with SQL Azure.

TBH this is a let down as most of my applications are now cloud based and ,.....using SQL Azure... which is a step backwards since this had gotten to the point where it was so easy to configure in 6.

[ErikEJ]

@michaelaird @ArieJones given that Conection.Open is called before each command execution, I would imagine that the 4,6,1 improvements would cover many scenarios.

[ArieJones]

Yes, but there is a big difference in a retry in the opening of a connection vs. what the connection resiliency feature provided in 6..... which was the retrying of a command whenever one of a number of known transient errors would occur.

Saves a lot of effort in terms of having to write all the defensive code to retry whatever you were attempting to do when the blip in the ol' network occurred.

@divega alludes to as much in #2004

[michaelaird]

Hey @rowanmiller ,

This is now getting totally off-topic but...in reviewing the .net 4.6.1 source to respond to this thread, I think I've spotted a bug/error.

This code http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlInternalConnectionTds.cs,457 appears to be the list of SQL errors that will be retried. 40020, 40143, 40166, and 40540 are actually error codes that are embedded in SQL Error # 40197 and not SQL Errors that should be retried. (see this document: https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-error-messages/#transient-faults-connection-loss-and-other-temporary-errors )

Can you help me pass this on to the appropriate team?

[michaelaird]

We actually log nearly all of the SQL errors we encounter (I overrode the SQLAzureExecutionStrategy for this purpose) so I ran a query on all the errors we encountered in 2015. I have to say, I was quite surprised at the result.

Of our top 10 errors, # 40613 accounted for 63%! This one will be handled by the new conn.open retry logic which is great news. The bad news is that the next biggest one, clocking in at 28%, was error number -2 (timeout expired). This one can happen during connection or at any time.

So, judging by our real-world experience, the new connection resiliency features aren't going to handle all the random transient failures that can crop up but they are going to catch the most common ones at the lowest possible level.

[ErikEJ]

@michaelaird Interesting find - also interesting to compare with the current implementation in EF6: https://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework.SqlServer/SqlAzureRetriableExceptionDetector.cs

[rowanmiller]

@michaelaird SQL Client is handled under https://github.com/dotnet/corefx... it would be great if you want to file an issue there for the error code issue you spotted.

[michaelaird]

Got it! I will file an issue there. Thanks.

On Wednesday, 30 December 2015, Rowan Miller notifications@github.com
wrote:

@michaelaird https://github.com/michaelaird SQL Client is handled under
https://github.com/dotnet/corefx... it would be great if you want to file
an issue there for the error code issue you spotted.

—
Reply to this email directly or view it on GitHub
#237 (comment)
.

[rowanmiller]

Currently planned for 1.1 - see https://blogs.msdn.microsoft.com/dotnet/2016/07/29/entity-framework-core-1-1-plans/ for details.

[AndriySvyryd]

This has been implemented in 48cb312