A Guide to Migrating VMware 5.1 Databases from SQL Express to SQL

Hey All!

I had a somewhat messier database migration at my most recent site, and it made me do a bunch of research that would make sense to share here. Most of this information came from KBs or scattered across the Internet… so welcome to your one-stop-shop for how to migrate VMware databases, and what I had to do when things went wrong.

I had to migrate a pair of environments today. One of them was a View install that has more moving parts, so I’ll illustrate that here.

 

Getting Started / SQL Pre-Reqs

I’m not going to fill up this blog post with how to set up SQLBest practices. I assume that you know that already. I included links just in case. What I WILL include are things that are needed after SQL is set up:

  1. Open port TCP 1433 on any Firewall program running on the machine.
  2. Set ‘Maximum Server Memory‘ (SQL Memory Max) to something sane for your environment.
  3. Open SQL Configuration Manager and expand SQL Network Configuration. Make sure that TCP/IP is enabled. Disable Dynamic Ports.
  4. Good. Now create a new SQL user account– I used VMwareUser.

 

View Composer

I started with the View Composer database because it didn’t have a strong dependency on the other components- The other good reason is that VDI administrators tend to be different than your vCenter administrators and may have different availability windows.

  1. Go through each pool and disable any refit operations that occur on logoff.
    1. This is mostly a safety thing. I want to be sure that there are no desktop operations running until I say “
  2. Disable the View Composer service
  3. Create a backup of the View Composer database using a File Backup in SQL Express.
  4. Copy the database backup to network storage or a local drive on the new SQL server.
  5. Create a new shell database on the shiny SQL server. Call it something nice- this is name will only be seen by you or the DBA team.
  6. Right click the new database, go to ‘Tasks’ and select ‘Restore Database‘. Select the backup file, and on the Options tab select ‘OVERWRITE’.
  7. Make VMware User dbo of the restored Composer database.
  8. Back on the server running View Composer, edit the Composer DSN. This is a 64-Bit DSN, so Administrative Tools > Data Sources (ODBC).
  9. Modify the SVIWebConfig. Sorry :-/
  10. Start up View Composer. If it starts without error, re-enable refit operations on the pool.

 

Update Manager

Theres a strong argument to start Update Manager fresh instead of migrating old information – in my case, the customer wanted to maintain some custom baselines… so migrate away!

  1. Disable the VMware Update Manager service.
  2. Create a backup of the VIM_UMDB database using a File Backup in SQL Express.
  3. Copy the database backup to network storage or a local drive on the new SQL server.
  4. Create a new shell database on the shiny SQL server. Call it something nice- this is name will only be seen by you or the DBA team.
  5. Right click the new database, go to ‘Tasks’ and select ‘Restore Database‘. Select the backup file, and on the Options tab select ‘OVERWRITE’.
  6. Make VMware User dbo of the restored VUM database.
  7. Back on the server running Update Manager, edit the Update Manager DSN. This is a 32-Bit DSN, so c:\Windows\SysWOW64\odbcad32.exe
  8. Edit the vci-integrity.xml file to reflect the new database information. I’m really sorry about this.
  9. Reconfigure VUM using the VMware Update Manager Configuration Utility 
    1. Modify the Database settings
    2. Re-Register with vCenter.

 

vCenter Database

Here’s the big one. Make sure you have a window of time for this one to be down that extends for both vCenter and SSO – SSO shouldn’t cause an outage, but if there are any configuration issues vCenter won’t be able to start… better to be safe and have a longer outage window than required. While vCenter is offline, no administrators will be able to get in and run the environment, no power operations will occur for VDI desktops, and DRS won’t work (Among other things)

  1. Disable the VMware vCenter  service.
  2. Create a backup of the VIM_VCDB database using a File Backup in SQL Express.
  3. Copy the database backup to network storage or a local drive on the new SQL server.
  4. Create a new shell database on the shiny SQL server. Call it something nice- this is name will only be seen by you or the DBA team.
  5. Right click the new database, go to ‘Tasks’ and select ‘Restore Database‘. Select the backup file, and on the Options tab select ‘OVERWRITE’.
  6. Make VMware User dbo of the restored vCenter database.
  7. Open the Registry Editor. I’m really sorry about this too.
    1. Navigate to HKEY_LOCAL_MACHINE > SOFTWARE > VMware, Inc > VMware VirtualCenter.
      1. Ensure that HKEY_LOCAL_MACHINE\SOFTWARE\VMware, Inc.\VMware VirtualCenter\DB\1 contains the correct DSN.
      2. Edit HKEY_LOCAL_MACHINE\SOFTWARE\VMware, Inc.\VMware VirtualCenter\DB\1 to the SQL username ‘VMwareUser’
      3. Ensure that HKEY_LOCAL_MACHINE\SOFTWARE\VMware, Inc.\VMware VirtualCenter\DB\4 has the right SQL driver.
      4. Edit HKEY_LOCAL_MACHINE\SOFTWARE\VMware, Inc.\VMware VirtualCenter/DbInstanceName and clear it (Don’t delete though!)
      5. Edit HKEY_LOCAL_MACHINE\SOFTWARE\VMware, Inc.\VMware VirtualCenter/DbServerType and change the Value to Custom.
      6. Open an Administrative Command Prompt. CD to “C:\Program Files\VMware\Infrastructure\VirtualCenter Server” and run the command vpxd.exe -p
        1. Enter password information when requested.
  8. Recreate the SQL Rollup Jobs.
  9. Open another configuration file in notepad: C:\ProgramData\VMware\VMware VirtualCenter\vcdb.properties
    1. Put a hash mark (#) to comment out everything in this file EXCEPT  usevcdb=true
      1. NOTE: The file could be modified to contain correct information, but the above method seems to work fine as well. To each their own.
  10. In the same directory, open dabase_name.properties in notepad. Verify that the Tomcat information is correct.
  11. Attempt to start the vCenter Service.

 

Single Sign On

Reinstall Single Sign On. Just kidding, although migrating this component has made many an engineer pull their hair out. I had my own issues during this migration and the vast number of suggestions I received went along the lines of “It’s better to reinstall vSphere if SSO is having any issues”. I powered through, and now you can too!

  1. Backed up the SSO configuration using the “Generate vCenter Single Sign-On backup bundle” link in the Start -> Programs menu from the SSO server.
  2. Disable the vCenter Single Sign-On  service.
  3. Create a backup of the RSA database using a File Backup in SQL Express.
  4. Copy the database backup to network storage or a local drive on the new SQL server.
  5. Create a new shell database on the shiny SQL server. Call it something nice- this is name will only be seen by you or the DBA team.
  6. Right click the new database, go to ‘Tasks’ and select ‘Restore Database‘. Select the backup file, and on the Options tab select ‘OVERWRITE’.
  7. Create new users (Or verify that the users migrated during the restore process) RSA_USER and RSA_DBA
  8. Check that the RSA_User that was migrated doesn’t have any mappings using this query against the restored database: sp_change_users_login report
  9. Create a new SQL User named RSA_USER at the SQL Server level. Give it the same password as RSA_USER had on the original SQL Express installation.  Set the default database to the newly restored SSO database.
  10. Run this query against the SSO database to re-map the RSA_USER account: sp_change_users_login ‘update_one’, ‘RSA_USER’, ‘RSA_USER’
  11. Recreate the RSA_DBA SQL user account and give it DBO over the SSO database.
  12. On the SSO Server:
    1. Navigate to the ssocli command – In my case, it was C:\Program Files\VMware\Infrastructure\SSOServer\Utils. Run the following command: ssocli configure-riat -a configure-db –database-host new_host_name
      1. Enter the SSO Master password that was used when SSO was initially set up.
    2. Go up a directory and open up the ..\SSOServer\webapps\ims\WEB-INF\classes\jindi.properties file in Notepad.
      1. Modify com.rsa.db.hostname to the hostname of the new SQL server
      2. Change the com.rsa.instanceName to the SQL Database Name here (instanceName seems inappropriate)
    3. Navigate to C:\Program Files\VMware\Infrastructure\SSOServer\webapps\lookupservice\WEB-INF\classes\config.properties
      1. Change the dburl= line to the information for the new server.
  13. Start SSO and hope for the best.

 

Cleanup

Some cleanup items at this point:

  1. Go into the registry and break the dependencies on SQL Express for vpxd.
  2. Restart all VMware services twice to ensure proper operation
  3. Make sure that the Web Client works correctly and that performance graphs load as expected
  4. Restart the server to make sure all comes back up.
  5. ???
  6. Profit!
Advertisements