Semaphore timed out SQL Azure

Question

I run the .Net MVC Azure website with an Azure SQL database accessed using Entity Framework 6. With interruptions (1 out of a thousand requests) I get the error "System.ComponentModel.Win32Exception: semaphore timeout period expired"

System.Data.SqlClient.SqlException: transport-level error while receiving results from the server. (provider: TCP Supplier, error: 0 - Semaphore timeout expired.) ---> System.ComponentModel.Win32Exception: semaphore timeout period has expired

There seems to be no reason for this, both the before and after the error, and their interaction with SQL Azure is excellent. Is there a way to handle or resolve this.

Answer 1

Azure SQL is very different from SQL SQL. When Azure SQL Server reboots or drops, it will disconnect several connections, and when you reconnect, you will receive a message to another SQL Server.

However, with TCP connections, you do not know if the other end terminated the connection unless you actually sent information to it, so this error occurs.

As soon as your code finds out about the end of the connection, it establishes a new connection for the next request, which will work fine.

With Entity Framework 6, you can deal with Transitional Error Handling using SQL Azure using Entity Framework

In your DBConfiguration class, you need to set SetExecutionStrategy and configure it. Just create a new class in your project and inherit it from DbConfiguration.

public class MyConfiguration : DbConfiguration { public MyConfiguration() { SetExecutionStrategy( "System.Data.SqlClient", () => new SqlAzureExecutionStrategy(1, TimeSpan.FromSeconds(30))); } } 

Details on Connection / Retry Failover Logic (EF6 onwards)

Answer 2

There is a problem with the information when this happens during the VIP exchange, and the EF6 restart strategy does not help in this scenario.

https://social.msdn.microsoft.com/Forums/azure/en-US/5e195f94-d4d2-4c2d-8a4e-7d66b4761510/vip-swap-and-the-semaphore-timeout-period-has-expired-errors? forum = ssdsgetstarted & prof = required