Symptoms
Assume that you use Microsoft SQL Server 2016 or 2017. When an availability group joins an existing distributed availability group (DAG) immediately after the DAG is dropped and recreated, it could fail to join the DAG and you receive the error messages that resemble the following:
Always On: Process AG configuration change notification for AG 'AGName' in state 'FORWARDER' (7).Error: 41162, Severity: 16, State: 0.Failed to validate sequence number of the configuration of availability group 'AGName'. The in-memory sequence number does not match the persisted sequence number. The availability group and/or the local availability replica will be restarted automatically. No user action is required at this time.Always On: AR 'AGName' is now processing notification (type 64).Always On: Process AG configuration change notification for AG 'AGName' in state 'FORWARDER' (7).Always On: AR 'AGName' is now validating AG integrity in WSFC.Always On: AR 'AGName' role transition [FORWARDER] --> [FORWARDER], trigger [VALIDATE_AG_CONFIG], state (wsfc = 1, metadata = 1).Always On: AR 'AGName' is now processing notification (type -2).
In addition, error 41162 can bring AG resolving state and it may cause two other issues: error 19407 and assertion failure.
Error 19407:
Nonqualified transactions are being rolled back in database DBName for an Always On Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required.[HaDrDbMgr::SetPrimaryAR] Setting primary as AGID: AGNumber, ReplicaID: ReplicaNumber, AGDBID: AGDBNumberError: 19407, Severity: 16, State: 2.The lease between availability group 'GroupName' and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.
Assertion:
Always On: Process AG configuration change notification for AG 'DatabaseName' in state 'RESOLVING_NORMAL' (0).
Always On: AR 'DatabaseName' is now validating AG integrity in WSFC.
Always On: GetTransportWithRef() is rejected, because local AR is not online.
State information for database 'DatabaseName' - Hardened Lsn: '(34:304752:1)' Commit LSN: '(0:0:0)' Commit Time: 'Jan 1 1900 12:00AM'
RECOVERY (DatabaseName, 6): Starting to stop parallel redo workers
**Dump thread - spid = 0, EC = 0x000001F280CC7250
***Stack Dump being sent to FileLocation
* BEGIN STACK DUMP:
* Location: "FileLocation":1774
* Expression: GetContext ()->GetController ()->GetHadrArRoleExternal () == HADR_ROLE_FORWARDING_SECONDARY
* SPID: SPId
* Process ID: ProcessId
Error: 17066, Severity: 16, State: 1.
SQL Server Assertion: File: <"Filelocation">, line=1774 Failed Assertion = 'GetContext ()->GetController ()->GetHadrArRoleExternal () == HADR_ROLE_FORWARDING_SECONDARY'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
Error: 3624, Severity: 20, State: 1.
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Resolution
This issue is fixed in the following cumulative update for SQL Server:
About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
On-Demand Hotfix Information:
This issue is fixed in the following on-demand hotfix for SQL Server:
References
Learn about the terminology that Microsoft uses to describe software updates.
