Best Practices to Prevent Request Denials or Connection Termination
To prevent request denials or connection termination in SQL Database, you must manage the application resources properly in your SQL Database applications. To provide a seamless user experience when a connection is closed, re-establish the connection, and then re-execute the failed commands or the query. The following sections provide a list of recommended coding practices when connecting to SQL Database. These recommended coding practices are not significantly different than the coding practices that apply to on-premises SQL Server.
Implement Retry Logic in your Application
The SQL Database TDS Gateway retries connections for about 30 seconds before reporting a failure. If you expect a high volume of application traffic, build retry logic in your application. If a connection fails, do the following:
Handle idleness and transient disconnections.
Note Install Reliability Update 1 for .NET Framework 4.0 that fixes the issue of SqlClient returning dead connections to the application. With this update, SqlClient checks if the connection in the pool is dead before returning it to the application. If the connection is dead, SqlClient reconnects before returning it to the application. For more information about this issue, see Minimizing Connection Pool errors in SQL Database.
Retry to connect to SQL Database in intervals of 10 seconds until the resources are available and your connection is established again. Depending on your application, databases, and network workload, you must increase the delay time as necessary.
Change your workload if a connection is terminated again. If a connection is terminated again, look at the error code, find out the real problem, and then try to change your workload. You can implement a queue or a delay mechanism in your client application to reduce your workload.
Another solution could be to redesign your application and database to remove resource bottlenecks. Ensure that your application do not overload tempdb through excessive DDL or DML operations. In addition, ensure that transactions do not block any resource. When appropriate, consider partitioning your database into multiple databases.
Ensure you code can recover from throttling. Users should assume that connections can disconnect at any time in an application. Therefore make a batch request in a single transaction whenever possible. This minimizes the cases where a batch could partially complete and put the database in a state that is unexpected/untested. To do this, you might want to do BEGIN TRANS/COMMIT TRANS around each ad hoc batch, and at the start/end of each stored procedure.
Understand how retry logic impacts the correctness of an application (idempotency). When performing updates or inserts, it is important to understand what happens when a connection is disconnected before success is returned. If the transaction is aborted, then retrying the connection is the right step. If the transaction is actually committed then running the transaction again might not be correct. Users performing changes that are non-idempotent may need to modify their logical database schema to be able to detect repeated transaction commits based on the retry logic being utilized to protect against disconnects.
Log Throttling Errors
Use the retry code to log throttling errors to distinguish between transient connection, throttling and hard failure syntax, missing stored procedures, and so on. This becomes particularly useful in troubleshooting scenarios if you are unable to connect to the SQL Database. In this case troubleshooting efforts will focus on logged information; and the ability to effectively troubleshoot will correlate to the quality of the logged information. For more information about implementing logging in SQL Database applications, see Implementing Logging for Windows Azure SQL Database Applications.