VMware: Restoring Custom Attributes after a VC 1.x to VC 2.0.x upgrade

****************************************
Disclaimer: Keep in mind, this is the process that worked for me, and I don’t recommend that anyone perform this on live data, but rather to test data.

I am not responsible for any changes to your database as a result of this process.

If you choose to use this method, please backup your database beforehand.

All VC 2.0.x database references are called VirtualCenter and all VC 1.x references are called VirtualCenterOLD.

Also, this properly works with dbo as the owner of all tables. If you have an alternate owner (as I do) you will have to modify the below scripts to match.

****************************************

After performing a VC 1.x to VC 2.0.x upgrade, custom attributes are lost.

This is because the VC 1.x and VC 2.0.x databases handle custom attributes a little different. VC 1.x has a custom attributes table called VPX_FIELDDEF, and VMs and HOSTs have separate tables (VPX_VM_FIELDS and VPX_HOST_FIELDS respectively.

In VC 2.0 there are only 2 tables, VPX_FIELD_DEF and VPX_FIELD_VAL.

The VC 1.0 table VPX_FIELDDEF is replaced by VPX_FIELD_DEF in VC 2.0.x.

The VC 1.0 tables, VPX_VM_FIELDS and VPX_HOST_FIELDS are replaced by VPX_FIELD_VAL in VC 2.0.

To import the old values into the new database, a couple steps will have to be taken.

Let’s say I have a SQL 2000 database with my VC 2.0.x database named VirtualCenter.

I need to get to the tables from my old VC 1.x installation. So I need to restore the old VC 1.x database where I can get to the data I need. So I restore the VC 1.x database to a database (on the same server), with the name VirtualCenterOLD.

Now run the following script from Query Analyzer, while attached to the new VirtualCenter database:

USE VirtualCenter

GO

INSERT INTO dbo.VPX_FIELD_DEF

SELECT FIELD_ID AS ID, NAME

FROM VirtualCenterOLD.dbo.VPX_FIELDDEF

GO

This will pull all values from the old VirtualCenterOLD table named VPX_FIELDDEF into the new VirtualCenter database table named VPX_FIELD_DEF, thus defining our custom attribute fields.

Now, We need to add the data from the Hosts and VMs.

Now run the following script from Query Analyzer, while attached to the new VirtualCenter database:

USE VirtualCenter

GO

INSERT INTO dbo.VPX_FIELD_VAL

SELECT VirtualCenterOLD.dbo.VPX_HOST_FIELDS.FIELD_ID AS FIELD_ID, dbo.VPXV_HOSTS.HOSTID AS ENTITY_ID, VirtualCenterOLD.dbo.VPX_HOST_FIELDS.FIELD_VALUE AS [VALUE]

FROM VirtualCenterOLD.dbo.VPX_HOST LEFT OUTER JOIN

dbo.VPXV_HOSTS ON VirtualCenterOLD.dbo.VPX_HOST.HOSTNAME = dbo.VPXV_HOSTS.NAME LEFT OUTER JOIN

VirtualCenterOLD.dbo.VPX_HOST_FIELDS ON VirtualCenterOLD.dbo.VPX_HOST.HOST_ID = VirtualCenterOLD.dbo.VPX_HOST_FIELDS.HOST_ID

WHERE (NOT (VirtualCenterOLD.dbo.VPX_HOST_FIELDS.FIELD_VALUE IS NULL)) AND (NOT (VirtualCenterOLD.dbo.VPX_HOST_FIELDS.FIELD_ID IS NULL))

GO

This will insert HOST attributes into the VPX_FIELD_VAL, where the FIELD_ID and VALUE come from the old data, and the ENTITY_ID comes from the new Entity value for the ESX Hosts in the new database, making sure that there are no nulls.

Now run the following script from Query Analyzer, while attached to the new VirtualCenter database:

USE VirtualCenter

GO

INSERT INTO dbo.VPX_FIELD_VAL

SELECT VirtualCenterOLD.dbo.VPX_VM_FIELDS.FIELD_ID AS FIELD_ID, dbo.VPXV_VMS.VMID AS ENTITY_ID,

VirtualCenterOLD.dbo.VPX_VM_FIELDS.FIELD_VALUE AS [VALUE]

FROM VirtualCenterOLD.dbo.VPX_VM LEFT OUTER JOIN

dbo.VPXV_VMS ON VirtualCenterOLD.dbo.VPX_VM.NAME = dbo.VPXV_VMS.NAME LEFT OUTER JOIN

VirtualCenterOLD.dbo.VPX_VM_FIELDS ON VirtualCenterOLD.dbo.VPX_VM.VM_ID = VirtualCenterOLD.dbo.VPX_VM_FIELDS.VM_ID

WHERE (NOT (VirtualCenterOLD.dbo.VPX_VM_FIELDS.FIELD_VALUE IS NULL)) AND (NOT (VirtualCenterOLD.dbo.VPX_VM_FIELDS.FIELD_ID IS NULL))

GO

This will insert VM attributes into the VPX_FIELD_VAL, where the FIELD_ID and VALUE come from the old data, and the ENTITY_ID comes from the new Entity value for the VM’s in the new database, making sure that there are no nulls.

This worked well for me, and I didn’t lose any custom attributes for my 300+ VM’s or my ESX Hosts.