How to manually remove a Host / Cluster from SCVMM 2012 SP1

Before you read further here, As far as I’ve worked with SCVMM 2012 SP1 RU4 and SCVMM 2012 R2 now, the issue seems to be fixed in those releases.

The story of a bad day

Sometimes things go wrong, that’s it.Recently a customer had a storage outage with all Hyper-V Cluster CSVs going completely offline for some minutes. Even worser, some of the underlying clustered disks got a new ID within the parent OS when iSCSI Storage (NetApp) came back online after a cluster give-back action. After spending some time to fix that, VMM run into trouble. Multiple Cluster refresh tasks did not resolve the hell of wrong information in the VMM database. I’m talking about duplicate CSVs, invalid references to VM configuration files… the list was long, believe me.

We then decided to remove the cluster from VMM, which does by the way nothing harmful to the cluster, the nodes or the hosted VMs of course. The target was to re-add the host and have a clean and refreshed inventory.

As you can imaging, this went wrong. If something goes wrong, most of times other things go wrong too. The result was the cluster and one of the nodes stucked in the “deletion pending” state with the following error: “Error (20400) . Error (2605). SQL Error Code 547”

Now this doesn’t only sound bad.. it is really bad. This means in fact that you’re trying to do something on an object within VMM which has invalid or missing references to other linked tables. Now you say.. well let’s just remove the cluster using Powershell

Get-SCVMhostCluster "clustername" | Remove-SCVMHostCluster -Force

..which results in the same error mentioned above..

Ok, next rty..

Get-SCVMHost "yourpendinghostname" | Remove-SCVMHost -Force

of course this will not work, as VMM still knows that the host belongs to a cluster and can’t be deleted as a single object without removing the cluster. The result in addition is a crash of VMM Service.

So we had a classical “Egg and Chicken” issue.

Ok next try. Let’s evict the cluster node using Powershell or Failover Cluster Manager Console. So done, the plan was to initiate a Host refresh task in VMM for the one in trouble. Booom.. While a Hyper-V Host is in “deletion pending” state, you can’t perform a refresh, not even with Powershell.

After some coffee I rembered myself having the same issues already with SCVMM SP1 Beta. Could this still not be fixed? Yes.. unfortunately that’s the case actually. So i pulled out a SQL Script, which’s original version is from Hazem Elshabini to forcefully remove the host from the database using hardcore T-SQL.

The way out

I can’t say this LOUD and CLEAR enough. Before you are going to touch the VMM Database manually, shut down the VMM Service and take a full backup of your database!!!

So after performing the backup I instructed the customer to execute the T-SQL script. As you can think.. this didn’t work at all. Puke! What the heck was going on there… Well the solution was pretty simple. The original script mentioned above did include everything, but the proper deletion of iSCSI Hostbus Adapter and their Linkage to the Target Mappings as well as the deletion of virtual switch extension mappings between a logical switch and it’s referenced instance on thie particular host. After digging into the database schema a bit I only had to add a few lines and I was able to remove the host with all it’s references from the VMM databases.

Restarting the VMM Service and having a look into the inventory showed my success. The pending node and the cluster where gone.. finally… did I mention FINALLY ! 🙂

The only thing to do now was to re-add the Cluster and it’s nodes to VMM, which went trough like a charm and voilà.. VMM was in sync again with the real world..

Before you have a look at the script at the bottom of this post, I’d like to remind you kindly to use this method as the very very last option, if everything else mentioned above did not work.

You’ll use the script at your own risk. I won’t give support for it nor will I be responsible for any kind of damage you produce in your VMM database.

-- Define the name of the VMM Database --
USE VirtualManagerDB;
-----------------------------------------

DECLARE @DeleteHostId GUID;
DECLARE @DeleteHostName NVARCHAR;
DECLARE @DatabaseName NVARCHAR;

-- ****** Define the Variables **********************************************************************************************
SET @DeleteHostId = 'YourVMHostID'
SET @DeleteHostName = 'YourVMHostName'
-- **************************************************************************************************************************

PRINT N'Deleting host with GUID ' + RTRIM(CAST(@DeleteHostID AS nvarchar(50)))

PRINT N'Getting host cluster GUID'

DECLARE @HostClusterID GUID;
SET @HostClusterID =
(
SELECT HostClusterID FROM [dbo].[tbl_ADHC_Host]
WHERE HostID = @DeleteHostId
)

IF (@HostClusterID IS NOT NULL)
PRINT N'Retreived host cluster GUID ' + RTRIM(CAST(@HostClusterID AS nvarchar(50)))
ELSE
PRINT N'This host does not belong to a cluster'

PRINT N'Deleteing physical objects'

DELETE FROM [dbo].[tbl_WLC_PhysicalObject]
WHERE HostId = @DeleteHostId

PRINT N'Deleteing virtual objects'

DELETE FROM [dbo].[tbl_WLC_VObject]
WHERE HostId = @DeleteHostId

PRINT N'Prepairing to delete host network adapters'

DECLARE @HostNetworkAdapterCursor CURSOR;
DECLARE @HostNetworkAdapterID GUID;
SET @HostNetworkAdapterCursor = CURSOR FOR
(SELECT NetworkAdapterID FROM [dbo].[tbl_ADHC_HostNetworkAdapter])

OPEN @HostNetworkAdapterCursor

FETCH NEXT FROM @HostNetworkAdapterCursor INTO @HostNetworkAdapterID

WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'Prepairing to delete host network adapter with GUID ' + RTRIM(CAST(@HostNetworkAdapterID AS nvarchar(50)))

PRINT N'Deleting logical network mapping for host network adapter with GUID ' + RTRIM(CAST(@HostNetworkAdapterID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_NetMan_HostNetworkAdapterToLogicalNetwork]
WHERE HostNetworkAdapterID = @HostNetworkAdapterID

PRINT N'Deleting IP subnet VLAN mapping for host network adapter with GUID ' + RTRIM(CAST(@HostNetworkAdapterID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_NetMan_HostNetworkAdapterToIPSubnetVLan]
WHERE HostNetworkAdapterID = @HostNetworkAdapterID

FETCH NEXT FROM @HostNetworkAdapterCursor INTO @HostNetworkAdapterID
END

CLOSE @HostNetworkAdapterCursor
DEALLOCATE @HostNetworkAdapterCursor

PRINT N'Completing host network adapters deletion'

DELETE FROM [dbo].[tbl_ADHC_HostNetworkAdapter]
WHERE HostID = @DeleteHostId

PRINT N'Deleting Virtual Switch Extensions'
DELETE FROM [VirtualManagerDB].[dbo].[tbl_NetMan_InstalledVirtualSwitchExtension]
WHERE HostID = @DeleteHostId

PRINT N'Deleting virtual networks'

DELETE FROM [dbo].[tbl_ADHC_VirtualNetwork]
WHERE HostID = @DeleteHostId

PRINT N'Deleting host volumes'

DELETE FROM [dbo].[tbl_ADHC_HostVolume]
WHERE HostID = @DeleteHostId

PRINT N'Deleting host disks'

DELETE FROM [dbo].[tbl_ADHC_HostDisk]
WHERE HostID = @DeleteHostId

PRINT N'Prepairing to delete host bus adapters'

DECLARE @HostBusAdapterCursor CURSOR;
DECLARE @HostBusAdapterID GUID;
SET @HostBusAdapterCursor = CURSOR FOR
(SELECT HbaID FROM [dbo].[tbl_ADHC_HostBusAdapter])

OPEN @HostBusAdapterCursor

FETCH NEXT FROM @HostBusAdapterCursor INTO @HostBusAdapterID

WHILE (@@FETCH_STATUS = 0)
BEGIN

PRINT N'Prepairing to delete host bus adapter with GUID ' + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

PRINT N'Deleting fiber port mapping for host bus adapter with GUID ' + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DECLARE @FiberPortID GUID;
SET @FiberPortID =
(
SELECT PortID FROM [dbo].[tbl_ADHC_FCHbaToFibrePortMapping]
WHERE FCHbaID = @HostBusAdapterID
)

DELETE FROM [dbo].[tbl_ADHC_FCHbaToFibrePortMapping]
WHERE FCHbaID = @HostBusAdapterID

PRINT N'Deleting fiber port with GUID ' + RTRIM(CAST(@FiberPortID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_FibrePort]
WHERE PortID = @FiberPortID

PRINT N'Deleting fiber channel mapping for host bus adapter with GUID ' + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_HostFibreChannelHba]
WHERE FCHbaID = @HostBusAdapterID

PRINT N'Deleting any iSCSI entries for host bus adapter with GUID ' + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DECLARE @iSCSIHBAID GUID;
SET @iSCSIHBAID =
(
SELECT ISCSIHbaID FROM [VirtualManagerDB].[dbo].[tbl_ADHC_HostInternetSCSIHba] WHERE ISCSINodeName LIKE '%' + @DeleteHostName + '%'
)
DELETE FROM [VirtualManagerDB].[dbo].[tbl_ADHC_ISCSIHbaToPortalMapping]
WHERE ISCSIHBAID = @iSCSIHBAID

DECLARE @iSCSITargets TABLE
(
TargetID GUID
)
INSERT INTO @iSCSITargets (TargetID)
SELECT TargetID FROM [dbo].[tbl_ADHC_ISCSIHbaToTargetMapping]
WHERE ISCSIHbaID = @HostBusAdapterID

PRINT N'Deleting iSCSI host bus adapter to target mapping for mapping for host bus adapter with GUID ' + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_ISCSIHbaToTargetMapping]
WHERE ISCSIHbaID = @HostBusAdapterID

PRINT N'Deleting iSCSI host bus adapter with GUID ' + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_HostInternetSCSIHba]
WHERE ISCSIHbaID = @HostBusAdapterID

PRINT N'Deleting iSCSI targets for host bus adapter with GUID ' + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DECLARE @iSCSITargetIDCursor CURSOR;
DECLARE @iSCSITargetID GUID;
SET @iSCSITargetIDCursor = CURSOR FOR
(SELECT TargetID FROM @iSCSITargets)

OPEN @iSCSITargetIDCursor

FETCH NEXT FROM @iSCSITargetIDCursor INTO @iSCSITargetID

WHILE (@@FETCH_STATUS = 0)
BEGIN

PRINT N'Deleting iSCSI targets with GUID ' + RTRIM(CAST(@iSCSITargetID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_ISCSITarget]
WHERE TargetID = @iSCSITargetID

FETCH NEXT FROM @iSCSITargetIDCursor INTO @iSCSITargetID
END

CLOSE @iSCSITargetIDCursor
DEALLOCATE @iSCSITargetIDCursor

FETCH NEXT FROM @HostBusAdapterCursor INTO @HostBusAdapterID
END

CLOSE @HostBusAdapterCursor
DEALLOCATE @HostBusAdapterCursor

PRINT N'Completing host bus adapters deletion'

DELETE FROM [dbo].[tbl_ADHC_HostBusAdapter]
WHERE HostID = @DeleteHostId

PRINT N'Prepairing to delete agent servers'

DECLARE @AgentServerID  GUID;
SET @AgentServerID =
(
SELECT AgentServerID FROM [dbo].[tbl_ADHC_AgentServerRelation]
WHERE HostLibraryServerID = @DeleteHostID
)

PRINT N'Deleting agent server relations'

DELETE FROM [dbo].[tbl_ADHC_AgentServerRelation]
WHERE HostLibraryServerID = @DeleteHostID

PRINT N'Deleting health monitor data for agent server with GUID ' + RTRIM(CAST(@AgentServerID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_HealthMonitor]
WHERE AgentServerID = @AgentServerID

PRINT N'Deleting agent server with GUID ' + RTRIM(CAST(@AgentServerID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_AgentServer]
WHERE AgentServerID = @AgentServerID

PRINT N'Deleting host GPUs'

DELETE FROM [dbo].[tbl_ADHC_HostGPU]
WHERE HostID = @DeleteHostId

PRINT N'Deleting host'

DELETE FROM [dbo].[tbl_ADHC_Host]
WHERE HostID = @DeleteHostId

IF (@HostClusterID IS NOT NULL)
BEGIN

PRINT N'Checking to see if any other hosts are joined to the same cluster'

DECLARE @HostCount INT;
SET @HostCount =
(
SELECT COUNT(*) FROM [dbo].[tbl_ADHC_Host]
WHERE HostClusterID = @HostClusterID
)

PRINT N'There are ' + RTRIM(CAST(@HostCount AS nvarchar(50))) + N' currently joined to the same cluster'

IF (@HostCount = 0)
BEGIN

PRINT N'Deleting cluster disks'

DELETE FROM [dbo].[tbl_ADHC_ClusterDisk]
WHERE ClusterID = @HostClusterID

PRINT N'Deleting virtual Switch Extensions'
DELETE FROM [VirtualManagerDB].[dbo].[tbl_NetMan_InstalledVirtualSwitchExtension]
WHERE HostID = @DeleteHostId

PRINT N'Deleting cluster'

DELETE FROM [dbo].[tbl_ADHC_HostCluster]
WHERE ClusterID = @HostClusterID
END
ELSE
PRINT N'This host is not the last host in the cluster, the cluster will be deleted upon the deletion of the last host.'
END
ELSE
PRINT N'This host does not belong to a cluster, no clusters will be deleted'

GO