Geek Answers Handbook

Suddenly there are problems with expectations waiting for waiting on SQL Azure

Two days ago, without any code changes or changes to the database, I do not get many (every 5 minutes or so) errors with the The wait operation timed out error with two different underscores of the complete errors about pre-registration and another message:

System.Data.Entity.Core.EntityException: The underlying provider could not open Open. ---> System.Data.SqlClient.SqlException: connection timed out. The waiting period has expired while trying to use an authorization confirmation confirmation before entering the system. This may be due to the fact that the handshake before entering the system failed or the server was unable to respond on time. The duration spent trying to connect to this server was - [Pre-Login] initialization = 21; acknowledgment = 14988; ---> System.ComponentModel.Win32Exception: timeout

System.Data.Entity.Core.EntityException: The underlying provider could not open Open. ---> System.Data.SqlClient.SqlException: connection timed out. The waiting period expires during the phase after entry. The connection may be disconnected while waiting for the completion of the login process and a response; Or it may result in a timeout when trying to create multiple active connections. This error occurred while trying to connect to the routing destination. The duration spent trying to connect to the source server was - [Pre-Login] initialization = 5; acknowledgment = 3098; [Login] initialization = 0; Authentication = 0; [Post-Login] complete = 7; The duration spent trying to connect to this server was - [Pre-Login] initialization = 20; acknowledgment = 5; [Login] initialization = 0; Authentication = 0; [Post-Login] complete = 11003; ---> System.ComponentModel.Win32Exception: timeout

I am using Entity Framework and my website is hosted on Azure Web App. I did some digging, and most of the SO questions that I find in this are NOT Entity Framework-specific, but the ADO.Net few messages I found led me to upgrade from the Basic to Standard (S0) service for the database and create GlobalDBConfig via

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

How can I find out what is still wrong and fix it? This is a very simple database with simple queries and very little traffic to the site (less than 1000 visits per day)

+6
source share
3 answers

We solved this problem along with other types of random timeouts in SQL Azure by switching to "contained users." Using server accounts on SQL Azure can cause problems:

This is not very efficient, as in the main SQL DBMS, and the user can sit on two different SQL servers, possibly on two different machines. Also, when the server has several user databases, then the wizard will be a bottleneck in the process of logging in, and when loading it can lead to high response time for logins. If Microsoft updates the software on the machine / server, then the wizard will be unavailable for several seconds, and all logins in the user database may also crash time ( http://www.sqlindepth.com/contained-users-in-sql-azure- db-v12 / )

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

The SQL for creating these users is as follows (run this in the database itself, and not in the main database, as for creating server logins):

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

Here are a few options: I highly recommend switching from (1) and (3), if possible

  • User Database Firewall Rules and User Authentication Included
  • Increase the connection timeout to a large value (60-120 seconds?)
  • If possible, update the client drivers to the latest version (7.4 and higher).
0
source

We had similar problems, and note that there is no such thing as automatic scaling of standalone databases on Azure, and since you are using the Entity Framework, here are some tips below

  • If you call the Web API to retrieve and transaction with your Azure SQL database, make sure you set the ALLWAYS ON option for the web API on the Azure portal.

  • Then your client application should probably try again if it cannot connect on the first try.

  • If the database queries result in timeouts due to the amount of data and indexes that cannot catch it, you need to slightly increase the execution time of the commands, and most importantly, you will need to update the statistics in the database and recompile all the objects in the database .

0
source

More articles:


All Articles