Randomly WebJob will failed

Question

I used Web Jobs for running my function continuously in background, its working fine in local machine and azure. But randomly this web job will failed and gives the error looks like below.

Microsoft.Azure.WebJobs.Host.FunctionInvocationException: Exception while executing function: Functions.ImportVehicles ---> System.Data.SqlClient.SqlException: 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.ComponentModel.Win32Exception: The system cannot find the file specified --- End of inner exception stack trace --- at 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) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at

This above exception occurs randomly and not able to get/reproduce it every time.

I know that the error would seem to indicate that this is a transient connection issue between Azure and SQL. However, I have configured the data context to use the SqlAzureExecutionStrategy which should be handling dropped connections.

Answer 1

The SqlAzureExecutionStrategy will retry the operation on TimeoutException and SqlException if the Errors contains any of the following error numbers: 40613, 40501, 40197, 10929, 10928, 10060, 10054, 10053, 233, 64 and 20. (from documentation)

The errornumber you are getting is Error 40. This is probably since you're using Named Pipes instead of TCP/IP connections. I suggest you switch to using TCP/IP (by adding tcp: in front of your connectionstring) and try again.

Answer 2

From the stack trace (correct me if I'm wrong), it looks like you're using plain ol' ADO.NET to connect to and interact with your Azure SQL Database. If that is the case, then the SqlAzureExecutionStrategy does NOT come into play because with ADO.NET, there is no automatic retry due to transient failures.

Transient failures can (and will) occur. You need to use a retry policy to make sure that your app can handle them. There's a few options out there. My personal favorite is just to use Entity Framework. You'll have to check the version number to be sure, but I believe automatic retry was introduced in v6.