Remove update manager tables from the vCenter Server Database when update manager is uninstalled or moved to its own database

Products

• VMware vCenter Server
• VMware vSphere ESXi

Issue/Introduction

This article provides steps to remove the vSphere Update Manager objects from the database without impacting vCenter Server.

 

If the vSphere Update Manager is installed against the vCenter Server database, it is possible to remove the vSphere Update Manager objects from the database without impacting vCenter Server.

 

Note: VMware recommends installing vSphere Update Manager against its own database. However you can also install vSphere Update Manager against the vCenter Server database.

Resolution

Steps to remove the vSphere Update Manager objects from the database:

 

• Connect to the vCenter database via MSSQL Management Studio as user having sufficient permissions
• Select the vCenter Server database from the drop down menu
• Run these sql statements, where <VCDB> is the vCenter Server database name to generate the drop statements for the Foreign Key constraints:
  
  USE <VCDB>;
  
  GO
  
  SELECT
  
  'ALTER TABLE ['+ b.name + '] DROP CONSTRAINT [' + a.name + '];'
  
  FROM sys.foreign_keys a, sys.tables b
  
  WHERE b.name like 'VCI%'
  
  AND
  
  a.parent_object_id=b.object_id

 

• Run these sql statements, where <VCDB> is the vCenter Server database name to generate the drop statements for the VUM tables:
  
  USE <VCDB>;
  
  GO
  
  SELECT
  
  'DROP TABLE ' + name;
  
  FROM sys.tables
  
  WHERE name like 'vci%'

 

• Run these sql statements, where <VCDB> is the vCenter Server database name to generate the drop statements for the VUM stored procedures:
  
  USE <VCDB>;
  
  GO
  
  SELECT
  
  'DROP PROCEDURE [' + name + '];'
  
  FROM sys.procedures
  
  WHERE name like 'vci%'

• Run these sql statements, where <VCDB> is the vCenter Server database name to generate the drop statements for the VUM views:
  
  USE <VCDB>;
  
  GO
  
  SELECT 'DROP VIEW ['+name +'];' FROM sys.views
  
  WHERE name like 'vumv_%'

• After removing the update manager objects from the vCenter Server Database a new blank VUM database can be created in MSSQL management studio.
• Modify the 32 bit DSN/ODBC for the update manager to point to the new database ensuring that the user is defined in the ODBC has sufficient permissions on the VUM database.

Additional Information

To create database objects for the Update Manager application see Initializing the Windows-based VMware vCenter Update Manager database without reinstalling it.