SQL Server 2012 Availability Group issue

Question

I need some assistance and some bandages for shooting myself in the foot. This is a DEV environment but I need to bring it up.

I am using SQL Server 2012 Enterprise on Windows 2008 R2. Somehow our AG listener group disappeared. I tried to recreate the AG listener and it was giving error regarding computer account not having permissions. The genius inside of me decided to delete AG group from Windows Cluster Admin and now I cannot recreate AG group (I should've done it via SQL Server instead of Windows).

I keep on getting the following error :

Creating availability group resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

ADDITIONAL INFORMATION:

Create failed for Availability Group 'SQLAG_D'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The availability group 'SQLAG_D' already exists. This error could be caused by a previous failed CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation. If the availability group name you specified is correct, try dropping the availability group and then retry CREATE AVAILABILITY GROUP operation.

Failed to create availability group 'SQLAG_D'. The operation encountered SQL Server error 41042 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41042)

Does anyone have any suggestions?

The AG Group is already dropped in Windows Cluster and it is not showing in SQL Availability Group. The sys.availability_groups system catalog view has the "deleted" AG in it. There is no mention of SQLAG_D in it.

Answer 1

You have to do following :

1. Drop the availability group then
2. using regedit, (first backup the registry of old availability group that starts with HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap) and then delete that key and
3. Finally create a new Availability Group with new name.

Note: Extra Caution .. as you are dealing with Windows Registry, Back it up first

Answer 2

Run

select * From sys.dm_hadr_name_id_map

If the AG still exists, drop the AG with the following command:

DROP AVAILABILITY GROUP AG_Name

Now you should be able to create the new AG.

Answer 3

The AD object for the listener is still there--deleting it from the cluster doesn't delete it from AD. If you have a Domain Admin reset the account, you should be able to recreate the availability group.