Using the VMware vCenter Server Database Pre-Upgrade Checker

search cancel

Using the VMware vCenter Server Database Pre-Upgrade Checker

book

Article ID: 340910

calendar_today

Updated On:

Products

VMware vCenter Server

Issue/Introduction

Before you upgrade vCenter Server, you can run the VMware vCenter Server Database Pre-Upgrade Checker on your current vCenter Server database to reveal problems that could prevent the upgrade or affect the performance of your database after the upgrade. You can use the Pre-Upgrade Checker for these upgrades:

On a VirtualCenter 2.5 Update 6 or later database before you upgrade to vCenter Server 4.x.
On a vCenter Server 4.0.x database before you upgrade to vCenter Server 4.1.x, 5.0.x, or 5.1.x.
On a vCenter Server 4.1.x database before you upgrade to vCenter Server 5.0.x or 5.1.x.
On a vCenter Server 5.0.x database before you upgrade to vCenter Server 5.1.x.
On a vCenter Server 5.1.x database before you upgrade to vCenter Server 5.5.x.

Note: You do not need to run the Pre-Upgrade Checker for minor update releases: for example, from version 4.0 to version 4.0 Update 3.

This article explains how to use the VMware vCenter Server Database Pre-Upgrade Checker.

Environment

VMware vCenter Server 5.0.x
VMware vCenter Server 5.1.x
VMware vCenter Server 5.5.x

Resolution

What the Pre-Upgrade Checker Checks

The Pre-Upgrade Checker compares your VCDB database signature profile with a known correct standard for your VCDB database version. A database signature profile is a representation of database structure and dependent objects. The Pre-Upgrade Checker creates a signature profile configuration file for your existing (pre-upgrade) database and compares it with the known correct signature profile for your VCDB database version. If the Pre-Upgrade Checker identifies a difference between your database signature profile and the known VCDB signature profile, the check fails. If your VCDB database signature profile matches the known signature profile, the check verifies that your database can be upgraded.

The Pre-Upgrade Checker also performs these checks.

Check for multiple schemas in the customer database.
Database user and role check. Checks that the given database user has the correct privilege to upgrade.
Table and structure check. No data checks are included in this check.
SQL compatibility mode check for Microsoft SQL Server. Determines whether the compatibility mode is set to an appropriate and supported level.

The Pre-Upgrade Checker outputs a single .zip file, which contains both the database signature file and the message log from running the Pre-Upgrade Checker.

Note: The Pre-Upgrade Checker works only with vCenter Server installations on Windows. The Pre-Upgrade Checker does not work with the vCenter Server Appliance.

Information required to identify and authenticate a connection to your database

In the Pre-Upgrade Checker, you can identify and authenticate a connection to your database by ODBC or by entering your database credentials. The required entries vary, depending on your database type and version. The Pre-Upgrade Checker enables only the options that are applicable to your connection method (ODBC or credentials), database type, and version. In some cases, you do not need to make entries in all fields that are enabled.

Note: If your vCenter Server uses the embedded (bundled) version of Microsoft SQL, make entries in the following fields to identify and authenticate the connection to your database: Host, Database, Instance, and Domain.

Table 1. Information Required to Identify and Authenticate a Connection to Your Database

Database Type	Required Information
Host	The IP address or Fully Qualified Domain Name (FQDN) of the vCenter Server host machine
Port	The port number to connect to the database
Database (Microsoft SQL Server)	The name of the database
Instance (Microsoft SQL Server)	The instance name of the database
Domain (Microsoft SQL Server)	The name of the domain that the database is in
SID (Oracle)	The SID of the database
Service Name (Oracle)	The service name of the database
Connection String (Oracle, with Oracle RAC)	A string you have set to connect to the database
User	A user name for a user with permissions to connect to the database and select from tables.
Password	The password for the user

Known issues

These known issues have been discovered in the Pre-Upgrade Checker.

The Pre-Upgrade Checker supports Oracle ODBC DSNs only if the ODBC DSNs use TNSNAMES.ORA to specify database credentials.
In very rare cases, you might choose not to use TNSNAMES.ORA to define databases addresses for the purpose of establishing connections to them. Instead, you can supply the connection string to your database in the TNS Service Name. In this situation, use your database credentials to identify and authenticate a connection to your database.
The Pre-Upgrade Checker supports DB2 ODBC DSNs only if the ODBC DSNs use the Remote directory entry type with TCP/IP connections.
When you use the Windows graphical user interface to create a data source for DB2, you specify the database host name in the tab TCP/IP, in the field Host name. The Pre-Upgrade Checker does not support the entry localhost for the host name. Instead of localhost, enter the IP address (including 127.0.0.1) or the FQDN. This creates a Remote directory entry, which the Pre-Upgrade Checker supports. Note: You cannot edit an existing data source to change the localhost entry. Create a new data source.

The Pre-Upgrade Checker supports MSSQL connection with Windows Authentication but only when a jTDS-specific .dll file is provided.
Workaround:
1. Go to the jTDS download page: http://sourceforge.net/projects/jtds/files
2. Choose jTDS 1.2.2 or higher and download the .zip file.
3. Unzip the .zip file.
4. Browse to the x86 or x64 folder depending on the JRE/JDK version you are using to the Single Sing-On folder which contains ntlmauth.dll.
5. Copy ntlmauth.dll into your JRE/JDK bin folder.

Running the Pre-Upgrade Checker

Prerequisites:

Verify that the vCenter Server host machine has 32/64-bit JDK 1.6 update 19 or later installed, and that Java is included in the PATH environment variable.
For Microsoft SQL databases, verify that the TCP/IP Protocol is enabled. In the SQL Configuration Manager, under SQL Server Network Configuration, select Protocols for <your_vCenter_Server_db_instance>, and, in the right-hand panel, enable TCP/IP.
Gather the information necessary to identify and authenticate a connection to your database. See Table 1.

To run the Pre-Upgrade Checker.

Download the attached Pre-Upgrade Checker DBPreUpgradeChecker.zip and unzip the file.

If you identify and authenticate a connection to your database by ODBC, run the Pre-Upgrade Checker on the host where the ODBC resides. If you identify and authenticate a connection to your database by database credentials, you can run the Pre-Upgrade Checker on any host that is allowed to connect to the vCenter server database.
From a command prompt, start the Pre-Upgrade Checker:

<path_to_DBPreUpgradeChecker>DBPreUpgradeChecker.bat
(for use with 32-bit JRE/JDK)
or
<path_to_DBPreUpgradeChecker>DBPreUpgradeChecker64.bat
(for use with 64-bit JRE/JDK)
Select a connection method: ODBC or Credentials.
In the RDBMS drop-down box, select your VCDB database type.
Identify your database and authenticate your database credentials. See Table 1.
Set the file name and location to store the output file, which contains the signature profile configuration and the message log.
1. Click the File button.
2. Browse to the location to save the file.
3. Type the File Name, for example, vcdb_scf.xml.
4. Click Save.
  
  When you have entered sufficient information to identify and authenticate a connection to your database, Run is enabled.
Click Run.

The Pre-Upgrade Checker creates the signature profile configuration file, and checks the file against the known correct signature profile for your database type and version. The results, with any error or warning messages, are displayed in the text box at the bottom of the DB Pre-Upgrade Checker dialog box. Table 2 lists the messages that can be reported in the output. To preserve this output for future reference, copy the output and paste it into a text file.

Error and warning messages reported by the Pre-Upgrade Checker

Table 2 lists the possible error and warning messages listed in the output of the Pre-Upgrade Checker. Error messages indicate problems that prevent the upgrade from taking place. Warning messages indicate problems that do not prevent the upgrade, but might affect the operation of the vCenter Server after the upgrade.

Table 2. Error and Warning Messages Reported by the Pre-Upgrade Checker

Message

Problem

Error: Extra constraint in customer's database >> .<constraint>

Your database has an extra constraint compared to the original vCenter Server database of the same version. The extra constraint prevents the upgrade process. Drop the extra constraint from your database.

Error: Extra index in customer's database >> .<index>

Your database has an extra index compared to the original vCenter Server database of the same version. The extra index prevents the upgrade process. Drop the extra index from your database.

Error: Extra routine in customer's database >> <routine name>

Your database contains an extra routine in database with the _PROC suffix. Because this suffix is reserved for vCenter Server database routines, the extra routine could prevent the upgrade process. Rename the routine. Do not use the _PROC suffix for the routine name.

Error: Extra table in customer's database >>

Your database contains an extra table with the VPX_ prefix. Because this prefix is reserved for vCenter Server database tables, the extra table can prevent the upgrade process. Rename the table. Do not use the VPX_ prefix for the table name.

Error: Extra view in customer's database >> <view name>

Your database contains an extra view with the VPXV_ prefix. Because this prefix is reserved for vCenter Server database views, the extra view could prevent the upgrade process. Rename the view. Do not use the VPXV_ prefix for the view name.

Error: Missing column in customer's table >> .<column>

A column is missing in your database for one of the VPX_ tables. This column is critical for the database work process.

Error: Missing constraint in customer's database >> .<constraint>

The missing constraint is critical for the database upgrade process.

Error: Missing index in customer's database >> .<index>

The missing index named in the message is critical for the database upgrade process.

Error: Missing permission for VC user: <permission>

A required user permission is missing from your database. See Tables 3, 4, and 5.

Error: Missing table in customer's database >>

All original vCenter Server database tables must be present before you upgrade the database.

Error: Missing trigger in customer's database >> .<trigger>

A trigger is missing from your database. This trigger is required for the database upgrade process.

Error: Missing view in customer's database >> <view name>

All original vCenter Server database views must be present before you upgrade the database.

Error: MSSQL 2005 in 2000 compatibility mode is not supported after VC 2.5.

Microsoft SQL Server 2005 can work in 2000 compatibility mode with VirtualCenter 2.5. All VirtualCenter and vCenter Server versions after VirtualCenter 2.5 do not work with Microsoft SQL 2005 in 2000 compatibility mode. Change the compatibility setting to
2005: EXEC sp_dbcmptlevel <VCDB>, 90;

Error: VCDB .<column> data type >> <data type>; customer >> <data type> </data></data>

A column in your database has a data type that differs from the same column in the original vCenter Server database. This problem prevents the upgrade process: the mismatch in data type would prevent the vCenter Server from working. For both Oracle and Microsoft SQL Server, changing a column's data type requires that you specify all column attributes, such as default values, null settings, etc. For IBM DB2, you need to specify only the data type:
ALTER TABLE ALTER COLUMN <column> SET DATA TYPE <data type>; </data>

Error: VCDB user has more than one DB schema. Please remove the extra DB schema!

A Microsoft SQL Server vCenter Server database supports only a single schema. If you have introduced a new database schema, remove it from the database before upgrading. Run the following query against your Microsoft SQL database server:
SELECT distinct sys.schemas.name FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id and sys.schemas.name <>'sys';
This query returns a list of the available database schemas. A Microsoft SQL Server vCenter Server database should have only the dbo schema. Any other schemas must be dropped. Remove (drop or move to another schema) all objects in that schema and then drop the extra schema by running the following command:
DROP SCHEMA <schema_name>;

Warning: Extra column for constraint in customer's database >> <constraint>.<column>

The named constraint in your database has an extra column. The extra column does not prevent the database upgrade. You can ignore this message.

Warning: Extra column for index in customer's database >> <index>.<column>

The named index in your database has an extra column. The extra column does not prevent the database upgrade. You can ignore this message.

Warning: Extra column in customer's table >> .<column>

A table in your database has an extra column compared to the original vCenter Server database of the same version. The extra column does not prevent the database upgrade process. You can ignore this message.

Warning: Extra constraint in customer's database >> .<constraint>

Your database has an extra constraint compared to the original vCenter Server database of the same version. The extra constraint does not prevent the database upgrade process, but this problem might cause data inconsistency.

Warning: Extra index in customer's database >> .<index>

Your database has an extra index compared to the original vCenter Server database of the same version. The extra index does not prevent the database upgrade process, but this problem might degrade your database performance.

Warning: Extra routine in customer's database >> <routine name>

Your database contains an extra routine, but the extra routine's name does not end with the reserved _PROC suffix. The extra routine does not prevent the vCenter Server database upgrade. You can ignore this message.

Warning: Extra table in customer's database >>

Your database contains an extra table, but the extra table's name does not start with the reserved VPX_ prefix. The extra table does not prevent the vCenter Server database upgrade. You can ignore this message.

Warning: Extra trigger in customer's database >> .<trigger>

The vCenter Server database does not currently use triggers. The extra trigger does not prevent the database upgrade. You can ignore this message.

Warning: Extra view in customer's database >> <view name>

Your database contains an extra view, but the extra view's name does not start with the reserved VPXV_ prefix. The extra view does not prevent the vCenter Server database upgrade. You can ignore this message.

Warning: Missing column for constraint in customer's database >> <constraint>.<column>

A constraint in your database lacks a column that was present in the originally deployed vCenter Server database. The missing column does not prevent the database upgrade process, but this problem might cause future data consistency.

Warning: Missing column for index in customer's database >> <index>.<column>

An index in your database lacks a column that was present in the originally deployed vCenter Server database. The column does not prevent the database upgrade process.

Warning: Missing column for view in customer's database >> <view>.<column>

A view in your database lacks a column that was present in the originally deployed vCenter Server database. The missing view does not prevent the database upgrade process, but this problem might degrade the performance of your database.

Warning: Missing constraint in customer's database >> .<constraint>

The missing constraint does not prevent the database upgrade process but this problem might cause future data inconsistency.

Warning: Missing index in customer's database >> .<index>

An index is missing in your database. This index does not prevent the database upgrade process but this might cause future performance problems.

Warning: Missing routine in customer's database >> <routine name>

A stored procedure or function is missing from your database. The missing routine does not affect the database upgrade, because all routines are recreated in the database upgrade process. You can ignore this message.

Warning: Trigger type for <trigger> in customer's database is <type> but should be <type>

A trigger type in your database differs from the same trigger in the vCenter Server database. The different trigger type does not prevent the database upgrade. You can ignore this message.

Warning: VCDB <constraint> type >> <type>; Customer type >> <type>

The named constraint type in your database differs from the same constraint in the original vCenter Server database. The different constraint type does not prevent the database upgrade. You can ignore this message.

Warning: VCDB .<column> default/pos/NULL >> <def/pos/null>; customer >> <def/pos/null>

A column in your database has been changed so that it has a different position, default, or null value. The changed column does not prevent the database upgrade. Changing the position of a column in the table requires data population and table recreation and is not advisable. The vCenter Server database and application do not require that a column is correctly positioned. For both Oracle and Microsoft SQL, you can change the default and null values in the column data type settings. For IBM DB2, you do not need data type information to change the default or null values:
-- set new default value ALTER TABLE ALTER COLUMN <column> SET DEFAULT <default>;
-- remove default value ALTER TABLE

ALTER COLUMN <column> DROP DEFAULT <default>;
-- set column is not nullable ALTER TABLE ALTER COLUMN <column> SET NOT NULL;
-- set column is nullable ALTER TABLE

ALTER COLUMN <column> DROP NOT NULL;

Additional Information

Setting User Permissions Reported as Missing by the Pre-Upgrade Checker

Tables 3, 4, and 5 list the database permissions that the Pre-Upgrade Checker might report as missing, and the corresponding commands to grant the missing permissions.

Table 3. User Permissions Required for Microsoft SQL Server Database Upgrade.

Missing Permission	Solution
CREATE TABLE	GRANT CREATE TABLE to VC_ADMIN_ROLE;
CREATE VIEW	GRANT CREATE VIEW to VC_ADMIN_ROLE;
CREATE PROCEDURE	GRANT CREATE PROCEDURE to VC_ADMIN_ROLE;
ALTER ON SCHEMA <schema name>	GRANT ALTER ON SCHEMA <schema name> to VC_ADMIN_ROLE;
REFERENCES ON SCHEMA <schema name>	GRANT REFERENCES ON SCHEMA <schema name> to VC_ADMIN_ROLE;
SELECT ON SCHEMA <schema name>	GRANT SELECT ON SCHEMA <schema name> to VC_USER_ROLE;
INSERT ON SCHEMA <schema name>	GRANT INSERT ON SCHEMA <schema name> to VC_USER_ROLE;
DELETE ON SCHEMA <schema name>	GRANT DELETE ON SCHEMA <schema name> to VC_USER_ROLE;
UPDATE ON SCHEMA <schema name>	GRANT UPDATE ON SCHEMA <schema name> to VC_USER_ROLE;
EXECUTE ON SCHEMA <schema name>	GRANT EXECUTE ON SCHEMA <schema name> to VC_USER_ROLE;
SELECT syscategories	GRANT SELECT ON msdb.dbo.syscategories TO VC_ADMIN_ROLE;
SELECT sysjobsteps	GRANT SELECT ON msdb.dbo.sysjobsteps TO VC_ADMIN_ROLE;
SELECT sysjobs	GRANT SELECT ON msdb.dbo.sysjobs TO VC_ADMIN_ROLE;
EXECUTE sp_add_job	GRANT EXECUTE ON msdb.dbo.sp_add_job TO VC_ADMIN_ROLE;
EXECUTE sp_delete_job	GRANT EXECUTE ON msdb.dbo.sp_delete_job TO VC_ADMIN_ROLE;
EXECUTE sp_add_jobstep	GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO VC_ADMIN_ROLE;
EXECUTE sp_update_job	GRANT EXECUTE ON msdb.dbo.sp_update_job TO VC_ADMIN_ROLE;
EXECUTE sp_add_jobserver	GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO VC_ADMIN_ROLE;
EXECUTE sp_add_jobschedule	GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO VC_ADMIN_ROLE;
EXECUTE sp_add_category	GRANT EXECUTE ON msdb.dbo.sp_add_category TO VC_ADMIN_ROLE;

Table 4. User Permissions Required for Oracle Database Upgrade.

Missing Permission	Solution
CREATE TABLE	GRANT CREATE TABLE TO <vpxuser>;
CREATE VIEW	GRANT CREATE VIEW TO <vpxuser>;
CREATE PROCEDURE	GRANT CREATE PROCEDURE TO <vpxuser>;
CREATE METERIALIZED VIEW	GRANT CREATE METERIALIZED VIEW TO <vpxuser>;
CREATE SEQUENCE	GRANT CREATE SEQUENCE TO <vpxuser>;
EXECUTE ON DBMS_LOCK	GRANT EXECUTE ON DBMS_LOCK TO <vpxuser>;
EXECUTE ON DBMS_JOB	GRANT EXECUTE ON DBMS_JOB TO <vpxuser>;

Table 5. User Permissions Required for IBM DB2 Database Upgrade.

Missing Permission	Solution
LARGE TABLESPACE VCTS_8k 8192	IF NOT EXISTS(SELECT * FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'VCBP_8K') THEN CREATE BUFFERPOOL VCBP_8K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 8K; END IF; IF NOT EXISTS(SELECT * FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'VCTS_8k') THEN CREATE LARGE TABLESPACE VCTS_8k PAGESIZE 8K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 12.67 PREFETCHSIZE 32 TRANSFERRATE 0.18 BUFFERPOOL VCBP_8K; END IF; GRANT USE OF TABLESPACE VCTS_8K TO USER <vCenter user> WITH GRANT OPTION;
LARGE TABLESPACE VCTS_16k 16384	IF NOT EXISTS(SELECT * FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'VCBP_16K') THEN CREATE BUFFERPOOL VCBP_16K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 16K; END IF; IF NOT EXISTS(SELECT * FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'VCTS_16k') THEN CREATE LARGE TABLESPACE VCTS_16k PAGESIZE 16K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 12.67 PREFETCHSIZE 32 TRANSFERRATE 0.18 BUFFERPOOL VCBP_16K; END IF; GRANT USE OF TABLESPACE VCTS_16K TO USER <vCenter user> WITH GRANT OPTION;
LARGE TABLESPACE VCTS_32k 32768	IF NOT EXISTS(SELECT * FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'VCBP_32K') THEN CREATE BUFFERPOOL VCBP_32K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32K; END IF; IF NOT EXISTS(SELECT * FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'VCTS_32k') THEN CREATE LARGE TABLESPACE VCTS_32k PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 12.67 PREFETCHSIZE 32 TRANSFERRATE 0.18 BUFFERPOOL VCBP_32K; END IF; GRANT USE OF TABLESPACE VCTS_32K TO USER <vCenter user> WITH GRANT OPTION;
SYSTEM TEMPORARY VCTEMPTS_8k 8192	IF NOT EXISTS(SELECT * FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'VCBP_8K') THEN CREATE BUFFERPOOL VCBP_8K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 8K; END IF; IF NOT EXISTS(SELECT * FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'VCTEMPTS_8k') THEN CREATE SYSTEM TEMPORARY TABLESPACE VCTEMPTS_8K PAGESIZE 8K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL VCBP_8K; END IF; GRANT USE OF TABLESPACE VCTEMPTS_8k TO USER <vCenter user> WITH GRANT OPTION;
SYSTEM TEMPORARY VCTEMPTS_16k 16384	IF NOT EXISTS(SELECT * FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'VCBP_16K') THEN CREATE BUFFERPOOL VCBP_16K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 16K; END IF; IF NOT EXISTS(SELECT * FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'VCTEMPTS_16k') THEN CREATE SYSTEM TEMPORARY TABLESPACE VCTEMPTS_16K PAGESIZE 16K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL VCBP_16K; END IF; GRANT USE OF TABLESPACE VCTEMPTS_16k TO USER <vCenter user> WITH GRANT OPTION;
SYSTEM TEMPORARY VCTEMPTS_32k 32768	IF NOT EXISTS(SELECT * FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'VCBP_32K') THEN CREATE BUFFERPOOL VCBP_32K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32K; END IF; IF NOT EXISTS(SELECT * FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'VCTEMPTS_32k') THEN CREATE SYSTEM TEMPORARY TABLESPACE VCTEMPTS_32K PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL VCBP_32K; END IF; GRANT USE OF TABLESPACE VCTEMPTS_32k TO USER <vCenter user> WITH GRANT OPTION;

Support for the Pre-Upgrade Checker

To get support for the Pre-Upgrade Checker, use your existing vCenter Server support contract to file a support request with VMware Global Support Services (GSS) at https://www.vmware.com/support/contacts/file-sr.html.

Open Source Components for the Pre-Upgrade Checker

The source files, copyright statements, and licenses applicable to the open source software components distributed in the Pre-Upgrade Checker (VMware DBCheckerTool) are available at:
https://download3.vmware.com/software/open-source/dbchecker_10/jtds-1.2.2-src.tar.gz

Attachments

DBPreUpgradeChecker.zip get_app

Feedback

thumb_up Yes

thumb_down No