Migrating vCenter databases from SQL Express to SQL Server

When installing vCenter you will need a vCenter database, an Update Manager database and with vCenter prior to 5.5 also a SSO database (RSA). With the installation you have the option to point to an (existing) dedicated SQL Server, SQL Server on the same Windows host or use the bundled SQL Express with the installer. Depending on the requirements of the environment a design decision is made to which sort of database setup vCenter will communicate. I prefer to separate the database resources and services from the vCenter server and will often option for a dedicated SQL Server or SQL instance part of SQL mirror/cluster. But sometimes the size of the environment is small enough, and a dedicated SQL license is a constrain at the design/build phase, test/pilot grows into production (don’t!), in these situations SQL Express is `fine’. Fine and supported up to a vCenter inventory of max 5 hosts and or 50 VM’s. No.. your vCenter will not crash immediately after adding a sixth, seventh or whatever hosts but performance is degraded to a point that certain sub processes/services are not running within optimal limits. This can have unforeseen consequences to your environment, and this is not what is wanted in production.

This post will combine the migration of SSO, vCenter and Update Manager databases from SQL Express to SQL Server. Yes you are missing a database, vCenter Orchestrator should be migrated as well. Moving this database should be included when using, use the vCenter Orchestrator Configuration service to point to the destination database server (stop orchestrator server to move.) Unfortunately when using the vCenter simple installation with a bundled SQL Express this combination is not used very often or not at all (a shame).

The proces

Migrating SQL Express from the bundled to a full installation includes the following actions:

• Assess your environment. What database names are used, what users and do you have the correct administration of users and passwords (see the SSO paragraph below for a way to find out the current RSA_DBA and RSA_USER passwords). Decide on whether you want to use the same users, or use new ones. Same goes for ports of destination SQL Server, Authentication mode used here etc. Always check the interoperability of vCenter with the database version. Do they support each other? Use SQL Authentication with the SSO database, vCenter supports Windows authentication (Best Practice) and SQL users.
• Plan for some downtime of SSO, vCenter and Update Manager. As your moving services and databases from one server to another, the vCenter, SSO and Update Manager services must be stopped before and until the data is copied and configuration is changed, remain down. No VM workload is hit and remain running, and host can be managed via the standalone vSphere client during changes. That is, versions before 5.5 and hardware version 10.
• Create back-ups of the to change files, registry keys and such before changing values.
• Migrate the SSO database. This step is only for pre-5.5 versions as SSO in vCenter 5.5 does not use a database.
• Migrate the vCenter database.
• Migrate the Update Manager database. That is when Update Manager is installed on the same SQL Express instance. Normally you would see this combination when a SQL Express Windows vCenter server is used.

Migrating the database itself can be done via a SQL Backup and restore (overwrite), detach-copy-attach, or SQL Server Import/Database Copy Wizard. Prior to the database move the appropriate services must be stopped. When doing the copy in one task, stop the services as described in the following paragraphs. If you use the one go and backup or copy is finished stop the SQL Express (VIM_SQLEXP).

SSO

Not sure what your current RSA_USER/RSA_DBA passwords are. Go to the SSOServer\utils directory in an elevated prompt and run the following command:

ssocli manage-secrets -a listallkeys

Stop the vCenter Single Sign-On service. After the service is stopped backup and restore or copy the database files on the destination SQL Server. For SSO two SQL users are created by the installer, RSA_DBA and RSA_USER. RSA_DBA is only used when doing the installation, not needed for this migration. For operational purposes RSA_USER is used. Add the login/user to the destination SQL Server and set the password / required permissions if these where not saved.

Open an elevated command prompt and go to the SSOServer installation directory\utils directory. For example when installed on D:

Run the following command: 

ssocli configure-riat -a configure-db –database-host newdbhost –database-port newdatabaseport –rsa-user RSA_USER –rsa-user-password password -m master_password

Replace the database hostname, database portnumber 1433 for standard SQL. Add the RSA-USER password and -m master password is the [email protected] password. rsa-user-password works only in combination with rsa-user option.

Open a text editor (also with elevated permissions if required). Open the following two files:

• SSOServer\webapps\ims\web-inf\classes\jndi.properties

Check the values:

• com.rsa.db.type=MSSQL.
• com.rsa.db.instance=RSA. (or whatever your RSA instance dbname is)
• com.rsa.db.msserverinstance= . Leave empty when using the default MSSQLSERVER instance on the destination server.
• com.rsa.db.hostname=destinationSQL server.
• com.rsa.db.port=1433.
• Note: don’t mix up the com.rsa.instanceName. This is not the SQL Instance, but the SSO instance. This should not be changed.
• SSOServer\webapps\lookupservice\WEB-INF\classes\config.properties

Check the values:

• db.url=jdbc:jtds:sqlserver://;serverName=;portNumber=1433;databaseName=RSA. PortNumber has to be added here.
• db.user=RSA_USER
• db.pass=yourpassword
• db.type=mssql
• db.host=destination SQL server fqdn.

Change the lines appropriately to you environment. Save the files.

Start the vCenter Single Sign-On service.

vCenter System

Stop the vCenter services. Include all vCenter services except Update Manager, Orchestartor or SSO. After the service is stopped backup and restore or copy the database files on the destination SQL Server. Add the login/user to the destination SQL Server and set the password / required permissions if these where not saved. The user needs db_owner on the vCenter database and dbo on MSDB system database on the destination server.

Reconfigure the System DSN to use the destination SQL Server. Use the 64-bit ODBC Manager. When using a default installation of MSSQL server, the default MSSQLSERVER instance does not have to be filled in, connection to the SQL server hostname is enough. When using Windows credentials the ODBC is editted in the service account user. Change the default database to the VIM_VCDB or what ever name you gave it. Test the connection to be sure everything works.

Update the ODBC connection in the registry:

• Click Start > Run, type regedit, and click OK. The Registry Editor window opens.
• Navigate to HKEY_LOCAL_MACHINE > SOFTWARE > VMware, Inc > VMware VirtualCenter.
• Modify the key DbInstanceName and remove the current Value data. Do not delete this key.
• Modify the key DbServerType and change the Value data from Bundled (the SQL Express) to Custom.
• Navigate to HKEY_LOCAL_MACHINE > SOFTWARE > VMware, Inc > VMware VirtualCenter > DB.
• Modify Key 4 and change the ODBC driver to the new driver if not changed check this value and leave intact.
• Modify key 2 and add the vCenter Server SQL user, for example vcuser.

The password that is set (at key 3) is needed to be change, open an elevated command promp and run the following command:

<install drive>:\Program Files\VMware\Infrastructure\VirtualCenter Server\vpxd.exe -p

Go to the SQL Server and recreate the SQL Jobs. Launch SQL Server Management Studio on the SQL Server and Navigate to SQL Server Agent. In the Jobs list create the following jobs:

Rollup Job	SQL Job Filename
Event Task Cleanup vCenter Database	job_cleanup_events_mssql.sql
Past Day stats rollup vCenter Database	job_schedule1_mssql.sql
Past Month stats rollup vCenter Database	job_schedule3_mssql.sql
Past Week stats rollup vCenter Database	job_schedule2_mssql.sql
Process Performance Data vCenter Database	job_dbm_performance_data_mssql.sql
Property Bulletin Daily Update vCenter Database	job_property_bulletin_mssql.sql
Topn past day vCenter Database	job_topn_past_day_mssql.sql
Topn past month vCenter Database	job_topn_past_month_mssql.sql
Topn past week vCenter Database	job_topn_past_week_mssql.sql
Topn past year vCenter Database	job_topn_past_year_mssql.sql

Adding the jobs: browse to Program Files\VMware\Infrastructure\VirtualCenter Server\sql or whever your install path is. Open the corresponding SQL file (Open > file). Select the vCenter database. Execute Query. And repeat for others.

When finding existing jobs, check that the corresponding SQL user in jobs is correct. Right click the Job, properties and check the user. And the database name is correct, if not recreate them.

Check the vcdb.properties file for the corresponding values. On Windows 2008R2 and higher this file is located in C:\ProgramData\VMware\VMware VirtualCenter (check show and uncheck hide files and protected files in explorer). When using SQL authentication set the vcdb.properties file value url= ;integratedSecurity\= to False. When using Windows authentication set this value to true. the rest should match sqlserver://<destination server>\\SQL instance;databaseName=\dbname; dbtype=mssql.

It is important that the Windows user has the required permissions on the SQL database and the VMware VirtualCenter Management Webservices and VMware VirtualCenter service is set to run as this user.

Remove the depend on SQL Express in the vCenter services. Open regedit and goto HKLM\System\CurrentControlSet\Services\. Remove the SQL Express service value from the DependOnService Multi string in the vCenter services where set. Do not delete the value, just removing the VIM_SQLEXP from the data is enough. The other dependencies should remain intact. Start the vCenter services. Check the logs if everything starts up correctly.

Update Manager

Last one. Stop the vSphere Update Manager services. After the service is stopped backup and restore or copy the database files on the destination SQL Server. Add the login/user to the destination SQL Server and set the password / required permissions if these where not saved. The user needs db_owner on the Update Manager database and dbo on MSDB system database on the destination server.

Reconfigure the System DSN to use the destination SQL Server. Use the 32-bit ODBC Manager. On Windows 2008R this is located in the C:\Windows\SysWOW64\odbcad32.exe. On Windows 2012 you can select the 32-bit from the apps menu.
When using a default installation of MSSQL server, the default MSSQLSERVER instance does not have to be filled in, connection to the SQL server hostname is enough. When using Windows credentials the ODBC is editted in the service account user. Change the default database to the VIM_UMDB or what ever name you gave it. Test the connection to be sure everything works.

Next go to your installation directory of Update Manager, for example D:\Program Files (x86)\VMware\Infrastructure\Update Manager\, and launch VMwareUpdateManagerUtility.exe (sometimes takes a while to start up). Login with an admin in vCenter (should have the user that installed and registered Update Manager to the vCenter in the user screen.
Click on database settings. Check the DSN name is the same you editted and tested. Type the SQL username and password twice. Click apply.

 

Start the vSphere Update Manager service.

After all changes be sure to check the vCenter Service Status if all is in green here. Some tracking or statistics roll up need the jobs to complete. This can take some time.

– Hopefully this helps you migrating from your SQL Express.

Sources: vmware.com.