Before upgrading a vCenter Server Appliance or migrating a vCenter Server on Windows that uses an external Microsoft SQL Server database, you must determine the size of the existing database. Based on the size of the existing database, you can calculate the minimum storage size for the new appliance so that the embedded PostgreSQL database can successfully assume the data from the old database with enough free disk space after the upgrade.

You run scripts to determine the Microsoft SQL Server core table size, the events and tasks table size, and the statistics table size. The Microsoft SQL Server core table corresponds to the database (/storage/db) partition of the PostgreSQL database. The Microsoft SQL Server events and tasks and statistics tables correspond to the statistics, events, alarms, and tasks (/storage/seat) partition of the PostgreSQL database.

During the upgrade of the appliance, you must select a storage size for the new appliance that is at least twice the size of the Microsoft SQL Server tables size.

Prerequisites

You must have the vCenter Server database login credentials.

Procedure

  1. Log in to a SQL Management Studio session with the vCenter Server database user.
  2. Determine the core table size by running the following script. 
    SELECT SUM(p.used_page_count * 8)/1024 AS disk_size
  FROM sys.dm_db_partition_stats p
  JOIN sys.objects o
    ON o.object_id = p.object_id
 WHERE o.type_desc = 'USER_TABLE'
   AND o.is_ms_shipped = 0 AND UPPER(o.name) NOT LIKE 'VPX_HIST_STAT%'
   AND UPPER(o.name) NOT LIKE 'VPX_SAMPLE_TIME%'
   AND UPPER(o.name) NOT LIKE 'VPX_TOPN%'
   AND UPPER(o.name) NOT LIKE 'VPX_TASK%'
   AND UPPER(o.name) NOT LIKE 'VPX_EVENT%'
   AND UPPER(o.name) NOT LIKE 'VPX_SDRS_STATS_VM%'
   AND UPPER(o.name) NOT LIKE 'VPX_SDRS_STATS_DATASTORE%'
   AND UPPER(o.name) NOT LIKE 'VPX_PROPERTY_BULLETIN%';
    The script returns the database storage size in MB.
  3. Determine the events and tasks table size by running the following script. 
    SELECT SUM(p.used_page_count * 8)/1024 AS disk_size
  FROM sys.dm_db_partition_stats p
  JOIN sys.objects o
    ON o.object_id = p.object_id
 WHERE o.type_desc = 'USER_TABLE'
   AND o.is_ms_shipped = 0 AND ( UPPER(o.name) LIKE 'VPX_TASK%'
    OR UPPER(o.name) LIKE 'VPX_EVENT%');
    The script returns the events and tasks storage size in MB.
  4. Determine the statistics table size by running the following script. 
    SELECT SUM(p.used_page_count * 8)/1024 AS disk_size
  FROM sys.dm_db_partition_stats p
  JOIN sys.objects o
    ON o.object_id = p.object_id
 WHERE o.type_desc = 'USER_TABLE'
   AND o.is_ms_shipped = 0
   AND (     UPPER(o.name) LIKE 'VPX_HIST_STAT%'
         OR UPPER(o.name) LIKE 'VPX_SAMPLE_TIME%'
         OR UPPER(o.name) LIKE 'VPX_TOPN%');
    The script returns the statistics storage size in MB.
  5. Calculate the minimum storage size for the new appliance that you are going to deploy during the upgrade.
    1. The size of the database (/storage/db) partition of the embedded PostgreSQL database must be at least twice the size of the Microsoft SQL Server core table returned in Step 2.
    2. The size of the statistics, events, alarms, and tasks (/storage/seat) partition of the embedded PostgreSQL database must be at least twice the sum of the sizes of the Microsoft SQL Server events and tasks and statistics tables returned in Step 3 and Step 4.
    For example, if the Microsoft SQL Server core table is 100 MB, the events and tasks table is 1,000 MB, and the statistics table is 2,000 MB, then the Postgres /storage/db partition must be at least 200 MB and the /storage/seat partition must be at least 6,000 MB.