-> Below messages were found in SQL Server error log,
2021-01-29 05:23:22.870 spid75s Always On Availability Groups connection with secondary database terminated for primary database ‘JBDB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
2021-01-29 05:23:22.870 spid74s Always On Availability Groups connection with secondary database terminated for primary database ‘JBREPL_SUB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
2021-01-29 05:23:22.870 spid35s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:4
2021-01-29 05:23:25.160 spid35s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.160 spid41s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:4
2021-01-29 05:23:25.160 spid35s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.160 spid35s DbMgrPartnerCommitPolicy::SetSyncState: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.170 spid41s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.170 spid41s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.170 spid41s DbMgrPartnerCommitPolicy::SetSyncState: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.170 spid75s A connection for availability group ‘JBSUB’ from availability replica ‘JBSUB-PRIMARY’ with id [1F96128A-7100-4235-89BB-1CD4903BAD6B] to ‘JBSUB-DR’ with id [6A9D0674-6DEC-4810-B731-C9CDE79CC62D] has been successfully established. This is an informational message only. No user action is required.
2021-01-29 05:23:25.180 spid50s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.180 spid35s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.180 spid50s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.180 spid35s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.180 spid50s Always On Availability Groups connection with secondary database established for primary database ‘JBREPL_SUB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
2021-01-29 05:23:25.180 spid50s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 6A9D0674-6DEC-4810-B731-C9CDE79CC62D:1
2021-01-29 05:23:25.180 spid35s Always On Availability Groups connection with secondary database established for primary database ‘JBDB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
-> In my case, I saw below 2 informational messages. But there were no failover or any issues with regards to Always On encountered. The message is displayed as “informational message only. No user action is required.” and I wanted to understand if we should worry about this or just ignore as it is an informational message,
2021-01-29 05:23:22.870 spid75s Always On Availability Groups connection with secondary database terminated for primary database ‘JBDB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
2021-01-29 05:23:25.180 spid35s Always On Availability Groups connection with secondary database established for primary database ‘JBDB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
-> As per article, Hard and Soft errors can cause a failure in a session between two availability replicas.
Hard Error
Availability replica does not regularly check components that Sqlservr.exe depends to see if it working or have failed. For some failures, the failed component will report an error to Sqlservr.exe and this is called Hard Errors.
Failures due to Hard Errors
-> A broken connection or wire
-> A bad network card
-> A router change
-> Changes in the firewall
-> Endpoint reconfiguration
-> Loss of the drive where the transaction log resides
-> Operating system or process failure
Soft Error
Always On session-timeout mechanism is employed to detect other failures that would possibly go unnoticed. Session-timeout, specified in seconds is the time-out period that Always On waits to receive a PING response from the partner before considering that the partner is down/disconnected. When a session timeout occurs, Availability group assumes that a failure has occurred and declares a soft error.
Failures due to Soft Errors
-> Network errors such as TCP link time-outs, dropped or corrupted packets, or packets that are in an incorrect order.
-> An operating system, server, or database that is not responding.
-> A Windows server timing out.
-> Insufficient computing resources, such as a CPU or disk overload, the transaction log filling up, or the system is running out of memory or threads. In these cases, you must increase the time-out period, reduce the workload, or change the hardware to handle the workload.
Session-Timeout
Soft errors are not detectable or reported directly to server instance and this can cause an availability replica to wait indefinitely for a response from its partner. Session timeout is implemented to prevent this scenario. Session-timeout, specified in seconds is the time-out period that Always On waits to receive a PING response from its partner before considering that the partner is down/disconnected. A response received within the timeout period indicates that the partner is available.
-> With all the details above, lets revisit the message and understand why it occurs.
-> Below messages related to connection terminated or established are written in SQL Server error log while session-timeout check happens. When the ping response from the partner is not received, below informational messages are written in SQL Server error log,
2021-01-29 05:23:22.870 spid75s Always On Availability Groups connection with secondary database terminated for primary database ‘JBDB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
Below informational messages are written in SQL Server error log as soon as a response after termination is received from the partner,
2021-01-29 05:23:25.180 spid35s Always On Availability Groups connection with secondary database established for primary database ‘JBDB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
-> In our case, connection terminated and established with the session-timeout period which is 10 seconds, hence the partner did not enter the disconnected state.
-> Lets try reproducing this in below environment,
Environment
-> Environment consists of two (2) Windows 2016 virtual machines. These servers were configured as nodes of a two (2) node Windows Server Failover Cluster (without shared storage). An instance of SQL Server 2017 Enterprise Edition is installed on each server; each instance act as an Always-On Availability Group Replica. The replica JBSUB-PRIMARY will be a member of an Automatic Failover set with synchronous commit to the replica JBSUB-DR.
-> JBSUB-Primary is the primary replica and JBSUB-DR is secondary replica.
-> Below are the details of the environment,
-> Below is the snapshot of SQL Server error log,
-> We will try to mimic a network issue/packet drop by dropping all receiving packets on JBSUB-DR for 9 seconds using Clumsy tool.
-> Below is the setting that will be used in clumsy tool on JBSUB-DR,
-> I will be using below script to stop the tool in 9 seconds,
-> I will execute “Network_Drop.CMD” first and when it reaches 9 seconds, clumsy will be started.
-> Below is what I see when I start Clumsy.
-> Below is what I see in SQL Server error log on JBSUB-PRIMARY,
-> Below is what I see in SQL Server error log on JBSUB-DR,
-> It is pretty clear that when the packets drop starts, we are able to see below messages related to connection terminated,
2021-01-29 09:03:33.010 spid61s Always On Availability Groups connection with secondary database terminated for primary database ‘JBDB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
2021-01-29 09:03:33.010 spid70s Always On Availability Groups connection with secondary database terminated for primary database ‘JBREPL_SUB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
-> Also, we are able to see below messages related to connection established when the clumsy tool is terminated and the packet drop stops,
2021-01-29 09:03:39.270 spid61s Always On Availability Groups connection with secondary database established for primary database ‘JBREPL_SUB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
2021-01-29 09:03:39.270 spid81s Always On Availability Groups connection with secondary database established for primary database ‘JBDB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
-> Below is the view of Always On availability group dashboard,
-> Since the handshake between replicas gets dropped and establishes within the session timeout, the secondary replica is not getting disconnected.
-> Lets try executing “Network_Drop.CMD” for more than 10 seconds and check the SQL Server error log and Always On dashboard,
-> Below is what I see in SQL Server error log on JBSUB-PRIMARY,
-> Below message is definitely a cause of concern as this message in SQL Server error log signifies that there is a packet drop or temporary disconnects between replicas. An informational message with connection terminated and established within the session time out will not have much impact. But if the handshake is not done within the session time out, then the partner will be disconnected and will connect back automatically once the handshake completes. This message should be investigated and fixed no matter the handshake happens within session time out or not.
2021-01-29 09:03:33.010 spid61s Always On Availability Groups connection with secondary database terminated for primary database ‘JBDB‘ on the availability replica ‘JBSUB-DR’ with Replica ID: {6a9d0674-6dec-4810-b731-c9cde79cc62d}. This is an informational message only. No user action is required.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.