Vcenter upgrade 5.1 - 5.5 -SQL jobs issue

 Issue with an upgrade failing  to install from 5.1u2 to 5.5u2e,  with SQL Database error.

Note*
Always do a SQL backup of the database to save your self from snapshots!!!

however when you have updated the SSO , webclient, Inventory service ,  Im reluctant to go back to a single snapshot at the start of the install. These were precuationary on the Vcenter The Issue was on the SQL Server installed on another machine.

 


The Problem 


Recieved this error when you begin the upgrade process for vcenter to 5.5, figgered it mite be autogrow on the sql server but was not.


recieved the "Exception Thrown while executing SQL script"

imediately after it starts to upgrade the database.





 My error in the log was like this.
 
 
I Used this VMware Article to fix it.

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004382




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

Details

VirtualCenter 2.5, vCenter Server 4.x and vCenter Server 5.x use SQL Server Agent to run these SQL agent jobs to manage historical data:
  • Past Day stats rollup
  • Past Week stats rollup
  • Past Month stats rollup
  • Process Performance Data myDB
You may need to set up or recreate SQL agent jobs to manage historical data manually under these circumstances:
  • An upgrade to VirtualCenter 2.5, vCenter Server 4.x or vCenter Server 5.x 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.x is reinstalled and database restored from backup.
  • When you try to access performance data for a period more than the last 24 hours, you see 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.
Note: For more information on SQL agent jobs for VMware vCenter Server 5.x, see Verifying jobs and stored procedures installed in vCenter Server 5.0 (2006097) and Verifying jobs and stored procedures installed in vCenter Server 5.1, 5.5 and 6.0 (2033096)

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 Express or vPostgres. SQL Server Agent is not available in Microsoft SQL Server Express. Historical data is managed by the VirtualCenter Server service in this case. In addition, the DSN account must have the correct permissions to run the rollup jobs. If the account does not have the correct permissions, the performance data is not collected. This can be seen in the logs for the rollup jobs.
Note: Ensure to take a backup of the vCenter Server database before proceeding.

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.

    Note: Ensure the SQL Server Agent is running. If it is not running, right-click the SQL Server Agent and select Start. Determine if the issue is still occurring.

  3. Click the Jobs folder. You see a list of all jobs available on your server.

    These jobs are related to VirtualCenter 2.5 and vCenter Server 4.x and vCenter Server 5.x:

    • Past Day Stats Rollup
    • Past Week Stats Rollup
    • Past Month Stats Rollup
    • Process Performance Data myDB
  4. Right-click any of these jobs, if they exist, and select Delete.
  5. Click OK to confirm the deletion.
  6. Close SQL Management Studio when these 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 vCenter Server (1003052) to ensure that you have the correct permissions.

  2. Click File > Open > File.
  3. Navigate to the location of the jobs:

    Notes:

    • The drive letters may change depending on where you install VirtualCenter:

      • VirtualCenter 2.5: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v3-to-v4\T-SQL\
      • VirtualCenter 2.5, Update 4: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v4-to-v5\T-SQL\
      • vCenter Server 4.x: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql
      • vCenter Server 5.x: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql

    • You may need to copy the job creation sql files from the vCenter Server to the server on which the Management Studio/SQL Plus is being run.
  4. Open job_schedule1_mssql.sql.
  5. Ensure that the VirtualCenter Database (for example, VCDB ) is selected and that Master is not selected.
  6. Right-click the VirtualCenter Database and select New Query.
  7. Copy the query outlined in job_schedule1_mssql.sql to the New Query pane in SQL Management studio, then click Execute Query.
  8. Repeat steps 1 to 7 for:
  9. Right-click the Jobs folder and click Refresh.

    Note: If there is no option to view the jobs folder in order to refresh, log out and then log in again to SQL Management Studio as SA or Administrator to refresh the jobs.

  10. After all three jobs are created, navigate to SQL Server Agent > Jobs in the Object Explorer.
  11. Right-click Past Day stats rollup, click Properties. Ensure the owner of the job is the same database login used by VirtualCenter to connect to the database.
  12. Right-click Past Month stats rollup, click Properties. Ensure the owner of the job is the same database login used by VirtualCenter to connect to the database.
  13. Right-click Past Week stats rollup, click Properties. Ensure the owner of the job is the same database login used by VirtualCenter to connect to the database.



Check your DB Jobs are correct here


http://kb.vmware.com/selfservice/search.do?cmd=displayKC&docType=kc&docTypeID=DT_KB_1_1&externalId=2033096

Purpose

This article provides a complete list of jobs and stored procedures in vCenter Server 5.1, 5.5 and 6.0.
vCenter Server offloads many data maintenance tasks to the database by installing stored procedures and jobs to invoke the stored procedures. For example, vCenter Server includes procedures to move data between tables or delete old data.

Notes:

Resolution

Verifying the jobs installed in vCenter Server 5.1, 5.5 and 6.0

To check the jobs installed in vCenter Server 5.1, 5.5 and 6.0 using MS SQL:
  1. Launch SQL Server Management Studio.
  2. Navigate to SQL Server Agent.

    Note: SQL Express does not have a SQL Server Agent so it does not have the jobs listed. Only the full edition of SQL Server has the agent and jobs.

  3. Ensure that the jobs listed in this table are installed:

    Note: For managing an Oracle vCenter Server database, you can use Oracle SQL Developer and for managing a DB2 vCenter Server database, you can use DB2 Control Center.

    Rollup Job Corresponding File
    Event Task Cleanup myDB job_cleanup_events_DB.sql
    Past Day stats rollup myDB job_schedule1_DB.sql
    Past Month stats rollup myDB job_schedule3_DB.sql
    Past Week stats rollup myDB job_schedule2_DB.sql
    Process Performance Data myDB job_dbm_performance_data_DB.sql
    Property Bulletin Daily Update myDB
    Note: This job only applies to vCenter Server 5.x    		 job_property_bulletin_DB.sql
    Topn past day myDB job_topn_past_day_DB.sql
    Topn past month myDB job_topn_past_month_DB.sql
    Topn past week myDB job_topn_past_week_DB.sql
    Topn past year myDB job_topn_past_year_DB.sql

    where DB is db2, mssql, or oracle.

    Note: Ensure that myDB references the vCenter Server database and not the master or some other database. If these jobs reference any other database, you must delete and recreate the jobs. 

Verifying the stored procedures installed in vCenter Server 5.1, 5.5 and 6.0

To check the stored procedures installed in vCenter Server 5.1, 5.5 and 6.0 using MS SQL:
  1. Navigate to vCenter DB > Programmability > Stored Procedures.
  2. Ensure that the stored procedures listed in this table are installed:

    Stored Procedure Corresponding File
    calc_topn1_proc
    calc_topn1_proc_DB.sql
    calc_topn2_proc
    calc_topn2_proc_DB.sql
    calc_topn3_proc
    calc_topn3_proc_DB.sql
    calc_topn4_proc
    calc_topn4_proc_DB.sql
    cleanup_events_tasks_proc
    cleanup_events_DB.sql
    clear_topn1_proc
    clear_topn1_proc_DB.sql
    clear_topn2_proc
    clear_topn2_proc_DB.sql
    clear_topn3_proc
    clear_topn3_proc_DB.sql
    clear_topn4_proc
    clear_topn4_proc_DB.sql
    delete_stats_proc
    delete_stats_proc_DB.sql
    insert_stats_proc
    insert_stats_proc_DB.sql
    l_purge_stat2_proc
    l_purge_stat2_proc_DB.sql
    l_purge_stat3_proc
    l_purge_stat3_proc_DB.sql
    l_stats_rollup1_proc
    l_stats_rollup1_proc_DB.sql
    l_stats_rollup2_proc
    l_stats_rollup2_proc_DB.sql
    l_stats_rollup3_proc
    l_stats_rollup3_proc_DB.sql
    load_stats_proc
    load_stats_proc_DB.sql
    load_usage_stats_proc
    load_usage_stats_proc_DB.sql
    process_license_snapshot_proc
    process_license_snapshot_DB.sql
    process_performance_data_proc
    process_performance_data_DB.sql
    purge_stat2_proc
    purge_stat2_proc_DB.sql
    purge_stat3_proc
    purge_stat3_proc_DB.sql
    purge_usage_stat_proc
    purge_usage_stats_proc_DB.sql
    rule_topn1_proc
    rule_topn1_proc_DB.sql
    rule_topn2_proc
    rule_topn2_proc_DB.sql
    rule_topn3_proc
    rule_topn3_proc_DB.sql
    rule_topn4_proc
    rule_topn4_proc_DB.sql
    stats_rollup1_proc
    stats_rollup1_proc_DB.sql
    stats_rollup2_proc
    stats_rollup2_proc_DB.sql
    stats_rollup3_proc
    stats_rollup3_proc_DB.sql
    upsert_last_event_proc
    upsert_last_event_proc_DB.sql
where DB is db2, mssql, or oracle.  
If any of these jobs or stored procedures are missing, you must install them by running the corresponding .sql file on the vCenter Server database using a database management tool such as SQL Management Studio. For more information on running these .sql files, see section Adding the SQL Server Agent Jobs in Updating rollup jobs after the error: Performance data is currently not available for this entity (1004382).

Note: The upsert_last_event_proc procedure is not required for the Oracle database.

All SQL scripts are located in the vCenter Server installation folder:
  • vCenter Server 5.1 and 5.5: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql.  
  • vCenter Server 6.0: C:\Program Files\VMware\vCenter Server\vpxd\sql
For more information on commonly used vCenter Server installation paths, see Common vCenter Server and vSphere Client Windows paths (1028185).

Ensure that the vCenter Server database is the target before executing the SQL file.

  • Product(s):
    VMware vCenter Server

  • Product Version(s):
    VMware vCenter Server 5.1.x
    VMware vCenter Server 5.5.x
    VMware vCenter Server 6.0.x

Comments

Post a Comment

Popular Posts