vSphere 4.1 Performance reporting fails after upgrade from ESX 3.5 or ESX 4.0

Overview

Again, This blog is used as (1) a central reference point to obtain specific information on issues, etc..that I will refer to posted on other sites, (2) Discovery information I generate from Virtualiztion activities, and (3) Architecture and related information around virtualization technologies:

With that said, below is another issue I found after upgrading from vSphere 3.5 to vSphere 4.1. As I have stated before, it would have been easier to just create a new "fresh" install and database.

NOTE: After this is done, you must start and stop the VMWare vCenter Server service, or restart the vCenter server completely for the update change to synchronize with the database updates.

Updating rollup jobs after the error: Performance data is currently not available for this entity

Details

VirtualCenter 2.5 and vCenter Server 4.0 use SQL Server Agent to run the following SQL Agent jobs to manage historical data:

• Past Day stats rollup
• Past Week stats rollup
• Past Month stats rollup

You may need to setup or recreate SQL agent jobs to manage historical data manually under the following circumstances:

• An upgrade to VirtualCenter 2.5 or vCenter Server 4.0 fails to add these jobs.
• The database is upgraded from SQL Express to SQL Standard or Enterprise.
• The database is moved to another SQL server.
• VirtualCenter 2.5 or vCenter Server 4.0 is reinstalled and database restored from backup.
• You try to access performance data for a period other than the last 24 hours and you are presented the message:
  
  Performance data is currently not available for this entity
   
• Windows Event Viewer on vCenter server shows an entry similar to:
  EventTypeId: com.vmware.vc.stats.StatsInsertErrorEvent
  FullFormattedMessage: Stats insertion failed for entity esxhost.domain.com due to ODBC error.

Solution

Note: This is a two part solution, read and understand all of the steps before proceeding. After the jobs are created and enabled properly, they may take a significant amount of time to run. This article does not apply to installations using Microsoft SQL Server 2005 Express. SQL Server Agent is not available in Microsoft SQL Server 2005 Express. Historic data is managed by the VirtualCenter Server service in this case. 

Verifying the existence of jobs and removing them

For the scripts to install correctly, you must remove any existing Past _____ stats rollup jobs. This section verifies the existence of jobs and removes them if present. 

To verify the existence of jobs and remove them:

1. Open SQL Management Studio using a Sysadmin account (typically SA or Administrator).
2. Expand the Server and SQL Server Agent.
3. Click the Jobs folder. You see a list of all jobs available on your server.
   
   The following jobs are related to VirtualCenter 2.5 and vCenter Server 4.0:
4. • Past Day Stats Rollup
   • Past Week Stats Rollup
   • Past Month Stats Rollup
      
5. Right-click on any of those jobs if they exist and select Delete.
6. Click OK to confirm the deletion.
7. Close SQL Management Studio when those jobs are removed.

Adding the SQL Server Agent Jobs

To add a SQL Server Agent Job:

1. Open SQL Management Studio using the database login used by VirtualCenter to connect to the database (typically not SA or Administrator). See Administrative Credentials are required for Oracle and SQL Server databases when installing or upgrading Virtual Center (1003052) to ensure you have the correct permissions.
2. Click File > Open > File.
3. Navigate to the location of the jobs:
   
   Note: The driver letters may change depending on where you install VirtualCenter.
   • For VirtualCenter 2.5, go to C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v3-to-v4\T-SQL\
   • For VirtualCenter 2.5 Update 4, go to C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v4-to-v5\T-SQL\
   • For vCenter Server 4.0, go to C:\Program Files\VMware\Infrastructure\VirtualCenter Server\
      
4. Open job_schedule1_mssql.sql.
5. Ensure that the VirtualCenter Database is selected and that Master is not selected.
6. Click Execute Query.
7. Follow Steps 1 to 6 for job_schedule2_mssql.sql and job_schedule3_mssql.sql.
8. After all three jobs are created, navigate to SQL Server Agent > Jobs in the Object Explorer.
9. Right-click Past Day stats rollup, then click Properties. Ensure that the owner of the job is the same user as the owner of the database.
10. Right-click Past Month stats rollup, then click Properties. Ensure that the owner of the job is the same user as the owner of the database.
11. Right-click Past Week stats rollup, then click Properties. Ensure that the owner of the job is the same user as the owner of the database.  

Oracle

To ensure that the scripts are upgraded properly:

1. Open SQL Plus.
2. Click File > Open > File.
3. Navigate to the location of the jobs and copy the contents of job_schedule1_oracle.sql:
   

   • For VirtualCenter 2.5, go to C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v3-to-v4\PL-SQL\
   • For vCenter Server 4.0, go to C:\Program Files\VMware\Infrastructure\VirtualCenter Server\
      
4. Open an sqlplus session with a user account that has access to the Oracle tables, jobs, and schedules related to the vCenter Server.
5. Execute the job_schedule1_oracle.sql script.
   
   Note: Contact your Oracle database administrator or see Oracle support documentation for information on how to execute Oracle scripts.
    
6. Repeat steps 1-6 for job_schedule2_oracle.sql and job_schedule3_oracle.sql.
7. After all three jobs are created, ensure that the global Oracle setting JOB_QUEUE_PROCESSES is not set to 0 as this disables all job processing. 

Additional Information

If you encounter issues after upgrading to vCenter Server 4.0, see Past Week stats rollupvc40 SQL Server job fails to complete after upgrading to vCenter 4.0 (1014209).