Unsupported Database after vCenter upgrades

This week I’ve been working on an upgrade project at a small customer. Pretty routine stuff, take a vSphere 5.0 environment and upgrade to vSphere 5.1, having SRM added another step to the upgrade process.  One thing I highly recommend is that you use the vCenter 5.1 Pre-Install Check fling that Alan Renouf has published on the VMware Labs web site. I did run into a supportability issue around SQL Express.

The environment had been continuously upgraded from Virtual Infrastructure 3.0 days to present and had always been small.  The database was on SQL Express which had been installed by the vCenter installer.  It looks like the last rebuild of vCenter was 4.1, with the mandatory move to a 64bit operating system. This has left the client with SQL Express 2005 as their database server even though it is not supported past vCenter 4.1 Update 3. It is a shame that neither the vCenter installer nor the Pre-Check directly flag this as a support problem, both could have used the SQL Server driver version on the DSN to detect an out of date database engine.  I suspect that a lot of small environments are running way out of date SQL Express as a result of the upgrades. The release notes for vCenter definitely mention that the database configuration is not supported, but the installer for vCenter doesn’t stay anything about producing an unsupported configuration.

I chose to get to a supported configuration by upgrading vCenter first, then migrating the databases to a SQL Express 2008 R2 instance. The SSO install wants a database so I let it install SQL Express 2008 R2, this give me the database engine I will use in the end.  The upgrades all ran nicely in place, the pre-check script let me know that SSO was going to fail to add the AD domain due to retired domain controllers.  Naturally I used William Lam’s blog post to add the AD domain using a command line.

Once the vSphere upgrades were done the migration from SQL 2005 to SQL 2008 is fairly simple:

  • Install SQL Management Studio Express 2008 R2
  • Stop all VMware services
  • Backup and detach databases from SQL 2005
  • Move database files to the correct location for SQL 2008
  • Attach databases to SQL 2008
  • Delete and recreate the ODBC DSNs using the newer SQL Native Client
  • Modify vcdb.properties file to point to the new SQL instance name
  • Start VMware services
  • Ensure plug ins like VUM load, remember that this is a slow loading plugin
  • Confirm that Overview Performance charts and Storage Views work
  • Change any service dependencies from the old SQL instance to the SQL 2008 instance
  • Restart your vCenter and make sure everything comes back cleanly

There was a major setup issue with SRM when I arrived (don’t ask) that made me leave SRM using the SQL 2005 Express server. I plan to get back and rebuild SRM to correct the issue and get the SRM database onto SQL Express 2008.

Once all of the databases are off the SQL2005 instance it should be uninstalled to reduce RAM usage on the vCenter host. I had to reboot the vCenter host between changing the service dependencies and uninstalling SQL 2005 as otherwise the uninstall wanted to stop the vCenter services.

One concern I had was the database rollup jobs that keep the performance data from making the database grow huge by averaging and removing statistics for older data. When you use real SQL Server the rollups are run by the SQL Agent service but with SQL Express 2005 there is no SQL Agent so the vCenter service runs it’s own rollups. The wrinkle is that SQL Express 2008 does have a SQL Agent so I was concerned that vCenter wouldn’t do the rollups. When these rollups don’t run he database gets huge very fast. The good news is that after a couple of days the databases hadn’t grown so the rollups must be working.