I'm evaluating disaster recovery using read-scale availability group on SQL Server 2019 and Windows 2019. The cluster type is NONE.
The cluster works fine in general even when restarting replicas.
The issue arises when simulating a DR with the following action :
- Stop the SQL server service on the primary node.
- Force failover on secondary with
ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS
- Start the SQL server service on the primary node.
When started, the primary node joins as primary and exhibits a split brain behaviour, which seems to be fine following this document.
When the previous primary replica recovers, it will also assume the primary role ...
But the primary node can no more connect to the other node.
This secondary replica is not connected to the primary replica. The connected state is
The same applies from the other node's perspective.
In the SQL Server logs I've got a message like
A connection timeout has occurred while attempting to establish a connection to availability replica 'xxxx(former secondary)' with id [yyy]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
Of course, there is no firewall or intermediate issue since I can connect again by drop/create the AG.
Also, I used wireshark to get some insights into the traffic:
The flow looks okay, but the retransmits show some kind of "misunderstanding" between the nodes.
This seems like a software issue on SQL Server.
SQL Server version: Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )
The cluster config
CREATE AVAILABILITY GROUP [ag1] WITH (CLUSTER_TYPE = NONE) FOR REPLICA ON N'replica-node1' WITH ( ENDPOINT_URL = N'tcp://10.61.137.7:5022', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC, SESSION_TIMEOUT = 30, SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL ) ), N'replica-node2' WITH ( ENDPOINT_URL = N'tcp://10.61.137.4:5022', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC, SESSION_TIMEOUT = 30, SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL ) );