Cloud Virtualization VMware

What to expect during vCloud Director 9.1 database migration from MSSQL to Postgres

This quick post is about my experience during vcloud DB migration from MSSQL to Postgres.

DB migration process should be straight forward if your cloud director cell is fairly new, never experienced database issues or never in your life you received an error in vCloud director GUI trying to delete an object (chances are low for this last one over the time).

I deployed a VM with Ubuntu and installed Postgres then configured users and roles to finally create the database. You will find plenty of information available on how to install Ubuntu and Postgres online or you can play with this vAPP

On a normal vCD cell without issues below procedure will migrate your DB

  1. Backup your MSSQL DB
  2. Stop vmware-vcd servicesvCD 9.1 has a known issue stopping services using cell-management-tool as ducumented on known issues in release notes
  3. Check vCloud Director cell can reach port 5432 on target DB server
  4. cell-management-tool dbmigrate -dbhost 1.2.3.4 -dbport 5432 -dbuser vcd -dbname vcd -dbpassword asdfghj -verbose
  5. cell-management-tool reconfigure-database –database-host 1.2.3.4 –database-port 5432 –database-name vcd –database-user vcd –database-password asdfghj  –database-type postgres
  6. Start vmware-vcd services

When DB migration was attempted I received error “Unable to migrate table [xxxx] to target database.” on 6 tables as shown on verbose logs below:

Configuring the target database...
......................................../
Successfully configured the target database.
Target database is using PostgreSQL version 9.5.12.
Blacklisted licensing_vm_data.
Successfully connected to source DB jdbc:jtds:sqlserver://5.6.7.8:1433/vcloud;socketTimeout=90 and target DB jdbc:postgresql://1.2.3.4:5432/vcloud.

Beginning database migration.
Starting table clearing process.
Successfully cleared table [vapp_property].
Successfully cleared table [vm_gosc_status].
Successfully cleared table [vapp_vm_sclass_metrics].
Successfully cleared table [vapp_vm_disk_storage_class].
Successfully cleared table [vapp_product_info].
Successfully cleared table [vapp_eula].
Successfully cleared table [guest_personalization_info].
Successfully cleared table [address_translation].
Successfully cleared table [gateway_assigned_ip].
Successfully cleared table [vm_snapshot_disk].
Successfully cleared table [vapp_vm].
Successfully cleared table [vapp_logical_resource].
Successfully cleared table [vdc_storage_class_settings].
Successfully cleared table [vdc_lr_encumbrance].
Successfully cleared table [shared_org_vdc_network].
Successfully cleared table [network_interface].
Successfully cleared table [logical_network_ip_scope].
Successfully cleared table [gateway_interface].
Successfully cleared table [params_soi].
Successfully cleared table [vm_snapshot].
Successfully cleared table [deployed_vm_resource].
Successfully cleared table [vm_container].
Successfully cleared table [vdc_logical_resource].
Successfully cleared table [org_vdc_network_services].
Successfully cleared table [logical_disk].
Successfully cleared table [real_network_backing].
Successfully cleared table [network_backing].
Successfully cleared table [logical_network].
Successfully cleared table [ccr_drs_vm_group_vm_pending].
Successfully cleared table [ccr_drs_vm_group_vm_inv].
Successfully cleared table [drs_rule_vm_inv].
Successfully cleared table [ccr_drs_host_group_host_inv].
Successfully cleared table [org_member_preference].
Successfully cleared table [acl].
Successfully cleared table [api_acl_rule].
Successfully cleared table [ui_portal_link].
Successfully cleared table [ui_plugin_access].
Successfully cleared table [org_prov_vdc_template_pvdcs].
Successfully cleared table [org_prov_vdc_template_bindings].
Successfully cleared table [org_prov_vdc_template_access].
Successfully cleared table [selector_extension_phase].
Successfully cleared table [licensing_ms_data].
Successfully cleared table [params_so].
Successfully cleared table [params_metainfo_so].
Successfully cleared table [entity_so].
Successfully cleared table [vm_resource].
Successfully cleared table [vm_disk_storage_class].
Successfully cleared table [vm_disk].
Successfully cleared table [shadow_vm].
Successfully cleared table [deployed_vm].
Successfully cleared table [vrp_rp].
Successfully cleared table [computevm].
Successfully cleared table [cell_application_table_access].
Successfully cleared table [vm_import_activity].
Successfully cleared table [vm_dstore_metrics_inv].
Successfully cleared table [prov_vdc_logical_resource].
Successfully cleared table [org_prov_vdc].
Successfully cleared table [vds_context].
Successfully cleared table [real_network].
Successfully cleared table [ccr_drs_vm_host_rule_inv].
Successfully cleared table [ccr_drs_vm_group_inv].
Successfully cleared table [ccr_drs_rule_inv].
Successfully cleared table [ccr_drs_host_group_inv].
Successfully cleared table [session_role].
Successfully cleared table [role_right].
Successfully cleared table [org_member].
Successfully cleared table [catalog_item].
Successfully cleared table [callout_task_binding].
Successfully cleared table [callout_collaboration].
Successfully cleared table [api_resource].
Successfully cleared table [api_action].
Successfully cleared table [api_file_descriptor].
Successfully cleared table [api_def_supported_versions].
Successfully cleared table [ui_portal_branding].
Successfully cleared table [ui_plugin].
Successfully cleared table [resource_file].
Successfully cleared table [placement_subject_item].
Successfully cleared table [org_right].
Successfully cleared table [org_prov_vdc_template_sprofile].
Successfully cleared table [org_prov_vdc_template].
Successfully cleared table [org_prov_vdc_specification_nw].
Successfully cleared table [site_status].
Successfully cleared table [remote_orgs].
Successfully cleared table [org_association].
Successfully cleared table [selector_extension].
Successfully cleared table [object_extension_selector].
Successfully cleared table [networked_vm_mac].
Successfully cleared table [metadata_text_value].
Successfully cleared table [metadata_string_value].
Successfully cleared table [metadata_long_value].
Successfully cleared table [metadata_date_value].
Successfully cleared table [metadata_boolean_value].
Successfully cleared table [managed_server].
Successfully cleared table [licensing_metric].
Successfully cleared table [krb_domain_realm_mapping].
Successfully cleared table [last_jobs].
Successfully cleared table [job_detail].
Successfully cleared table [iam_id_provider_settings].
Successfully cleared table [oauth_key_configuration].
Successfully cleared table [hardware_version_guest_os].
Successfully cleared table [org_member_source].
Successfully cleared table [grp].
Successfully cleared table [gateway_assigned_mac].
Successfully cleared table [from_cloud_tunnel].
Successfully cleared table [service_offering].
Successfully cleared table [service].
Successfully cleared table [entity_soi].
Successfully cleared table [vm].
Successfully cleared table [disk].
Successfully cleared table [deployed_ip_address].
Successfully cleared table [deleted_object_error_detail].
Successfully cleared table [vrp].
Successfully cleared table [computehub_set_computehub].
Successfully cleared table [cluster_hardware_version].
Successfully cleared table [cell_application_instance].
Successfully cleared table [vshield_manager].
Successfully cleared table [vm_inv].
Successfully cleared table [vcenter_runtime].
Successfully cleared table [vc_computehub].
Successfully cleared table [storage_pod_inv].
Successfully cleared table [resource_pool_inv].
Successfully cleared table [prov_vdc].
Successfully cleared table [property_map].
Successfully cleared table [network_pool].
Successfully cleared table [network_inv].
Successfully cleared table [managed_server_inv].
Successfully cleared table [folder_inv].
Successfully cleared table [dv_switch_inv].
Successfully cleared table [dv_switch].
Successfully cleared table [dv_portgroup_inv].
Successfully cleared table [datastore_inv].
Successfully cleared table [datastore].
Successfully cleared table [datacenter_inv].
Successfully cleared table [custom_field_manager_inv].
Successfully cleared table [compute_resource_inv].
Successfully cleared table [cluster_compute_resource_inv].
Successfully cleared table [tenant_migration_history].
Successfully cleared table [busy_object].
Successfully cleared table [task_activity_queue].
Successfully cleared table [smtp_server].
Successfully cleared table [role].
Successfully cleared table [org_email].
Successfully cleared table [org_association_keys].
Successfully cleared table [catalog].
Successfully cleared table [callout_endpoint].
Successfully cleared table [branding].
Successfully cleared table [api_resource_type].
Successfully cleared table [api_link].
Successfully cleared table [api_external_service_config].
Successfully cleared table [api_definition].
Successfully cleared table [ip_range].
Successfully cleared table [allocated_ip_address].
Successfully cleared table [activity_results].
Successfully cleared table [activity_parameters].
Successfully cleared table [vc_task_mapping].
Successfully cleared table [vcenter_supported_builds].
Successfully cleared table [vc_activity_queue].
Successfully cleared table [vapp_vm_logical_disk].
Successfully cleared table [valc_storage_class_join].
Successfully cleared table [usr].
Successfully cleared table [ui_portal_theme].
Successfully cleared table [ui_portal_resource].
Successfully cleared table [ui_plugin_resource].
Successfully cleared table [tunneling_application].
Successfully cleared table [truststore].
Successfully cleared table [transfer_server].
Successfully cleared table [to_cloud_tunnel].
Successfully cleared table [task_inv].
Successfully cleared table [storage_profile_inv].
Successfully cleared table [storage_fabric_handle].
Successfully cleared table [sprofile_compliance].
Successfully cleared table [shared_resource_assignment].
Successfully cleared table [user_session].
Successfully cleared table [scheduled_activity_jobs].
Successfully cleared table [saml_message_storage].
Successfully cleared table [transfer_session].
Successfully cleared table [resource_assignment].
Successfully cleared table [reservation].
Successfully cleared table [placement_subject].
Successfully cleared table [rights].
Successfully cleared table [org_prov_vdc_specification].
Successfully cleared table [org_delegation_user].
Successfully cleared table [sites].
Successfully cleared table [object_extension].
Successfully cleared table [object_config].
Successfully cleared table [object_condition].
Successfully cleared table [network_resources_config].
Successfully cleared table [network_pool_merge_data].
Successfully cleared table [network_fabric_handle].
Successfully cleared table [networked_vm].
Successfully cleared table [named_vm_group].
Successfully cleared table [monitored_entity].
Successfully cleared table [metric_configuration].
Successfully cleared table [metadata].
Successfully cleared table [managed_server_network_inv].
Successfully cleared table [managed_server_datastore_inv].
Successfully cleared table [os].
Successfully cleared table [managed_media].
Successfully cleared table [lock_intent].
Successfully cleared table [lock_handle].
Successfully cleared table [licensing_report].
Successfully cleared table [library_storage_profiles].
Successfully cleared table [ldisk_storage_class_join].
Successfully cleared table [ldisk_fo_join].
Successfully cleared table [ldap_provider].
Successfully cleared table [krb_realm_config].
Successfully cleared table [jobs]
Successfully cleared table [identity_provider].
Successfully cleared table [saml_id_provider_settings].
Successfully cleared table [oauth_id_provider_settings].
Successfully cleared table [guest_osfamily].
Successfully cleared table [member_source].
Successfully cleared table [group_user].
Successfully cleared table [gateway_logical_resource].
Successfully cleared table [gateway].
Successfully cleared table [from_cloud_tunnel_listener].
Successfully cleared table [folder].
Successfully cleared table [fifo_activity_queue].
Successfully cleared table [federation_settings].
Successfully cleared table [federated_user].
Successfully cleared table [federated_group].
Successfully cleared table [failed_cells].
Successfully cleared table [ext_localization_resources].
Successfully cleared table [ext_localization_bundles].
Successfully cleared table [extension].
Successfully cleared table [extensibility_selector].
Successfully cleared table [extensibility_phase].
Successfully cleared table [event].
Successfully cleared table [service_offering_instance].
Successfully cleared table [dv_switch_resource].
Successfully cleared table [disk_adapter_type].
Successfully cleared table [storage_class
Successfully cleared table [resource_item].
Successfully cleared table [reserved_ip_address].
Successfully cleared table [deleted_object].
Successfully cleared table [datastore_profile_inv].
Successfully cleared table [datacenter_network_inv].
Successfully cleared table [config].
Successfully cleared table [computehub_set].
Successfully cleared table [compute_fabric_handle].
Successfully cleared table [hardware_version].
Successfully cleared table [cluster_compute_resource_ext].
Successfully cleared table [cloud_proxy_ticket].
Successfully cleared table [cells].
Successfully cleared table [virtual_center].
Successfully cleared table [task].
Successfully cleared table [organization].
Successfully cleared table [audit_event].
Successfully cleared table [api_idempotent_store].
Successfully cleared table [api_external_service].
Successfully cleared table [amqp].
Successfully cleared table [ip_scope].
Successfully cleared table [activity_entity_record].
Successfully cleared table [activity].
Finished clearing tables for migration. Beginning database migration.
Migrating table [activity].
Successfully migrated table [activity] : (24)
Migrating table [activity_entity_record].
Successfully migrated table [activity_entity_record] : (0)
Migrating table [ip_scope].
Successfully migrated table [ip_scope] : (28)
Migrating table [amqp].
Successfully migrated table [amqp] : (1)
Migrating table [api_external_service].
Successfully migrated table [api_external_service] : (0)
Migrating table [api_idempotent_store].
Successfully migrated table [api_idempotent_store] : (0)
Migrating table [audit_event].
Successfully migrated table [audit_event] : (7,700)
Migrating table [organization].
Successfully migrated table [organization] : (12)
Migrating table [task].
Successfully migrated table [task] : (0)
Migrating table [virtual_center].
Successfully migrated table [virtual_center] : (1)
Migrating table [cells].
Successfully migrated table [cells] : (1
Migrating table [cloud_proxy_ticket].
Successfully migrated table [cloud_proxy_ticket] : (0)
Migrating table [cluster_compute_resource_ext].
Successfully migrated table [cluster_compute_resource_ext] : (4)
Migrating table [hardware_version].
Successfully migrated table [hardware_version] : (7)
Migrating table [compute_fabric_handle].
Successfully migrated table [compute_fabric_handle] : (61)
Migrating table [computehub_set].
Successfully migrated table [computehub_set] : (2)
Migrating table [config].
Successfully migrated table [config] : (323)
Migrating table [datacenter_network_inv].
Successfully migrated table [datacenter_network_inv] : (88)
Migrating table [datastore_profile_inv].
Successfully migrated table [datastore_profile_inv] : (19)
Migrating table [deleted_object].
Successfully migrated table [deleted_object] : (0)
Migrating table [reserved_ip_address].
Successfully migrated table [reserved_ip_address] : (0)
Migrating table [resource_item].
Successfully migrated table [resource_item] : (69,646)
Migrating table [storage_class].
Successfully migrated table [storage_class] : (4)
Migrating table [disk_adapter_type].
Successfully migrated table [disk_adapter_type] : (6)
Migrating table [dv_switch_resource].
Successfully migrated table [dv_switch_resource] : (1)
Migrating table [service_offering_instance].
Successfully migrated table [service_offering_instance] : (0)
Migrating table [event].
Successfully migrated table [event] : (0)
Migrating table [extensibility_phase].
Successfully migrated table [extensibility_phase] : (10)
Migrating table [extensibility_selector].
Successfully migrated table [extensibility_selector] : (4)
Migrating table [extension].
Successfully migrated table [extension] : (0)
Migrating table [ext_localization_bundles].
Successfully migrated table [ext_localization_bundles] : (0)
Migrating table [ext_localization_resources].
Successfully migrated table [ext_localization_resources] : (0)
Migrating table [failed_cells].
Successfully migrated table [failed_cells] : (0)
Migrating table [federated_group].
Successfully migrated table [federated_group] : (0)
Migrating table [federated_user].
Successfully migrated table [federated_user] : (0)
Migrating table [federation_settings].
Successfully migrated table [federation_settings] : (50)
Migrating table [fifo_activity_queue].
Successfully migrated table [fifo_activity_queue] : (24)
Migrating table [folder].
Successfully migrated table [folder] : (0)
Migrating table [from_cloud_tunnel_listener].
Successfully migrated table [from_cloud_tunnel_listener] : (0)
Migrating table [gateway].
Successfully migrated table [gateway] : (7)
Migrating table [gateway_logical_resource].
Successfully migrated table [gateway_logical_resource] : (1)
Migrating table [group_user].
Successfully migrated table [group_user] : (0)
Migrating table [member_source].
Successfully migrated table [member_source] : (14)
Migrating table [guest_osfamily].
Successfully migrated table [guest_osfamily] : (3)
Migrating table [oauth_id_provider_settings].
Successfully migrated table [oauth_id_provider_settings] : (0
Migrating table [saml_id_provider_settings
Successfully migrated table [saml_id_provider_settings] : (1)
Migrating table [identity_provider].
Successfully migrated table [identity_provider] : (15)
Migrating table [jobs].
Successfully migrated table [jobs] : (1,508)
Migrating table [krb_realm_config].
Successfully migrated table [krb_realm_config] : (0)
Migrating table [ldap_provider].
Successfully migrated table [ldap_provider] : (1)
Migrating table [ldisk_fo_join].
Successfully migrated table [ldisk_fo_join] : (9)
Migrating table [ldisk_storage_class_join].
Successfully migrated table [ldisk_storage_class_join] : (41)
Migrating table [library_storage_profiles].
Successfully migrated table [library_storage_profiles] : (4)
Migrating table [licensing_report].
Successfully migrated table [licensing_report] : (8)
Migrating table [lock_handle].
Successfully migrated table [lock_handle] : (0)
Migrating table [lock_intent].
Successfully migrated table [lock_intent] : (0)
Migrating table [managed_media].
Successfully migrated table [managed_media] : (23)
Migrating table [os]
Successfully migrated table [os] : (18)
Migrating table [managed_server_datastore_inv].
Successfully migrated table [managed_server_datastore_inv] : (29)
Migrating table [managed_server_network_inv].
Successfully migrated table [managed_server_network_inv] : (145)
Migrating table [metadata].
Successfully migrated table [metadata] : (24)
Migrating table [metric_configuration].
Successfully migrated table [metric_configuration] : (8)
Migrating table [monitored_entity].
Successfully migrated table [monitored_entity] : (0)
Migrating table [named_vm_group].
Successfully migrated table [named_vm_group] : (2)
Migrating table [networked_vm].
Successfully migrated table [networked_vm] : (38)
Migrating table [network_fabric_handle].
Successfully migrated table [network_fabric_handle] : (169)
Migrating table [network_pool_merge_data].
Successfully migrated table [network_pool_merge_data] : (0)
Migrating table [network_resources_config].
Successfully migrated table [network_resources_config] : (1)
Migrating table [object_condition].
Successfully migrated table [object_condition] : (83)
Migrating table [object_config].
Successfully migrated table [object_config] : (221)
Migrating table [object_extension].
Successfully migrated table [object_extension] : (0)
Migrating table [sites].
Successfully migrated table [sites] : (1)
Migrating table [org_delegation_user].
Successfully migrated table [org_delegation_user] : (0)
Migrating table [org_prov_vdc_specification].
Successfully migrated table [org_prov_vdc_specification] : (0)
Migrating table [rights].
Successfully migrated table [rights] : (213)
Migrating table [placement_subject].
Successfully migrated table [placement_subject] : (19)
Migrating table [reservation].
Successfully migrated table [reservation] : (1)
Migrating table [resource_assignment].
Successfully migrated table [resource_assignment] : (39)
Migrating table [transfer_session].
Successfully migrated table [transfer_session] : (0)
Migrating table [saml_message_storage].
Successfully migrated table [saml_message_storage] : (0)
Migrating table [scheduled_activity_jobs].
Successfully migrated table [scheduled_activity_jobs] : (24)
Migrating table [user_session].
Successfully migrated table [user_session] : (0)
Migrating table [shared_resource_assignment].
Successfully migrated table [shared_resource_assignment] : (0)
Migrating table [sprofile_compliance].
Successfully migrated table [sprofile_compliance] : (5)
Migrating table [storage_fabric_handle].
Successfully migrated table [storage_fabric_handle] : (16)
Migrating table [storage_profile_inv].
Successfully migrated table [storage_profile_inv] : (17)
Migrating table [task_inv].
Successfully migrated table [task_inv] : (20)
Migrating table [to_cloud_tunnel].
Successfully migrated table [to_cloud_tunnel] : (0)
Migrating table [transfer_server].
Successfully migrated table [transfer_server] : (0)
Migrating table [truststore].
Successfully migrated table [truststore] : (3)
Migrating table [tunneling_application].
Successfully migrated table [tunneling_application] : (0
Migrating table [ui_plugin_resource].
Successfully migrated table [ui_plugin_resource] : (0)
Migrating table [ui_portal_resource].
Successfully migrated table [ui_portal_resource] : (0)
Migrating table [ui_portal_theme].
Successfully migrated table [ui_portal_theme] : (2)
Migrating table [usr].
Successfully migrated table [usr] : (38
Migrating table [valc_storage_class_join].
Successfully migrated table [valc_storage_class_join] : (10)
Migrating table [vapp_vm_logical_disk].
Successfully migrated table [vapp_vm_logical_disk] : (1)
Migrating table [vc_activity_queue].
Successfully migrated table [vc_activity_queue] : (0)
Migrating table [vcenter_supported_builds].
Successfully migrated table [vcenter_supported_builds] : (6)
Migrating table [vc_task_mapping]
Successfully migrated table [vc_task_mapping] : (1,885)
Migrating table [activity_parameters].
Successfully migrated table [activity_parameters] : (24)
Migrating table [activity_results].
Successfully migrated table [activity_results] : (24)
Migrating table [allocated_ip_address].
Successfully migrated table [allocated_ip_address] : (21)
Migrating table [ip_range].
Successfully migrated table [ip_range] : (29)
Migrating table [api_definition].
Successfully migrated table [api_definition] : (0)
Migrating table [api_external_service_config].
Successfully migrated table [api_external_service_config] : (0)
Migrating table [api_link].
Successfully migrated table [api_link] : (0)
Migrating table [api_resource_type].
Successfully migrated table [api_resource_type] : (0)
Migrating table [branding].
Successfully migrated table [branding] : (1)
Migrating table [callout_endpoint].
Successfully migrated table [callout_endpoint] : (1)
Migrating table [catalog].
Successfully migrated table [catalog] : (10)
Migrating table [org_association_keys].
Successfully migrated table [org_association_keys] : (3)
Migrating table [org_email].
Successfully migrated table [org_email] : (12)
Migrating table [role].
Successfully migrated table [role] : (90)
Migrating table [smtp_server].
Successfully migrated table [smtp_server] : (12)
Migrating table [task_activity_queue].
Successfully migrated table [task_activity_queue] : (0)
Migrating table [busy_object].
Successfully migrated table [busy_object] : (0)
Migrating table [tenant_migration_history].
Successfully migrated table [tenant_migration_history] : (2)
Migrating table [cluster_compute_resource_inv].
Successfully migrated table [cluster_compute_resource_inv] : (1)
Migrating table [compute_resource_inv].
Successfully migrated table [compute_resource_inv] : (0)
Migrating table [custom_field_manager_inv].
Successfully migrated table [custom_field_manager_inv] : (1)
Migrating table [datacenter_inv].
Successfully migrated table [datacenter_inv] : (1)
Migrating table [datastore].
Successfully migrated table [datastore] : (9)
Migrating table [datastore_inv].
Successfully migrated table [datastore_inv] : (11)
Migrating table [dv_portgroup_inv].
Successfully migrated table [dv_portgroup_inv] : (23)
Migrating table [dv_switch].
Successfully migrated table [dv_switch] : (1)
Migrating table [dv_switch_inv].
Successfully migrated table [dv_switch_inv] : (1)
Migrating table [folder_inv].
Successfully migrated table [folder_inv] : (58)
Migrating table [managed_server_inv].
Successfully migrated table [managed_server_inv] : (3)
Migrating table [network_inv].
Successfully migrated table [network_inv] : (1)
Migrating table [network_pool].
Successfully migrated table [network_pool] : (1)
Migrating table [property_map].
Successfully migrated table [property_map] : (158)
Migrating table [prov_vdc].
Successfully migrated table [prov_vdc] : (1)
Migrating table [resource_pool_inv].
Successfully migrated table [resource_pool_inv] : (14)
Migrating table [storage_pod_inv].
Successfully migrated table [storage_pod_inv] : (1)
Migrating table [vc_computehub].
Successfully migrated table [vc_computehub] : (1)
Migrating table [vcenter_runtime].
Successfully migrated table [vcenter_runtime] : (1)
Migrating table [vm_inv].
Successfully migrated table [vm_inv] : (43)
Migrating table [vshield_manager].
Successfully migrated table [vshield_manager] : (1)
Migrating table [cell_application_instance].
Successfully migrated table [cell_application_instance] : (4)
Migrating table [cluster_hardware_version].
Successfully migrated table [cluster_hardware_version] : (5)
Migrating table [computehub_set_computehub].
Successfully migrated table [computehub_set_computehub] : (2)
Migrating table [vrp].
Successfully migrated table [vrp] : (12)
Migrating table [deleted_object_error_detail].
Successfully migrated table [deleted_object_error_detail] : (0)
Migrating table [deployed_ip_address].
Successfully migrated table [deployed_ip_address] : (0)
Migrating table [disk].
Successfully migrated table [disk] : (0)
Migrating table [vm].
Successfully migrated table [vm] : (37)
Migrating table [entity_soi].
Successfully migrated table [entity_soi] : (0)
Migrating table [service].
Successfully migrated table [service] : (0)
Migrating table [service_offering].
Successfully migrated table [service_offering] : (0)
Migrating table [from_cloud_tunnel].
Successfully migrated table [from_cloud_tunnel] : (0)
Migrating table [gateway_assigned_mac].
Successfully migrated table [gateway_assigned_mac] : (4)
Migrating table [grp].
Successfully migrated table [grp] : (0)
Migrating table [org_member_source].
Successfully migrated table [org_member_source] : (14)
Migrating table [hardware_version_guest_os].
Successfully migrated table [hardware_version_guest_os] : (540)
Migrating table [oauth_key_configuration].
Successfully migrated table [oauth_key_configuration] : (0)
Migrating table [iam_id_provider_settings].
Successfully migrated table [iam_id_provider_settings] : (0)
Migrating table [job_detail].
Successfully migrated table [job_detail] : (130)
Migrating table [last_jobs].
Successfully migrated table [last_jobs] : (509)
Migrating table [krb_domain_realm_mapping].
Successfully migrated table [krb_domain_realm_mapping] : (0)
Migrating table [licensing_metric].
Successfully migrated table [licensing_metric] : (5,432)
Migrating table [managed_server].
Successfully migrated table [managed_server] : (3)
Migrating table [metadata_boolean_value].
Successfully migrated table [metadata_boolean_value] : (0)
Migrating table [metadata_date_value].
Successfully migrated table [metadata_date_value] : (0)
Migrating table [metadata_long_value].
Successfully migrated table [metadata_long_value] : (0)
Migrating table [metadata_string_value].
Successfully migrated table [metadata_string_value] : (24)
Migrating table [metadata_text_value].
Successfully migrated table [metadata_text_value] : (0)
Migrating table [networked_vm_mac].
Successfully migrated table [networked_vm_mac] : (47)
Migrating table [object_extension_selector].
Successfully migrated table [object_extension_selector] : (0)
Migrating table [selector_extension].
Successfully migrated table [selector_extension] : (0)
Migrating table [org_association].
Successfully migrated table [org_association] : (0)
Migrating table [remote_orgs].
Successfully migrated table [remote_orgs] : (0)
Migrating table [site_status].
Successfully migrated table [site_status] : (0)
Migrating table [org_prov_vdc_specification_nw].
Successfully migrated table [org_prov_vdc_specification_nw] : (0)
Migrating table [org_prov_vdc_template].
Successfully migrated table [org_prov_vdc_template] : (0)
Migrating table [org_prov_vdc_template_sprofile].
Successfully migrated table [org_prov_vdc_template_sprofile] : (0)
Migrating table [org_right].
Successfully migrated table [org_right] : (1,355)
Migrating table [placement_subject_item].
Successfully migrated table [placement_subject_item] : (36)
Migrating table [resource_file].
Successfully migrated table [resource_file] : (0)
Migrating table [ui_plugin].
Successfully migrated table [ui_plugin] : (0)
Migrating table [ui_portal_branding].
Successfully migrated table [ui_portal_branding] : (1)
Migrating table [api_def_supported_versions].
Successfully migrated table [api_def_supported_versions] : (0)
Migrating table [api_file_descriptor].
Successfully migrated table [api_file_descriptor] : (0)
Migrating table [api_action].
Successfully migrated table [api_action] : (0)
Migrating table [api_resource].
Successfully migrated table [api_resource] : (0)
Migrating table [callout_collaboration].
Successfully migrated table [callout_collaboration] : (0)
Migrating table [callout_task_binding].
Successfully migrated table [callout_task_binding] : (0)
Migrating table [catalog_item].
Successfully migrated table [catalog_item] : (51)
Migrating table [org_member].
Successfully migrated table [org_member] : (50)
Migrating table [role_right].
Successfully migrated table [role_right] : (3,673)
Migrating table [session_role].
Successfully migrated table [session_role] : (0)
Migrating table [ccr_drs_host_group_inv].
Successfully migrated table [ccr_drs_host_group_inv] : (2)
Migrating table [ccr_drs_rule_inv].
Successfully migrated table [ccr_drs_rule_inv] : (0)
Migrating table [ccr_drs_vm_group_inv].
Successfully migrated table [ccr_drs_vm_group_inv] : (2)
Migrating table [ccr_drs_vm_host_rule_inv].
Successfully migrated table [ccr_drs_vm_host_rule_inv] : (2)
Migrating table [real_network].
Successfully migrated table [real_network] : (16)
Migrating table [vds_context].
Successfully migrated table [vds_context] : (1)
Migrating table [org_prov_vdc].
Successfully migrated table [org_prov_vdc] : (10)
Migrating table [prov_vdc_logical_resource].
Successfully migrated table [prov_vdc_logical_resource] : (5)
Migrating table [vm_dstore_metrics_inv].
Successfully migrated table [vm_dstore_metrics_inv] : (46)
Migrating table [vm_import_activity].
Successfully migrated table [vm_import_activity] : (0)
Migrating table [cell_application_table_access].
Successfully migrated table [cell_application_table_access] : (762)
Migrating table [computevm].
Successfully migrated table [computevm] : (47)
Migrating table [vrp_rp].
Successfully migrated table [vrp_rp] : (12)
Migrating table [deployed_vm].
Successfully migrated table [deployed_vm] : (9)
Migrating table [shadow_vm].
Successfully migrated table [shadow_vm] : (1)
Migrating table [vm_disk].
Successfully migrated table [vm_disk] : (0)
Migrating table [vm_disk_storage_class].
Successfully migrated table [vm_disk_storage_class] : (3)
Migrating table [vm_resource].
Successfully migrated table [vm_resource] : (0)
Migrating table [entity_so].
Successfully migrated table [entity_so] : (0)
Migrating table [params_metainfo_so].
Successfully migrated table [params_metainfo_so] : (0)
Migrating table [params_so].
Successfully migrated table [params_so] : (0)
Migrating table [licensing_ms_data].
Successfully migrated table [licensing_ms_data] : (19,842)
Migrating table [selector_extension_phase].
Successfully migrated table [selector_extension_phase] : (0)
Migrating table [org_prov_vdc_template_access].
Successfully migrated table [org_prov_vdc_template_access] : (0)
Migrating table [org_prov_vdc_template_bindings].
Successfully migrated table [org_prov_vdc_template_bindings] : (0)
Migrating table [org_prov_vdc_template_pvdcs].
Successfully migrated table [org_prov_vdc_template_pvdcs] : (0)
Migrating table [ui_plugin_access].
Successfully migrated table [ui_plugin_access] : (0)
Migrating table [ui_portal_link].
Successfully migrated table [ui_portal_link] : (0)
Migrating table [api_acl_rule].
Successfully migrated table [api_acl_rule] : (0)
Migrating table [acl].
Successfully migrated table [acl] : (12)
Migrating table [org_member_preference].
Successfully migrated table [org_member_preference] : (39)
Migrating table [ccr_drs_host_group_host_inv].
Successfully migrated table [ccr_drs_host_group_host_inv] : (3)
Migrating table [drs_rule_vm_inv].
Successfully migrated table [drs_rule_vm_inv] : (0)
Migrating table [ccr_drs_vm_group_vm_inv].
Successfully migrated table [ccr_drs_vm_group_vm_inv] : (2)
Migrating table [ccr_drs_vm_group_vm_pending].
Successfully migrated table [ccr_drs_vm_group_vm_pending] : (0)
Migrating table [logical_network].
Unable to migrate table [logical_network] to target database.
Migrating table [network_backing].
Successfully migrated table [network_backing] : (0)
Migrating table [real_network_backing].
Successfully migrated table [real_network_backing] : (16)
Migrating table [logical_disk].
Successfully migrated table [logical_disk] : (41)
Migrating table [org_vdc_network_services].
Successfully migrated table [org_vdc_network_services] : (0)
Migrating table [vdc_logical_resource].
Successfully migrated table [vdc_logical_resource] : (45)
Migrating table [vm_container].
Successfully migrated table [vm_container] : (31)
Migrating table [deployed_vm_resource].
Successfully migrated table [deployed_vm_resource] : (0)
Migrating table [vm_snapshot].
Successfully migrated table [vm_snapshot] : (1)
Migrating table [params_soi].
Successfully migrated table [params_soi] : (0)
Migrating table [gateway_interface].
Unable to migrate table [gateway_interface] to target database.
Migrating table [logical_network_ip_scope].
Unable to migrate table [logical_network_ip_scope] to target database.
Migrating table [network_interface].
Unable to migrate table [network_interface] to target database.
Migrating table [shared_org_vdc_network].
Unable to migrate table [shared_org_vdc_network] to target database.
Migrating table [vdc_lr_encumbrance].
Successfully migrated table [vdc_lr_encumbrance] : (0)
Migrating table [vdc_storage_class_settings].
Successfully migrated table [vdc_storage_class_settings] : (0)
Migrating table [vapp_logical_resource].
Successfully migrated table [vapp_logical_resource] : (29)
Migrating table [vapp_vm].
Successfully migrated table [vapp_vm] : (38)
Migrating table [vm_snapshot_disk].
Successfully migrated table [vm_snapshot_disk] : (0)
Migrating table [gateway_assigned_ip].
Unable to migrate table [gateway_assigned_ip] to target database.
Migrating table [address_translation].
Successfully migrated table [address_translation] : (0)
Migrating table [guest_personalization_info].
Successfully migrated table [guest_personalization_info] : (37)
Migrating table [vapp_eula].
Successfully migrated table [vapp_eula] : (1)
Migrating table [vapp_product_info].
Successfully migrated table [vapp_product_info] : (9)
Migrating table [vapp_vm_disk_storage_class].
Successfully migrated table [vapp_vm_disk_storage_class] : (3)
Migrating table [vapp_vm_sclass_metrics].
Successfully migrated table [vapp_vm_sclass_metrics] : (39)
Migrating table [vm_gosc_status].
Successfully migrated table [vm_gosc_status] : (38)
Migrating table [vapp_property].
Successfully migrated table [vapp_property] : (106)
Successfully restarted sequence seq_config for table config.config_id at value 18,892
Successfully restarted sequence seq_vcenter_supported_builds for table vcenter_supported_builds.id at value 7
Successfully restarted sequence seq_audit_event_internal_id for table audit_event.internal_id at value 85,166
Successfully restarted sequence seq_cell_instance_id for table cells.instance_id at value 1,242
Successfully restarted sequence seq_os for table os.os_id at value 1,085
Successfully restarted sequence seq_guest_os_family for table guest_osfamily.family_id at value 4
Successfully restarted sequence seq_resource for table resource_item.resource_id at value 69,741
Successfully restarted sequence seq_disk_adapter_type for table disk_adapter_type.diskadapter_id at value 7
Database migration exited with table failures.
SUCCESS:248
FAILED:6
SKIPPED:0

Just to give some background on these errors and how I ended up with issues trying to perform a DB migration let me tell that weeks before attempting to migrate this DB my vCD cell was experiencing errors to remove network configuration from Org-VDC.

Tables presenting error were the same modified on a previous case to delete objects using MSSSQL queries where deletion was not possible from vCD GUI.

Error messages trying to delete an organization were about removed networks still in use and IP address still allocated right after Org network was removed from vCD GUI.

Error message seen in vCloud Director was “Real networks from this vCenter Server are still referenced.”
This issues was fixed then by running SQL queries to remove objects referencing that Org-VDC.

So DB migration was suspended obviously until DB cleanup was completed.

Result pointed errors on tables modified with queries for removal of Org network objects but some leftovers still existed in the DB and re-inserting removed objects with empty configuration was now needed to completely remove all dependencies between tables and be able to migrate DB.

Resolution

Sequence of queries to be run included INSERT, SELECT ( to check existence of objects) and finally DELETE to remove them completely.

Remember to take a backup of your DB.


INSERT statements

Insert into gateway_interface values (0x124CF65A79A14EF4B22A54D19F1549DA, 0x23EEC5568F2045B89E9A9F7086D6473B, 0xFE82A8CAE618427AA307673AE7F915A1, 'vnic1', 'Management-Int', '1', '1', '0','0', '0', NULL, NULL, '0');

Insert into logical_network_ip_scope values (0x39A2BEB3E1B94300A8F70CD988E8F576, 0x01C4228A5D58460DB82C8E873DFFE962);

Insert into network_interface values (0x0226A7E03A494619A3CB4150D9D15DFA, NULL, 0x609260CC39C6424FA22E8F415245EC1E,'0', '5', '00:50:56:09:01:52', '4', NULL, NULL, NULL, NULL, NULL, 0xA6F67CF847DD4EA0A622D319A04B6FD6, NULL, '1', NULL, NULL, NULL, NULL, '0', '2');

Insert into shared_org_vdc_network values (0xBDB02C473513406AA47FE16CC094A6CA, 0x1A6C0E45D0C04AFE830C16F694CF8001, 0x7F8E628CA73B492C85D59376F15C1240);

Insert into gateway_assigned_ip values(0xA9FE5CD04C2642FDB781E181A5037396, 0x7348C578484D45A9A8BA5C7B5C4EA1C5,'0', 0xCDC0AD2B1A3A433FB2439049B34887F3, 'b3ba022b-0ef1-3cd2-80bd-1014c379aa25', '0', '1');

SELECT queries:

Select * from logical_network_ip_scope where logical_network_id in (0x1EDC788F771944B9974EF6210E678262 ,0x32171017912E40669673CD58D95B9E4D, 0x76542134082C47DE84B0C3E4E05E74E4, 0xEB365A733CE541DEA354481739D3E6DC)

Select * from logical_network where id in (0x1EDC788F771944B9974EF6210E678262 ,0x32171017912E40669673CD58D95B9E4D, 0x76542134082C47DE84B0C3E4E05E74E4, 0xEB365A733CE541DEA354481739D3E6DC)

DELETE queries:

delete from logical_network_ip_scope where logical_network_id in (0x1EDC788F771944B9974EF6210E678262 ,0x32171017912E40669673CD58D95B9E4D, 0x76542134082C47DE84B0C3E4E05E74E4, 0xEB365A733CE541DEA354481739D3E6DC)

delete from logical_network where id in (0x1EDC788F771944B9974EF6210E678262 ,0x32171017912E40669673CD58D95B9E4D, 0x76542134082C47DE84B0C3E4E05E74E4, 0xEB365A733CE541DEA354481739D3E6DC)

Finally I was visualising last days of that Windows machine running MSSQL and migrate my DB to remove cost on license.

Once above was done DB migration was executed once more and completed successfully now my lab is using Postgres DB.

Leave a Reply