vCenter upgrade database question

[davidmynatt]

I'm planning an upgrade from VirtualCenter 2.5 to vCenter 4.1. I'm currently using the SQL Express database with 2.5, but plan to move to SQL 2008 for 4.1. I'll be switching servers, since 4.1 requires a 64-bit OS.

How should I handle the database for this upgrade? It seems I have two options, but the vSphere Upgrade Guide doesn't seem to offer a recommendation.

Option 1: Upgrade from SQL Express to SQL 2008 on the 2.5 server, then migrate to the 4.1 server.

Option 2: Migrate from the 2.5 server to the 4.1 server using the data migration tool, maintaining the SQL Express database. Then, upgrade the database from SQL Express to SQL 2008 on the 4.1 server.

Does anyone have any suggestions or know of any documentation on this?

Thanks

[vmroyale]

Hello and welcome to the forums.

How should I handle the database for this upgrade? It seems I have two options, but the vSphere Upgrade Guide doesn't seem to offer a recommendation.

Option 1: Upgrade from SQL Express to SQL 2008 on the 2.5 server, then migrate to the 4.1 server.

Option 2: Migrate from the 2.5 server to the 4.1 server using the data migration tool, maintaining the SQL Express database. Then, upgrade the database from SQL Express to SQL 2008 on the 4.1 server.

Option 1 will leave you in an unsupported (or worse) state, as SQL 2008 and VC 2.5 are not listed as compatible in the vSphere Compatibility Matrixes. Option 2 might be the best approach, or at least the approach that doesn't change too many things at once.

Does anyone have any suggestions or know of any documentation on this?

VMware kb 7960893 details how to move the database.

Good Luck!

[mdgunther]

I'm basically doing the same thing (though I'm at 4.0) and have been digging around trying to find the best answer. Let me present a third option I'm considering trying.

1. Use the Data Migration Tool to migrate (following the upgrade guide instructions).

2. Detach the database from SQL Express (with Management Studio), copy the files to the new server, and attach the database in the new SQL 2008 install.

3. Run the Data Migration Tool installer (still following the upgrade guide) and instead of selecting "Install SQL Server 2005 Express", I would choose "Use an existing supported database".

One unknown here is dealing with the database upgrade (2005 vs. 2008). I found this at Microsoft's site:

I do not know if the upgrade from 2005 Express to 2008 will do database upgrades automatically. Having SQL 2008 installed already would limit downtime.

The other unknown is what does the Data Migration Tool do with the SQL Express database it copied if you select an existing database.

I plan to start working on my upgrade in 2-3 weeks. Since I'm using a fresh Windows VM for this, I can find out what doesn't work without hurting production.

[davidmynatt]

My supervisor wants to:

1. Upgrade the 2.5 server to SQL 2005.

2. Install SQL 2008 on the new 4.1 server, then move the database over to the new server.

3. Migrate vCenter over to the new server.

Any thoughts on this?

mdgunther, I don't know whether or not it's possible to detach a db from SQL Express 2005 and attach it to SQL 2008. That would be the easiest method, if it works. Unfortunately, I don't think we'll be able to wait to find out.

Thanks for all the input!

[mdgunther]

I admit I have no experience with SQL Server outside of the Express version that VMware bundles. Because of that, I've been searching all over the place to get answers.

What I can tell you for sure is that Express is just a crippled version of the full product limited to 4GB for the database and 1GB of memory. This is why VMware recommends the limit of 5 hosts and 50 VMs because the database won't keep up otherwise. I have been unable to find any information that the format of the database is any different. The upgrade from 2005 to 2008 does change the database, so you can't go back. (This is why you should back it up first.)

If you want to test migrating the database, do a backup/restore operation instead of a detach/attach. Detaching pulls the database out, while backing up does not. So you can keep running the old SQL server while testing the import of the new one. When you go to migrate for real, you can overwrite the test database.

As for your supervisor's suggestion, I think upgrading to SQL 2005 on the 2.5 server won't do anything for you but cause downtime while the service is stopped (and maybe for a reboot on top of that). Steps 2 & 3 sound exactly like what I'm planning out right now.

[mdgunther]

I just did my upgrade yesterday and the database part of it was easy. The attach/detach went off without a hitch, there were no compatibility issues. The Migration Tool worked well too. While it had the backed up database (and I think it even upgraded it too) ready to go, choosing an existing DSN prevented it from restoring it.

I did make sure the permissions on the database were correct so vCenter could upgrade and use it. This is done throught the SQL Server Management Studio. The defaults may work for you.

One problem I did have was getting the 32 bit DSN set up for the Upgrade Manager database. Be sure to run C:\windows\syswow64\odbcad32.exe to create a 32 bit DSN just like you did for the vCenter database. (Don't be alarmed, it can't show existing 64 bit DSNs.) I did this during the Migration Tool install for Update Manager because I didn't get the 32 bit DSN created properly the first time. The installer could not find the 32 bit DSN after I created it (prompting several attempts to get it right) via the dropdown box. With nothing left to try, I typed the name of the DSN in the box and it took it.

Another thing to look out for is that SQL Agent needs to be running for the installer to create the jobs needed to maintain the data. It warns you when it needs it, but I initially thought it was a DSN error. Agent is set to Manual by default in the Services control panel. I switched it to Automatic and started it and the install proceeded.

When using the Migration tool, it will ask you if you want to copy the downloaded patches for Update Manager. Skip that. I had 6GB worth of unneeded patches. Update Manager will redownload anything it needs.

Everything is good now except for one thing. 4.1 has made a mess of HA for me, it no longer configures. But that is out of the scope of this thread.