Moving the VMware vCenter Server 4.x/5.x/6.0.x SQL database

A simple necessary task that isn’t so simple.  So the scenario plays out like this.  Customer is running vCenter, Update Manager and SRM databases on a SQL server that is running Windows 2003 with SQL 2005 – not extremely uncommon.  With the impending end of Windows 2003 looming we decide to migrate to Windows 2008 R2 and SQL 2008 R2.  

For the most part we followed KB 7960893 from VMware.

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

Now this KB will get you close, which is nice.  And what does close mean.  It means that we had databases up and running on the SQL server.  It means that we had ODBC connections that were connected and tested OK.  And then….It means that we couldn’t get the service to start – WTF.

So let’s look at the process and the final resolution.  First off it’s a pretty common task and I was doing this for a customer in their DR site, but it is something that everyone will run into at some time. 

Several steps in the process.

1.       Backup the databases on the old SQL server.  Simple enough go into SQL management studio right click the database and back it up.  Stop all of the VMware services on vCenter and SRM first.
2.       Copy the databases over to the new server – Really easy.
3.       Restore the databases to the new SQL server.
4.       Yeah this next step is a little harder.  Permissions.  We had to set the proper permissions.  The customer wanted to use a different service account and instead of having separate accounts for vCenter, UM, and SRM they wanted to use one account for all of them.  Not a huge deal, so whatever, let’s make it happen.  We used a local account in SQL with SQL server authentication.  We then granted dbo permissions to the vCenter, UM, and SRM databases.  You also have to give it dbo for the msdb database.  OK, all of this sounds pretty straightforward so far.
5.       Edit the vcdb.properties file to change the server name – Really easy.
6.       I didn’t update the SQL server agents just yet, because I didn’t care about performance statistics.
7.       Go to ODBC and ODBC32 on the vCenter server and change them to point to the new server and the correct database using the proper username and password.  Then test it.  BOOM, everything worked.  Start the vCenter and UM services up.  And…It failed.  WTF.

Troubleshooting Time

8.       So to do a little troubleshooting I looked in event viewer, got a whole bunch of nothing. 
9.       So what next?  I decided to start the vCenter service using the executable in standalone mode.  Open an administrative command prompt and the run vcdb –s.  Hmmm.  Interesting error.  Login failure and it was related to a DSN, which is telling me SQL login failure.  But the ODBC connections all tested positive.
10.   So I did a registry search and each of the 3 databases all have a section with a DB entry in it.  In that section I looked in there, and it still had the old entry for the name in there.  In the picture it is entry 2.  So I changed it.  DANG IT, the service still wouldn’t start.
11.   Well the line right below the username in the registry is the password hash for the old username, in the picture it is entry 3.  To change that password hash you have to go back to that administrative command prompt and run the vcdb –p command and then enter the new password twice.  And now.  BOOM the service starts.  WOO HOO.

12.   So now let’s start the Update Manager service.  It fails.  WTF.

13.   This was pretty easy.  I ran the Update Manager Utility and change the database in there and it started up fine after that.  Update Manager Utility is mentioned in KB 2011327, and it is located under Program Files (x86), because it is still a 32bit program, also have to update the ODBC32 as well.  So start the service and it worked.

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






14.   OK so now 2 of the 3 databases are connected and running.  Go to the SRM server and start that service up.  DANG IT, no go.  On to troubleshoot that.

15.   Went through several troubleshooting steps, and actually worked with VMware technical support, but since nothing was really set up in SRM we just did a full uninstall/reinstall.  It wasn’t pretty and if there had been any data on there I would have definitely taken the time to get it working, but…I didn’t so this was the easy way out.

So in a nutshell, it wasn’t as easy as I thought it would be.  But it is done, consulting is never quite as easy as it seems, but I still enjoy it.