Customer had all his VMware databases for vCenter Server, Update Manager, Single Sign On (SSO), vCloud Director and vCenter Chargeback running on one big SQL Server where they shared resources with other databases. They asked me to move the databases to a new MS SQL Server because the load of the VMware databases was more than expected. I prepared the move by reading a few VMware KB’s that described how to move the databases, but still experienced some issues, that is why I wrote this blogpost to have a good manual for the next time I have to move these databases.
This first post shows how to move the SSO database.
Move the SSO database
Before the actual move, make sure that you’ve read the following KB: Updating the vCenter Single Sign On server database configuration (2045528). Since we also have to move the database, which is not described in the mentioned KB article, there are some extra steps to perform. This post only works for MS SQL Server.
- Make a connection to the old database server
- On the SSO server, stop the Single Sign On service.
- On the old database server make a backup of the SSO database (default name is RSA).
- After a successful backup, set the SSO database to Offline
- Copy the backup to the new database server
- On the new database server, import the SSO database
- On the security section of the SSO database, check if the user RSA_USER is present.
After this, the SSO database is available, but there is no login user connected. Usually when you create a SQL user, you also make a mapping to a database which then automatically creates the database user. In our case the database user is already present but is not yet mapped to a SQL user. That’s what we’ll do now. First let’s make sure the RSA_USER is not yet mapped:
- Run the following sql query against the SSO database to show all unmapped users of the database: sp_change_users_login report
- Now create a new SQL User (SQL Authentication) at the SQL Server level not at database level. Name this user RSA_USER and use the same password the database RSA_USER has. Set the default database to RSA (the SSO database).
- Run the following sql query against the SSO database to map the user RSA_USER (server level) to the RSA_USER (database level): sp_change_users_login ‘update_one’, ‘RSA_USER’, ‘RSA_USER’
- To check if things worked out, rerun the query against the SSO database. The RSA_USER should not show up: sp_change_users_login report
When running the queries, make sure you run them against the correct database. See image.
Next step is to create the RSA_DBA user which is only a SQL Server user and not a database user. But this user should become the owner of the SSO database.
- At SQL Server level create the SQL user RSA_DBA and be sure to use the same password you previously used. (Well, you can always reset it later on).
- After the RSA_DBA user has been created, open the properties of the user and now set this user as the owner of the SSO database
Your database is now ready for use. We just have to tell the SSO Server that it should look somewhere else from now on. To do this run the following on the SSO Server:
- Go to the ssoserver\utils folder (usually: C:\Program Files\VMware\Infrastructure\SSOServer) . Run the command: ssocli configure-riat -a configure-db –database-host new_host_name
- You will be prompted for a password, this is the master password you also used to login to SSO with the user: admin@system-domain or root@system-domain.
- Check the jndi.properties file for the correct database reference. You can find it in C:\Program Files\VMware\Infrastructure\SSOServer\webapps\ims\web-inf\classes\
- Edit the file C:\Program Files\VMware\Infrastructure\SSOServer\webapps\lookupservice\WEB-INF\classes\config.properties and modify all values that need to be updated.
- To update the SSO DB RSA_USER password, run the command if, for example, the RSA_USER password has expired or the Database has been moved to another SQL instance: ssocli.cmd configure-riat -a configure-db –rsa-user-password new_db_password –rsa-user New_RSA_USER
Now cross your fingers and start the SSO Service. Check if you can logon to the SSO Server: https://<your SSO server>:9443/vsphere-client/ Login with: admin@system-domain
Great post! Will definately bookmark this for future reference!
Hi, be carful about automatic correction ;) –database-host make an error… correct argument is –database-host (with 2 “-“).
thank you for your helpfull post.
Sgrimbuhler (VirtualGeek.ch)
I don’t suppose this applies when moving from a SQL Server vcenter DB to the embedded (PostgreSQL?) DB of a vCenter Server Appliance?
I’m afraid not. I don’t think the vCSA can import a database. But on the other hand, there is not much info stored in the database. You can easily recreate it. And with SSO 5.5 things have changed completely. There is no more database !!
Re: not much info
Unfortunately, our environment has many groups in system-domain made up of AD users/security groups — best case would be to keep those, and I hope support has a method for extracting that stuff.
And thanks for the article!
I know this is old, but I was wondering if you could clarify the point of the “ssocli configure-riat” commands if you still have to go in and change the config files anyways.
My guess is that the ssocli commands make a change INSIDE the database. And the change of the configuration files is for the web components. But that is my guess :-)