locked
SQL Availability Group Listener computer object remains in Active Directory after deletion. RRS feed

  • Question

  • SQL Version: 2016 SP2-CU13

    Hello.

    I am having difficulty with SQL Listeners computer objects remaining in Active Directory after the Listener (and Availability Group) have been deleted from SQL via TSQL.

    The command I am running:

    ALTER AVAILABILITY GROUP [@agname]
    REMOVE LISTENER N'@listener';
    DROP AVAILABILITY GROUP [@agname]

    The command is successful and the Availability Group and Listener no longer appear in any of the system tables in SQL but it does remain in AD in a disabled state.

    Is there a way to have it removed from Active Directory instead of remaining in a disabled state? I could have it remove with a PowerShell command (Remove-ADComputer -Identity $listener), but I don't want to manipulate AD this way. I would rather SQL took care of it if possible.

    It does remove the DNS entry.

    Thank you.

    Friday, June 19, 2020 3:20 PM

All replies

  • I would not mess around with Active Directory without understanding the policies setup by your AD administrator. Doing so could cause more issues in your entire network infrastructure. They may have setup policies to cleanup stale computer objects, including virtual computer objects.

    In my case, I use this PowerShell command to cleanup stale disabled computers in Active Directory, particularly Always On Availability Group listener names or SQL Server failover clustered instance network names. You can add the LastLogonTimestamp attribute to filter by the number of days that the virtual computer objects have been stale (30 days of inactivity would be safe but that should be based on your corporate policy).

    Again, I don't recommend doing this without knowledge of the existing Active Directory policies.

    ForEach ($computer in (Get-ADComputer -Property Name, Enabled -Filter {Enabled -eq $False})) {Remove-ADObject $computer -Recursive -Confirm:$false}



    Edwin M SarmientoMicrosoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Monday, June 22, 2020 8:43 AM
  • Hi DBA_Bandy,

    A computer object is created automatically whenever you create an Availability Group Listener. Even if you drop the listener, the computer object remains there in Active Directory. You may contact your domain admin to clean up this computer objects. I test in my environment, SQL Availability Group Listener computer object can’t be delete automatically even  giving the ‘Delete computer objects’ permission. We need to delete it manually using PowerShell or Active Directory UI.

    Best regards,
    Cathy

    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, June 22, 2020 9:29 AM
  • following link may help you

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b6381a1c-2239-4dde-b837-ba2700c391da/after-deleting-availability-group-listener-remains-in-the-system-table?forum=sqldisasterrecovery


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Monday, June 22, 2020 9:31 AM