.NET Core process crashing due to an SQL connection pool exception

▲
0
▼

I recently migrated a .NET Framework 4.6.1 application to .NET Core 2.1 and the process is crashing when the SQL AZure database becames unavailable. The process runs on Windows as a Service.

The problem itself is not the exception (it is an expected condition of SQL Azure) but the fact that I'm unable to handle the exception since it is not occurring in my code directly. It seems the exception is occurring in the SQL connection pool.

I collected the stack trace below by listening the AppDomain.CurrentDomain.UnhandledException event:

System.Data.SqlClient.SqlException (0x80131904): Database 'XXXXXX' on server 'XXXXXX' is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX'. at new System.Data.SqlClient.SqlInternalConnectionTds(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, object providerInfo, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling) at DbConnectionInternal System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at DbConnectionInternal System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at DbConnectionInternal System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at void System.Data.ProviderBase.DbConnectionPool.PoolCreateRequest(object state) at bool System.Threading.ThreadPoolWorkQueue.Dispatch() ClientConnectionId:3873debc-b302-44f5-820a-8f760b1634e3 Error Number:40613,State:1,Class:14

This is not a new code and the behavior seems different when running on .NET Framework, where the process didn't stop.

c#
.net
.net-core
azure-sql-database
asked on Stack Overflow Nov 6, 2018 by André Bires • edited Nov 6, 2018 by phuzi

2 Answers

▲
0
▼

The reason is that there is a bug in the package System.Data.SqlClient for versions until 4.5.0

https://github.com/dotnet/corefx/issues/14615#issuecomment-389248862

answered on Stack Overflow Nov 6, 2018 by André Bires
▲
0
▼

If your client program connects to SQL Database by using the .NET Framework class System.Data.SqlClient.SqlConnection, use .NET 4.6.1 or later (or .NET Core) so that you can use its connection retry feature. For more information on the feature, see this webpage.

When you build the connection string for your SqlConnection object, coordinate the values among the following parameters:

ConnectRetryCount: Default is 1. Range is 0 through 255.

ConnectRetryInterval: Default is 1 second. Range is 1 through 60.

Connection Timeout: Default is 15 seconds. Range is 0 through 2147483647.

Specifically, your chosen values should make the following equality true:

Connection Timeout = ConnectRetryCount * ConnectionRetryInterval

For example, if the count equals 3 and the interval equals 10 seconds, a timeout of only 29 seconds doesn't give the system enough time for its third and final retry to connect: 29 < 3 * 10.

More info: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues#net-sqlconnection-parameters-for-connection-retry

Or install Polly.Net:

Install-Package Polly

More about that found on their GitHub: https://github.com/App-vNext/Polly

answered on Stack Overflow Nov 6, 2018 by JP Hellemons

User contributions licensed under CC BY-SA 3.0