How do you recover / clean when we delete a datastore cluster from vCenter but it still shows up under Reservations of vRA 7.x
Sharing the approach taken to get to the solution
Note: Below steps have to be performed on IAAS ( MS SQL Database )
Step 1
Below query is to see if there any VM associated with the Storage path
SELECT vm.VirtualMachineName, vm.VirtualMachineID, vm.StoragePath, hr.HostReservationName, h.HostName
FROM VirtualMachine vm JOIN HostReservationToStorage hrts ON vm.HostStorageReservationID = hrts.HostReservationToStorageID
JOIN HostToStorage hts ON hrts.HostToStorageID = hts.HostToStorageID
JOIN HostReservation hr ON vm.HostReservationID = hr.HostReservationID
JOIN Host h ON vm.HostID = h.HostID
Where hts.StoragePath = '<<DATASTORE{CLUSTER}>>'
<<DATASTORE{CLUSTER}>> is the value of the Storage Path which was deleted or decommissioned on vCenter
This query returned no results which means that there are no virtual machines on this storage path
Step 2
Now since we know that there are no virtual machines using this storage path let's move on to the next phase where we find out on how many tables is this storage path being referenced
Using stored procedure "SearchAllTables" we have to find out the references. How we create this stored procedure has been documented here
exec SearchAllTables '<<DATASTORE{CLUSTER}>>';
There were 4 tables this storage path was part of
[dbo].[HostToStorage].[StoragePath]
[dbo].[Storage].[StorageName]
[dbo].[UserLog].[Message]
[dbo].[VirtualMachineHistoryProperties].[PropertyValue]
The first two tables are important ones. The last two Userlog and VirtualMachineHistoryProperties are not that important as it would relate more to logging and storing properties.
So let's inspect HostToStorage and Storage tables in depth.
Step 3
Executing below query on dbo.HostToStorage we would capture few ids of this StoragePath.
select * from dbo.HostToStorage where StoragePath = '<<DATASTORE{CLUSTER}>>';
HostToStorageID : 147EC6B2-BC8D-4B79-A94D-A1D30833311B
HostID : 6896D5CC-ABFD-4719-A254-FFEA79453239
StorageID : FEF71405-D569-4AAF-BEBF-02ACC37BFB73
Step 4
Executing below query on dbo.Storage we would capture few ids of this StoragePath.
select * from dbo.Storage where StoragePath = '<<DATASTORE{CLUSTER}>>';
StorageID : FEF71405-D569-4AAF-BEBF-02ACC37BFB73 , StorageUniqueID : f264d83b-d7e6-4f1c-a423-ca9173ac80d9/group-p412357 */
Step 5
If you try and delete these from SQL query window it would not let you do it as there are references to this on another table
delete from dbo.HostToStorage where StoragePath = '<<DATASTORE{CLUSTER}>>';
delete from dbo.Storage where StorageName = '<<DATASTORE{CLUSTER}>>';
Msg 547, Level 16, State 0, Line 39
The DELETE statement conflicted with the REFERENCE constraint "HostToStorage_HostReservationToStorage". The conflict occurred in database "vra", table "dbo.HostReservationToStorage", column 'HostToStorageID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 41
The DELETE statement conflicted with the REFERENCE constraint "Storage_HostToStorage". The conflict occurred in database "vra", table "dbo.HostToStorage", column 'StorageID'.
The statement has been terminated.
Step 6
If we read the above exception dbo.Storage was referring to dbo.HostToStorage and dbo.HostToStorage to dbo.HostReservationToStorage
In order to clean this up, we need to find out from dbo.HostReservationToStorage on what other reservations are using this Storage Path
To identify this we have to pick up the HostToStorageID from Step#3 and then execute below query
select * from HostReservationToStorage where HostToStorageID = '147EC6B2-BC8D-4B79-A94D-A1D30833311B';
This returns three values in my case
HostReservationToStorageID : D64058DB-2CE9-4F87-AA2A-3345639B69A4 HostReservationID : F7655D38-5850-4C22-B2E2-A814C2870135
HostReservationToStorageID : 5B444E45-C798-4757-A7ED-9CC5BEEBCA42 HostReservationID : 0AE886F9-44CE-4EE2-96A2-06FE82AB456E
HostReservationToStorageID : A4925663-A7CF-4616-8D48-F581756C7009 HostReservationID : 1723EEDE-C95E-47CF-B993-68315AD7298D
Step 7
Now that I have HostReservationID it's very easy for me to find out what's the HostReservationName
select * from HostReservation where HostReservationID in ( 'F7655D38-5850-4C22-B2E2-A814C2870135','0AE886F9-44CE-4EE2-96A2-06FE82AB456E','1723EEDE-C95E-47CF-B993-68315AD7298D')
The above query returns to me HostReservationNames as an output along with other outputs.
Step 8
Using these reservation names captured, we would go ahead and then uncheck this storage path
Step 9
Peforming data collection after we uncheck the storage path as discussed under Step#8 would remove this patch from all reservations
Note: Please take a backup before you perform any changes on the database either SQL or Postgres
!!! Hope this helps !!!
Commenti