none
What if you remove cluster role on the last node of the windows 2016 cluster with SQL always on group exists RRS feed

  • Question

  • this question might be crazy in production, but it happened in my test, I create a cluster and created SQL always on group on it. later I uninstalled the cluster service, 

    it happened: the AlwaysOn group databases show as restoring. the AG is not responsible, if try to drop the AG, get

    Msg 41081, Level 16, State 0, Line 6
    Failed to destroy the Windows Server Failover Clustering group corresponding to availability group 'AG1'.  The operation encountered SQL Server error 41000 and has been terminated.  Refer to the SQL Server error log for details about this SQL Server error and corrective actions.
    Msg 41108, Level 16, State 0, Line 6
    An error occurred while removing availability group 'AG1'. The DROP AVAILABILITY GROUP command removed the availability group configuration from the local metadata. However, the attempt to remove this configuration from the Windows Server Failover Clustering (WSFC) cluster failed because the Always On Availability Groups manager is not online (SQL Server error: 41081). To remove the availability group configuration from the WSFC cluster, re-enter the command.

    then I added the cluster role back,  sure the cluster service is not started unless the cluster with original name exists. 

    then I clear the box so as not to enable alwayson high availability group, now the sql server is back to a standalone server. 

    the only mess is to clear the AD entries for the original cluster name and the AG group listener name, I believe nothing else? please advise.

    Also, just wondering the logic, why it is allowed to remove cluster role on the primary node of a cluster where AG resources exists.

    Friday, October 18, 2019 6:11 PM

Answers

  • Hi George Zhou,

     

    >>the only mess is to clear the AD entries for the original cluster name and the AG group listener name, I believe nothing else? please advise.

     

    Yes, you are right. The message raised is not a dangerous error message. It clearly mentions that availability group information has been removed from SQL Server, but it might still exist in the failover cluster.

     

    Check availability group in SSMS and it should be gone from “Always On High Availability” > “Availability Group”. 

     

    The database would be in restoring state and you need to bring them online using below

     

    ALTER DATABASE <DBName> SET ONLINE

     

    >>Also, just wondering the logic, why it is allowed to remove cluster role on the primary node of a cluster where AG resources exists.

     

    It is by design. Ag is just a cluster resource and it is based on windows failover cluster. You remove the failover clustering service means ag breaks.

     

    Best regards,

    Dedmon Dai


    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

    Monday, October 21, 2019 3:18 AM

All replies

  • Hi George Zhou,

     

    >>the only mess is to clear the AD entries for the original cluster name and the AG group listener name, I believe nothing else? please advise.

     

    Yes, you are right. The message raised is not a dangerous error message. It clearly mentions that availability group information has been removed from SQL Server, but it might still exist in the failover cluster.

     

    Check availability group in SSMS and it should be gone from “Always On High Availability” > “Availability Group”. 

     

    The database would be in restoring state and you need to bring them online using below

     

    ALTER DATABASE <DBName> SET ONLINE

     

    >>Also, just wondering the logic, why it is allowed to remove cluster role on the primary node of a cluster where AG resources exists.

     

    It is by design. Ag is just a cluster resource and it is based on windows failover cluster. You remove the failover clustering service means ag breaks.

     

    Best regards,

    Dedmon Dai


    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

    Monday, October 21, 2019 3:18 AM
  • thanks Dedmon for the reply. btw, once I removed the high availability  check box on the sql service, the database returned to normal as it is in standalone sql server without the need to set it online.
    Tuesday, October 22, 2019 4:48 PM