Environment

-> A request comes in to drop the existing Always-on availability group JBDB from above environment.

-> Alwayson Availability group should be deleted/dropped by connecting to Primary Replica JBAG1 using SQL Server Management Studio.

-> But, someone tried deleting the Availability group from SQL Server management studio connected using the LISTENER JBAPP and got below error,

TITLE: Microsoft SQL Server Management Studio
——————————
Drop failed for Availability Group ‘JBDB’. (Microsoft.SqlServer.Management.SDK.TaskForms)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17224.0+((SSMS_Rel_17_4).180212-1043)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+AvailabilityGroup&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
——————————
The network path was not found
——————————
BUTTONS:
OK
——————————

-> When “Always On High Availability” folder for SQL Server Instance JBAG1 which is the primary is refreshed from SQL Server management studio, “Availability group” JBDB is not available.

-> But you will see Availability group JBDB “Resolving” at the secondary replica “JBAG2”. At this point you delete the Availability group JBDB from your secondary replica JBAG2 from SSMS.

-> When connected to cluster administrator (cluadmin.msc), you will not see roles related to Availability group JBDB visible.

-> Lets try creating another Availability group with same name JBDB that contains listener JBAPP. It fails with below error,

TITLE: Microsoft SQL Server Management Studio
——————————
Create failed for Availability Group ‘JBDB’. (Microsoft.SqlServer.Management.HadrModel)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17224.0+((SSMS_Rel_17_4).180212-1043)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroup&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Failed to create availability group ‘JBDB’, because a Windows Server Failover Cluster (WSFC) group with the specified name already exists. The operation has been rolled back successfully. To retry creating an availability group, either remove or rename the existing WSFC group, or retry the operation specifying a different availability group name. (Microsoft SQL Server, Error: 41171)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=41171&LinkId=20476
——————————
BUTTONS:
OK
——————————

-> The error advises us that the ROLE JBDB is already exists in cluster administrator. But we have checked it and it is not present.

-> Execute below query on JBAG1 which was acting as the primary replica when Availability group JBDB was deleted from SSMS using Listener name,

select * from sys.availability_group_listeners
go
select * from sys.availability_groups

-> You see an orphaned listener entry under system table sys.availability_group_listeners. But no corresponding entries in sys.availability_groups.

-> Lets first try restarting the SQL Services on JBAG1 and then try running the below queries,

select * from sys.availability_group_listeners
go
select * from sys.availability_groups

-> The orphaned entry is no longer present in sys.availability_group_listeners after the SQL Server restart.

-> Lets try creating Availability group with same name JBDB that contains listener JBAPP and it works fine this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.