vCenter Server Database Upgrade Hangs
Recently I was given a project to upgrade a 4.0 ESX environment to 5.1 (actually I have had a couple few of these upgrades recently). For this project the physical vCenter Server would also be migrated to a vCenter Server virtual machine. The upgrade/migration process is fairly straight forward. A new Virtual Machine that would host the vCenter Server components (SSO, Inventory Service, vCenter Server, VUM) was provisioned and Microsoft SQL Server 2008 Standard was installed. The SSO databases were created. Then the SSO and Inventory Services were then installed without issue.
I created a backup of the original vCenter database and for the size of the environment the database was ginormous, a little over 15GB. I thought that was odd but for whatever reason I did not really dig into it much (until this would cause the upgrade to fail – keep reading). The database was detached from the original physical vCenter Server, the db and log files were copied to the new vCenter virtual machine, and the database was then attached to the the new SQL server. The vCenter ODBC connection to the new database was created and tested, all looked fine.
The vCenter Server installation was started and the database upgrade option was selected. The upgrading database part of the process appeared to hang and the transaction log for the vCenter database started to grow. It started to look like it would fill up the disk space on the drive set up to store the databases. After giving it about an hour I went ahead and canceled the upgrade process.
Some quick googling led me to the VMware KB article http://kb.vmware.com/kb/1007453. The VPX_HIST_STAT tables were over 12GB in size! I then noticed that the SQL Agent was not running on the old vCenter Server. The SQL Agent Service had been disabled and because of this the vCenter performance data roll up jobs were not being run. When SQL is installed the SQL Agent service is set to Manual start up by default, this should be changed to Automatic since this service is required for the vCenter database roll up jobs to run.
Due to the size of the tables and time constraints instead of trying to get the roll up jobs to run, I followed the process outlined in VMware KB 1007453 to truncate the VPX_HIST_STAT(1-4) tables and the VPX_SAMPLE_TIME(1-4) tables. Be aware that this process removes all historical performance data. To keep the performance data the roll up jobs could have been run, but because of the size of the tables this probably would have taken a lot of time and I doubt it would have completed successfully.
Once these tables were truncated the upgrade completed without issue. On the new vCenter Server I set the SQL Agent Service to Automatic and after a few days checked the environment to ensure the roll up jobs were being processed (VMware KB article – http://kb.vmware.com/kb/2007388).
Once the vCenter upgrade was completed the upgrading/migrating the host from ESX 4.0 using VUM went without issue.
Checking the roll up jobs is now part of my pre- and post- upgrade process.
(I know, I know it should have been part of the process in the first place)