突然间，SQL Azure上出现大量“等待操作超时"问题 [英] All of a sudden getting lots of Wait Operation Time out issues on SQL Azure

问题描述

两天前，没有代码更改或对数据库的更改，我并没有收到很多(每5分钟左右)带有The wait operation timed out错误的错误，其中有关于预登录和登录的两个不同的下划线的完整错误.另一个关于帖子:

Two days ago, with no code changes or changes to the DB, I am not getting a lot (every 5 minutes or so) of errors with The wait operation timed out error with two different underlining full errors on about the pre-login and the other about the post:

System.Data.Entity.Core.EntityException:基础提供程序在打开时失败. ---> System.Data.SqlClient.SqlException:连接超时已过期.尝试使用登录前握手确认时，超时时间已过.这可能是因为登录前握手失败或服务器无法及时回复.尝试连接到该服务器所花费的时间为-[登录前]初始化= 21；握手= 14988; ---> System.ComponentModel.Win32Exception:等待操作超时

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21; handshake=14988; ---> System.ComponentModel.Win32Exception: The wait operation timed out

System.Data.Entity.Core.EntityException:基础提供程序在打开时失败. ---> System.Data.SqlClient.SqlException:连接超时已过期.登录后阶段已过超时时间.等待服务器完成登录过程并做出响应时，连接可能已超时.或者尝试创建多个活动连接时可能已超时.尝试连接到路由目标时发生此故障.尝试连接到原始服务器时所花费的时间为-[登录前]初始化= 5；握手= 3098; [登录]初始化= 0;认证= 0; [登录后] complete = 7;尝试连接到该服务器所花费的时间为-[登录前]初始化= 20；握手= 5; [登录]初始化= 0;认证= 0; [登录后]完成= 11003； ---> System.ComponentModel.Win32Exception:等待操作超时

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. 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=5; handshake=3098; [Login] initialization=0; authentication=0; [Post-Login] complete=7; The duration spent while attempting to connect to this server was - [Pre-Login] initialization=20; handshake=5; [Login] initialization=0; authentication=0; [Post-Login] complete=11003; ---> System.ComponentModel.Win32Exception: The wait operation timed out

我正在使用Entity Framework，并且我的网站托管在Azure Web App上.我做了一些挖掘工作，发现的大多数SO问题都与Entity Framework不相关，但是ADO.Net的一些发现使我从DB的基本服务升级为标准(S0)服务，并使用

I am using Entity Framework and my web site is hosted on an Azure Web App. I have done some digging and most SO questions I find about this are NOT related to Entity Framework but ADO.Net the few posts I found lead me updated from a Basic to Standard (S0) service for the DB and creating a GlobalDBConfig with

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

如何找出其他问题并解决?这是一个非常简单的数据库，具有简单的查询，并且访问该网站的流量很少(每天少于1000次访问)

How can I figure out what else is going wrong and fix it? This is a very simple DB with simple queries and very little traffic to the site (less then 1000 visits a DAY)

推荐答案

我们通过切换到包含用户"，解决了此问题以及SQL Azure上其他类型的随机超时问题.在SQL Azure上使用服务器级别的登录名可能会引起问题:

We resolved this issue, along with other types of random timeouts on SQL Azure by switching to "contained users". Using server-level logins on SQL Azure can cause issues:

这不是很有效，因为在SQL DB master中，用户可以坐在 两个不同的SQL服务器可能位于两个不同的计算机中.还 当服务器具有多个用户数据库时，主服务器将成为 登录过程中的瓶颈，在负载下可能会导致 登录响应时间长.如果Microsoft正在更新软件 在机器/服务器上，那么主机将无法使用 秒，到用户数据库的所有登录也会在此失败 时间( http://www.sqlindepth.com/contained- users-in-sql-azure-db-v12/)

This is not very efficient as in SQL DB master and user can sit on two different SQL servers potentially in two different machines. Also when a server has multiple user databases then master will be the bottleneck in the login process, and under load this may result in high response time for logins. If Microsoft is updating the software on the machine / server then master will be unavailable for a few seconds and all the logins to the user database can fail too at this time (http://www.sqlindepth.com/contained-users-in-sql-azure-db-v12/)

就像您的情况一样，我对此表示怀疑，因为我的数据库负载不大，但是切换到包含用户仍然产生了巨大的变化.

As in your case, I had my doubts because my database was not under heavy load, but switching to contained users made a tremendous difference anyway.

创建这些用户的SQL如下(在数据库本身上运行此命令，而不是像创建服务器级登录名那样在master数据库上运行):

The SQL to create these users is as follows (run this on the database itself, not on the master database as you would for creating server-level logins):

Create user ContainedUser with password = 'Password'
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [ContainedUser]
ALTER ROLE [db_owner] ADD MEMBER [ContainedUser]

这篇关于突然间，SQL Azure上出现大量“等待操作超时"问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！