Tuesday, August 20, 2013

SQL Availability Group deletion issues – SQL 2012

 

Today I learned a valuable lesson about deleting SQL Availability Groups in SQL 2012 running on Windows 2008 R2 (2 node cluster). Luckily I was working in DEV environment. Basically we started having issues with Availability Group (SQLAG_D). The AG listener was not working so I decided (don’t do it this way) to delete the AG from Windows Cluster. This went fine and I was about to recreate SQLAG_D. Everything looked perfect and then at the end, I got the error

TITLE: Microsoft SQL Server Management Studio
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)

I tried the following

  • Looked for SQLAG_D on both nodes and didn’t find anything
  • Looked in Failover Cluster Admin and still no signs
  • Looked in sys.availability_group and still no SQLAG_D

I tried to create it again and it failed with same error. At this point, I posted my question on dba.stackexchange.com SQL AG Group Issue and got good feedback. An answer from Kin resolve my issues

 

You have to do following :

  1. Drop the availability group then
  2. using regedit, (first backup the registry of old availability group that starts withHKEY_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

Thanks to All SQL DBA community. You guys are amazing.

Posted by at

4 comments:

  1. Rob NMarch 7, 2016 at 3:53 AM

    This problem also happens on Windows Server 2012R2. If you uninstall an instance the process doesn't remove this registry key so if you ever want to use the same AG name on that server again you need to hack the registry (as per your article).

    Of course this can all be circumvented by deleting the AG object before the uninstall, but this should really be part of the SQL uninstall process.

    ReplyDelete
    Replies
    1. SQLRizMarch 7, 2016 at 7:33 AM

      I haven't tested it on Windows 2012R2 but you are right that it should be part of the checklist to delete AG Group before uninstall.

      Delete
  2. Rob NMarch 8, 2016 at 11:35 PM

    I have raised a Connect item for this:
    https://connect.microsoft.com/SQLServer/feedback/details/2451275

    ReplyDelete
  3. AnonymousApril 28, 2016 at 2:37 PM

    OMG this article really saved me! THANK YOU! Just to clarify, you have to perform this registry edit on each server in your SQL failover cluster!

    ReplyDelete

Subscribe to: Post Comments (Atom)