Upgrading to vCenter 4.1 with bundled SQL Express Edition – database migration fails

My infrastructure uses a bundled SQL Express Edition database because it isn’t hugely complex, and I didn’t want too much dependency on other servers (themselves VMs). I encountered problems upgrading the vCenter database while moving from Windows Server 2003 R2 SP2 x86 to Windows 2008 R2. I was migrating from vSphere 4.0U1 to 4.1. Perhaps this precise combination of versions was the problem, or perhaps it was that my database started life as VirtualCenter 3.5.

The process seems simple enough – unzip and run the Data Migration Tool from the installation media on the source vCenter server, move this folder (now with \data added) to the destination server and launch the install.bat script. However, there seem to be two major snags. The first is that the backup process will fail:

DB logs: HResult 0x2, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server

VMware has a knowledgbase article about this. They claim it’s caused by a misconfiguration of the SQL 2005 Express Edition instance which is pretty rich considering it was set up by their own installer. Make the named pipe change they suggest and it will work. Now unplug this machine from the LAN or disconnect its network adapter in vSphere if it’s a VM – remember you can connect the VI Client directly to the hypervisor which is hosting it.

The destination server needs to be configured with the same hostname as the source. I had then assumed that the restore tool would need to be run after a new install of vCenter 4.1 was placed on the destination server, so I installed vCenter. Then I discovered that the install.bat script in the datamigration folder refuses to run if it detects the product is already present. So naturally I uninstalled it and tried again. Perhaps this is what messed things up, or perhaps it’s because I’m using Windows 2008 R2.

Anyway, the datamigration\install.bat script kicks off the main product installer, supposedly importing all your backed up settings.

According to the vCenter 4.1 Upgrade Guide page 40 item 10 you are supposed to:

Select Install SQL Server 2005 Express instance (for small-scale deployments) and click Next.

Item 16 on the same page states that:

When the vCenter Server installation finishes, click Finish. The data migration tool restores the backed up configuration data.

If you do this you may, like me, discover that it actually doesn’t work and you end up with a completely blank database instance. Consulting datamigration\logs\restore.log I found no reference at all to any database restore.

My workaround

Go to your original vCenter server. Open the Data Sources MMC snap-in. In System DSN you should see an entry like so:

Note down the details, then create the same entry on your destination server (this will create a 64bit DSN). Notice how on page 38 of the Upgrade Guide it specifically states that:

If you use the data migration tool to migrate a SQL Server Express database located on the vCenter Server system to a new system, you do not need to create the 64-bit DSN. The data migration tool creates the DSN as part of the installation process

Apparently sometimes you do need to create the DSN.

On the destination server, copy the file \datamigration\data\vc\vc_upgraded_db and paste it in C:\temp. Rename it to vc_upgraded_db.bak.

Still on the destination server download, install and run the 64bit SQL Management Studio Express. Even if you’ve uninstalled vCenter, the SQL Express Edition instance will be left behind. If there’s not already one from a failed install, create a local database called VIM_VCDB. Restore the backup in C:\temp\vc_upgraded_db.bak over the top, paying attention to select Options -> Overwrite Existing Database and browsing to the target file locations of both the mdf and ldf files – the old database was found in C:\Program Files\Microsoft SQL Server\MSSQL.1\Data but on the 64bit system it’s C:\Program Files (x86)\…

Right-click your database, and select Properties. In Options, make sure your database recovery model is set to Simple. If you don’t do this your transaction log will fill up in a couple of days and the vCenter services will stop. In my case it seemed to have defaulted to Bulk-Logged.

Once that’s done you’ll need to update your newly created DSN to set the default database to VIM_VCDB. Now run datamigration\install.bat once again but this time opt to use an existing database as shown below:

Strangely, you will find that the you cannot use the SYSTEM account for the vCenter services however this can easily be changed in the Services MMC snap-in later.

And that’s it. You should end up with a working install with your data intact. One final tweak is to delay launch of the vCenter services so they don’t fail to start up at boot time.

UPDATE – after wasting a number of hours today with this, I’ve done some more searching and found this VMware KB article which basically just admits that the Data Migration Tool sometimes doesn’t work, and won’t even report errors in the log! When I download something as important as this, I sort of take it for granted that I won’t have to Google “vcenter data management tool does not migrate database” the moment I try using it (can’t believe I didn’t try that).

Advertisements

5 thoughts on “Upgrading to vCenter 4.1 with bundled SQL Express Edition – database migration fails

  1. Matt

    I had a very similar problem where the data migration tool did not migrate my database and started with a blank one. It did create the DSN for me so all I did was take your advice and restore the DB over the blank one and it worked like a charm. Thanks for the tip, great post!

    Reply
  2. dave

    thanks for the tips mate.

    just wondering how you managed to have two hosts with the same name – presumably in the same AD domain? Did you have to remove the old VC computer account before installing the 2008 vc into the domain?

    tia

    dave

    Reply
    1. patters Post author

      I left the old one powered off once I had the database backup off it and the new one will have overwritten the old one’s AD account object once joined to the domain.

      Reply
  3. Pingback: Upgrading to vSphere vCenter 4.1 Experiencies « VirtuallyNil

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s