0

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 :

  1. Stop the SQL server service on the primary node.
  2. Force failover on secondary with ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS
  3. 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 DISCONNECTED. 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:

enter image description here

The flow looks okay, but the retransmits show some kind of "misunderstanding" between the nodes.

This seems like a software issue on SQL Server.

Appendix:

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  )
            );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.