none
Availablity group for Read Scale. Manual failover without data loss not working in SQL2019 RRS feed

  • Question

  • Hi
    I am following the instructions for Manual failover without data loss and it fails in SQL 2019, however, it works correctly in SQL 2017.
    I created a Read Scale Only Availablity Group with two instances on linux, and it fails. I created a Read Scale Only Availablity Group with one instances on linux and another in Windows and fails with the same error.

    I get the error when I execute: ALTER AVAILABILITY GROUP [ag1] SET (ROLE = SECONDARY);
    and it does make sense to me as it mention the AG resouce that does not exsits as there is no cluster in place.

    The message is:
    Msg 41104, Level 16, State 5, Line 1
    Failover of the availability group to the local replica failed because the availability group resource did not come online due to a previous error.

    I was intructed to refer to https://support.microsoft.com/en-ph/help/4492604/fix-manual-failover-between-forwarder-and-secondary-replica-fails-with, but the issue is different. In my case, the databases are in Synchronized state and there is no cluster (Pacemaker or WSFC) in place.

    Monday, December 2, 2019 5:55 PM

All replies

  • Hi Danval,

     According to the message,it seems the cause of this error is that AG is not online,you could run following statement on primary replica to observe the status of AG:

    __________________________________________________________________________________________________
    select
    AGNode.group_name
    ,AGNode.replica_server_name
    ,AGNode.node_name,ReplicaState.role,ReplicaState.role_desc
    ,ReplicaState.is_local
    ,DatabaseState.database_id
    ,db_name(DatabaseState.database_id) as database_name
    ,DatabaseState.group_database_id
    ,DatabaseState.is_commit_participant
    ,DatabaseState.is_primary_replica
    ,DatabaseState.synchronization_state_desc
    ,DatabaseState.synchronization_health_desc
    ,ClusterState.group_id
    ,ReplicaState.replica_id
    from sys.dm_hadr_availability_replica_cluster_nodes AGNode
    join sys.dm_hadr_availability_replica_cluster_states ClusterState
    on AGNode.replica_server_name = ClusterState.replica_server_name
    join sys.dm_hadr_availability_replica_states ReplicaState
    on ReplicaState.replica_id = ClusterState.replica_id
    join sys.dm_hadr_database_replica_states DatabaseState
    on ReplicaState.replica_id=DatabaseState.replica_id
    ____________________________________________________________________________________________________

    in addition,would you please tell me the linux vesrion.

    Best regards,
    Cris

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 3, 2019 10:19 AM