Migrating vCenter 5.1 SQL Express to SQL 2008 R2 server using backup and restore method

I am using vSphere 5.1 and SQL Server 2008 R2, maybe soon I will work on 5.5 and SQL 2012 scenario and will update.
As mentioned I have a vCenter 5.1 server and a standalone SQL Server 2008 R2 in lab with 2 ESXi Hosts, these hosts are part of a cluster and connected to a distributed switch.

We will start with preparing SQL Server 2008 R2.
1. Configure Ports:
Using SQL Server configuration manager, we will change the dynamic port to static. Remove any entry present in IP1 “TCP Dynamic Ports” and enter 1433 for “TCP Port”. Same changes in IPAll (scroll down to end), after the changes are done we need to restart the SQL service.

Verify the changes by checking the registry key as shown below.
2 .Create database as RSA and VIM_VCDB
3. Export Users from SQL Express using KB918992_transfer-logins script (search the KB for more information)
Open the file in Management studio and execute. It will create 2 stored procedures of which we will be using “sp_help_revlogin”
Drag the “SP_Help_revlogin” stored procedure to a new Query window and execute, this will result in some queries. Copy the lines representing RSA_USER and RSA_DBA as we need to execute these lines on SQL 2008 R2.
Once executed, you will find 2 new users created Security/Logins

4. Create a user vCenterDB with SQL server authentication.

5. Backup database
On vCenter Server stop the vmware related services and using management studio connect to local SQL Express instance to backup the two databases i.e., RSA and VIM_VCDB.
Make sure you select “Overwrite all existing backup sets” (make a copy of database file to be safe after backup is complete)
Copy the backup files to SQL 2008 R2 server.
6. Create ODBC connection on vCenter Server

7. Change ODBC entry in registry.
We need to edit 2 entries as shown below.
For String value “1” enter the ODBC Name that we created in previous step.
for String value “2” enter the user which is used to connect to database i.e., vcenterdb.
This is the user that we created on SQL 2008 R2 server.
Change the Single Sign On (SSO) service dependency by removing the value from “DependOnService” (double click to modify)

8. Change Password for ODBC connection
From registry we changed the ODBC Name and the Username that will be used by vCenter server, we also need to provide the password, which can be done using command prompt.
Open cmd with Administrative privilege (Right click and Run As Administrator).
C:\Program files\VMware\Infrastructure\VirtualCenter Server\vpxd.exe –p

9. Restore database
On SQl Server 2008 R2, restore the backup to respective databases. Under options, select “Overwrite the existing database (with replace)” option.
10. Update the SSO database configuration
using cmd with administrative privilage, change directory to
c:\Program Files\VMware\Infrastructure\SSOServer\utils

ssocli configure-riat -a configure-db –database-host T-SQL2.vhomelab.com –database-port 1433 -m <master password>
<master password> is the password for admin@system-domain that you specified at the time of Single Sign On installation.
Change config.properties file. specify ServerName, Instance, and PortNumber and db.Host
Start the Single Sign On service on vCenter Server
11. Change the dependency of virtualcenter server service
Tthe same way we did for SSO. This time search for vpxd key and modify the DependOnService to remove MSSQL$VIM_SQLEXP entry.

12. Map vCenterDB
The vCenterDB user that we  created in SQL and which is also being used to connect to Database using vCenterSQL ODBC need some permissions on VIM_VCDB database.
In the properties of vCenterDB user select User Mapping and Select VIM_VCDB as shown below.
2nd, add vCenterDB User to db_owner roles in VIM_VCDB database security section.
Start the VirtualCenter Server service on vCenter server.
In case the service is not starting up and throws some error check vpxd-x log at location
C:\ProgramData\VMware\VMware VirtualCenter\Logs
If the steps are followed in order as mentioned, things should work without issues.

1. The performance chart is not showing up
Error: Perf Charts service experienced an internal error.
Message: Report application initialization is not completed successfully.
2. Storage Views Tab
Error The storage service is not initialized.  Please try again later
Solution: Open and midify vcdb.properties file located at C:\ProgramData\VMware\VMware VirtualCenter
Change the line url = jdbc:sqlserver:…… to match below
url = jdbc:sqlserver://T-SQL2:1433;instanceName=vCenterSQL;databaseName=VIM_VCDB

Restart VMware virtualcenter management service, re-open VIClient, still if it does not shows up Restart the service a couple of times with few minutes interval.
In my case it did not work in 1st restart of service, had to restart it 3 times.


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 )

Facebook photo

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

Connecting to %s