Processing.Tasks database retriers do not reopen connection if failover happens after transaction begins

  • Description

    The issue is applicable to solutions with Always On configured for Processing.Tasks database.

    If manual failover (via SQL Management Studio) is performed during the time when a state information about the history rebuild process is being stored or retrieved, the state might not be put to or got from the database after the failover. In addition, the following exceptions may be thrown:

    System.InvalidOperationException : This SqlTransaction has completed; it is no longer usable.
    at System.Data.SqlClient.SqlTransaction.ZombieCheck()
    at System.Data.SqlClient.SqlTransaction.Commit()   
    at Sitecore.Analytics.Aggregation.History.SqlRebuildProcessStateDataProvider.<SetFailureAsync>d__4.MoveNext()
    ...
    
    System.Data.SqlClient.SqlException : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
      ----> System.ComponentModel.Win32Exception : The specified network name is no longer available
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
    at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
    at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
    at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
    at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) 
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 
    at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) 
    at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) 
    at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName, Boolean shouldReconnect) 
    at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, String transactionName)
    at Sitecore.Analytics.Aggregation.History.SqlRebuildProcessStateDataProvider.<AddStateAsync>d__1.MoveNext()
    ...
    
    System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception (0x80004005): An existing connection was forcibly closed by the remote host
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)  
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)  
    at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
    at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
    at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
    at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
    at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
    at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
    at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName, Boolean shouldReconnect)
    at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, String transactionName)
    at Sitecore.Analytics.SqlCursorDataProvider.<SaveSplitAsync>d__61.MoveNext()
    ...
    

    The cause of the issue is the failover being initiated in the middle of a transaction. Retrying in the middle of a transaction is not supported.

    Refer to the following article from Microsoft where this behavior is stated as expected and other error messages are described: Idle Connection Resiliency.The connection resiliency feature is supported on Microsoft Azure SQL Databases and SQL Server 2014 (and later versions).

Applies to:

CMS 9.0 Initial Release+

June 24, 2019
June 24, 2019

Reference number:

332036