Sql Connection Error on Startup Hangs Application

[jeffsugden]

This issue occurs on startup when using the SqlServer storage package on a web application hosted on IIS. If the Sql Server connection fails for any reason, the Hangfire initialization procedure throws an exception and the application hangs.

Because the exception occurs during startup, the application will remain in this error state until the AppDomain is restarted, which we normally have to do by manually resetting the appPool in IIS.

Here is a stacktrace showing where the exception occurs, taken by temporary disconnecting the instance from the network.

[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) +1394
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +1120
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +70
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +910
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +114
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1631
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +117
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +267
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +318
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +211
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +393
   System.Data.SqlClient.SqlConnection.Open() +122
   Hangfire.SqlServer.SqlServerStorage.CreateAndOpenConnection() +68
   Hangfire.SqlServer.SqlServerStorage..ctor(String nameOrConnectionString, SqlServerStorageOptions options) +301
   Hangfire.SqlServerStorageExtensions.UseSqlServerStorage(IGlobalConfiguration configuration, String nameOrConnectionString) +86
   XXX.Web.Startup.RegisterHangfire(IAppBuilder app) in C:\XXX\Startup.cs:48
   XXX.Web.Startup.Configuration(IAppBuilder app) in C:\XXX\Startup.cs:24

Statup.cs:

    Hangfire.GlobalConfiguration.Configuration.UseSqlServerStorage(dbConnectionString);
    Hangfire.GlobalConfiguration.Configuration.UseAutofacActivator(container, useTaggedLifetimeScope: false);
    app.UseHangfireDashboard("/hangfire");
    app.UseHangfireServer();

Hangfire Versions: Core 1.5.4.0 SqlServer 1.5.4.0 Windows Server 2012 R2 IIS 8.5

Also it looks like issue may be related: https://github.com/HangfireIO/Hangfire/issues/273

[odinserj]

So what happens with the network on that instance? I'm unsure whether it helps, if I add an immediate retry logic in case of an exception to the Hangfire code base. You also can set the SqlServerStorageOptions.PrepareSchemaIfNecessary property to false, and use SqlServerObjectsInstaller class manually:

using (var connection = CreateAndOpenConnection())
{
    SqlServerObjectsInstaller.Install(connection, options.SchemaName);
}

[jeffsugden]

This Sql Server is at a remote location across a VPN link. The network outages that we have measured last several minutes, so an immediate retry would not help there.

I updated the application to use with SqlServerStorageOptions.PrepareSchemaIfNecessary property set to false. Testing confirms that this does allow the AppDomain to start properly, so the application does not hang. That will be good enough for the needs of this application, I will just make a note to do any schema update(s) manually for future updates.

Would it be beneficial to allow Hangfire to defer the database schema initialization if it fails, and continue to try the initialization in the background? This way the AppDomain could at least start properly. Though I suspect that it may be difficult implement in that way.

[justinmcdaniel]

I have a similar issue to this (might be the same). I have followed the guide for Auto-Start, and that's working great, but if the SQL Server just happens to be down/unreachable at the time the App Pool is recycling, the App Pool will crash. This brings down the website until I manually log onto the web server and restart the App Pool.

Is using the "PrepareSchemaIfNecessary" option still the best solution?

Attached is a copy of the XML event from the windows event viewer. Here is a SS for a quick glance (otherwise click the link for the *.TXT file after). HangfireSQLStartupInPreloadError.txt

[Kirlac]

Is there any update on this yet? We have an Azure SQL instance set up for our staging server which will shut down after an extended period of inactivity and wake back up when needed. This cold start delay seems to cause hangfire to throw an error on startup and lock our application in an error state until we manually restart it. Is there some way we can configure this so that if it fails it doesn't prevent the application from starting and can then just periodically retry (ie with an exponential backoff) in the background?

[yashvit]

We are facing a similar issue. The application hangs and everytime hangfire tries to queue a bg job, we get the same errors. Restarting the app fixes the issue - until it occurs again.