Move vCenter SQL Express database to stand alone SQL

[henrikp80]

Hi

Im trying to move vSphere 5.1 U1 vCenter database from SQL 2008 R2 Express (Windows 2008 R2) to a stand alone SQL 2012 Standard (Windows 2012) server.

I have moved vCenter datbases several times before but now it wont work.

This is how I do it:

• Shutdown vCenter services
• Backup vCenter database
• Create an empty database on the new SQL server
• Do a restore from the earlier backup file, with override setting
• Change the ODBC (x64) connection to the new server.
•     Express has Intergrated Windows aut. so I change to SQL aut.
• ODBC Test - Connection successfully
• Trying to start vCenter. Not so very succesfully. Something about error 2.

Checking System eventviwer and it's very red.

"The VMware VirtualCenter Server service terminated with service-specific error The system cannot find the file specified.."

Checking SQL logs. Finding this error:

"Login failed for user ''.Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only."

Trying to configure connection in the registry (x86):

[HKEY_LOCAL_MACHINE\SOFTWARE\VMware, Inc.\VMware VirtualCenter\DB]

"5"="50"

"4"="SQL Server Native Client 11.0"

"1"="VMware vCenter"

"2"="sqlvcsvcs"

"3"="password"

Again, trying to start vCenter. Not so very succesfully. Something about error 2.

Checking System eventviwer and it's very red.

Checking SQL logs. Finding this error:

"Login failed for user 'sqlvcsvcs'. Reason: Password did not match that for the login provided."

Changing password and trying to connect. Now I dont even get any changes in the SQL-log. Neither successfully or failed.

Have also tried...:

• Change vcdb.properties >> Failure
• Connect to database using the SQL account >> Success
• Trying to connect to Express database using SQL account >> Failure. The same errors as above.
• Googled as hell and find some tips..but...Dont have ISS Admin, dont have "World Wide Web Publishing..." etc

Does anyone have any clue how to solve this, it starting to freak me out 

I have the SRM and the VUM databases on the SQL stand alone server so I know it should work.

[EDIT]

The SQL Express is installed on the vCenter server, not member in a domain. The stand alone SQL is a domain server. It's a HP VS3 solution.

Yep, I use fully FQDN name 

[Eternal_Snow]

You can try to:

1 Leave username and password empty in ODBC.

2 Change vCenter Service account to a domain account (it should be in the vCenter local admin group for easy).

3 Assign this account to be the db owner of that database in your new database server.

[marcelo_soares]

You need to change the hash password stored on the registry (is not clean text). Use instructions on KB http://kb.vmware.com/kb/1017688

[rhaslund]

Here is a link to a great blog that has a post series on migrating from SQL Express to full SQL server:

Migrating vCenter Database Express to SQL 2008 R2 Part4 | vm-blog.info

[henrikp80]

It ended up with an un/install of vCenter. Probably the best way to move databases for vSphere 5.1.

Backup/Restore - Uninstall/Install approx. 30 min.

Good luck!

[rhaslund]

Good that you found a solution, even though I would rather call it a workaround

Please remember to mark the question as answered.