Migrate vCenter database from SQL Express to full SQL Server

These are the steps I used to move vCenter’s SQL Express database to a standalone SQL Server instance. I did this for two reasons: 1) my server and VM count exceeded the number recommended for use by SQL Express, and 2) I wanted better control over the database. Theoretically, your steps should be very similar, but not exactly the same. Please keep this in mind if you attempt this process using these directions.

Before you do anything, backup your current vCenter database. This is your fall back plan should the entire thing get borked, consequentially this is probably the most important step!

Should you encounter any problems during this process the KB is your friend. Some articles to read (before hand even!) are 1003928, 7960893, and 1478. VMware has published a document specifically about using SQL Server for vCenter here. If you are not friends with your (or any…) DBA, go buy him/her a beer now…they should be an integral part of this process as they have probably done things similar to this many times!

Prepare by finding the location of your database files. Using SQL Server Management Studio (SSMS), connect to the SQL Express instance (vc.server.nameSQLEXP_VIM), expand the databases, right click the VIM_VCDB and select “Properties”. Select the “Files” page on the left, then scroll to the right in the right pane to find the “Path” column. This is the location of the database’s files. If you have VUM installed, they should be in the same place.

For me they were installed to C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData.

From your VC server, stop the VMware services:

  • VMware Converter Enterprise Service
  • VMware Infrastructure Web Acess
  • VMware License Server
  • VMware Update Manager Service (only if VUM is installed)
  • VMware VirtualCenter Server

If you have other VMware services (VMware Tools Service, VMware Physical Disk Helper Service) they are related to VMware tools, and should only be present if your VC server is also a VM.

From SSMS, right click the VIM_VCDB database and choose “Tasks->Detach”. Do the same for VIM_UMDB if it’s present. Now copy the .mdf and .ldf files for the databases from the location you found above to the destination location on your SQL server. The destination is wherever you have decided you want the files to be located at on your standalone SQL Server. It doesn’t necessarily have to be the default location for the server, but that does make the most sense…

Remember that the .mdf file is “data” and the .ldf file is “logs”. I highly recommend that you copy, not move, the files (just in case something goes wrong). You also don’t want to delete the original(s) until you are sure they are working in their new home.

After the files are copied, from SSMS connected to your standalone SQL Server, right click “Databases” and select “Attach”. Browse to the location you placed the VIM_VCDB.mdf and select it. After selecting it you will see in the bottom pane the data file location (you should have just selected it) and the log file location. If it is reporting in the message column that the log file is not found, you must browse for it and point it to the log file. IT MUST HAVE THE LOG FILE TO SUCCEED! After you have ensured that both files have been located, hit the ok button. Repeat for VIM_UMDB if you have VUM installed.

We now need to create a login for VC to use when connecting to the database. First we want to delete the existing user from the database. From SSMS connected to your standalone SQL Server instance, expand the “Security” folder for the VIM_VCDB database, then expand the “Users” folder. Look for the “virtual_center” user, right click and select “Delete”. Hit OK in the pop-up. Do the same for VIM_UMDB if you have Update Manager.

Now, we recreate the login. From SSMS, expand the Security folder that is at the root folder level, then right click “Logins” and select “New Login”. In the pop-up, enter the login name you want to use (I use “virtual_center”), select the radio button for “SQL Server authentication”, then enter a password. If you are in an AD domain, you will have to meet it’s password complexity requirements. Make sure to uncheck “User must change password at next login”. At the bottom, select “VIM_VCDB” as the default database.

Before clicking “OK”, select the “User Mapping” page on the left…we want to give virtual_center dbo rights on it’s database. You should see a list of all the databases on the server. Check the box next to VIM_VCDB, then click the elipsis (“…”) in the gray box of the “Default Schema” column. In the ensuing popup, click the “Browse” button, scroll down to find the “[dbo]” role, check the box, then hit “OK” twice. In the bottom pane (labeled “Database role membership”), put a check in the box for “db_owner”. Repeat this process for VIM_UMDB if you have Update Manager. Click OK to create the new user and have it added to the database(s).

Test that you can connect as the virtual_center user by connecting as that user. You can have multiple connections from SSMS to the same server with different logins. Go to “File->Connect Object Explorer”, make sure that the correct server is specified and “SQL Server Authentication” is selected for “Authentication”. Enter your VC username and password then click “Connect”.

You should be able to see the databases on the server when you expand the databases folder. You should also be able to see all of the tables and views when you expand those folders. Verify you have access by opening a table…expand the Tables folder and find the “dbo.VPX_HOST” table. Right click and select “Open”. In the right hand pane you should see the table’s contents (which happen to be your ESX hosts) in an Excel like format. If you do not see the tables, make sure that the permissions are correct for the virtual_center user. If you see no data, again check permissions, or this may be normal if you had no ESX hosts connected to vCenter.

Since are treating this “update” similar to a new install, according to the VMware documentation, the user that VC uses to initialize the database must be a sysadmin on the server. To enable this privledge level, from the root Security folder, expand Logins, and right click on the virtual_center user, select “Properties”. Select the “Server Roles” page on the left, then place a check for the box “sysadmin”. Click ok. Note that if this is a shared SQL Server instance, this user now has full, unfettered access to all databases, so after we are finished with the reconfigure of vCenter, we will set the permission back to normal.

Now that we have the database prepared and verified access, from your VC server, open the ODBC Administration application (Start->Administrative Tools->Data Sources(ODBC)). Browse to the System DSN tab, then select the “VMware Virtual Center” DSN. Click “Configure” on the right. In the pop-up, leave the name and description alone, but change the Server to read your server’s name (and instance if applicable). Click “Next”. In this window, ensure that the bottom radio button is selected (“With SQL Server authentication…”) and the checkbox below it is selected. In the two text boxes, enter the username and password created above. Click next, in this window, select the top check box (“Change the default database to:”), then select the VC database (“VIM_VCDB”). Click Next. Unless you are not using english, you shouldn’t have to do anything on this window, click “Finnish”. In the pop-up, click “Test Data Source”…it should say “TESTS COMPLETED SUCCESSFULLY!”, if not, check all of the previous settings to make sure they are correct. This MUST complete before it will work! Assuming it completed, click OK twice. Repeat this for the “VMware Update Manager” DSN if it exists. Click OK to close the ODBC administrator.

Now we must let vCenter itself know of the change. Open the control panel and “Add/Remove Programs” on your vCenter server. Find the “VMware VirtualCenter Server” entry, then click “Change”. This will look very much like the install window, click “Next”, then select the radio button for “Repair”, click “Next”. In the next window, select the bottom radio button (“Use an existing databse server”), click Next, then provide the DSN, username, and password that were just created. THE DSN MUST MATCH EXACTLY WHAT IS FOUND IN THE ODBC ADMINISTRATOR! Click next. A pop-up should appear that asks if you want to reinitialize the database…DO NOT CLICK YES UNLESS YOU WANT TO ERASE ALL CURRENT DATA FOR YOUR INFRASTRUCTURE! I repeat…DO NOT CLICK YES UNLESS YOU ARE AWARE OF THE CONSEQUENCES! Choose “No” to keep all the existing data, and acknowledge the SQL Server Agent prompt. Assuming you already have License Server installed to the localhost (or somewhere), choose the bottom radio button (“Configure … to use an existing license server”) and hit next. Again, assuming you installed License Server to the localhost (your vCenter host that is), keep the defaults and click Next. A pop-up will complain about not being able to contact the license server, this is normal (we turned it off before we began, remember?), click ok to move on. Keep the defaults on the next pop-up and click Next. I like Tomcat to start automatically so I always select the top checkbox on the following screen. Click next, then “Install” to start the process.

The installer may ask for the location of it’s install files if they have been moved/deleted. I originally installed from the zip, so I had to unzip the files again and point it at the directory so it could find the files it needed.

Once it’s done we can now restart the vCenter services. The VirtualCenter Server service should have started automatically when the above step finished…if it did not, start it first. After vCenter, you can start the Update Manager, License Server, Converter and Capacity planner services. Also, make sure that the SQL Express services (“SQL Server (SQLEXP_VIM)”) are stopped and disabled.

From your workstation, open VI Client and connect to vCenter. You should see everything as it was before.

In order for the performance stats to roll up correctly we need to add the agent jobs onto the SQL Server. This process is described in the VMware Knowledge Base.

The final step is to go back to SSMS connected to your SQL Server and remove the sysadmin server role assigned to the virtual_center user.

I also recommend that you take this opportunity to do some database maintenance. VMware has provided a KB article that describes how to do this here. Make sure you heed their warning about not having vCenter connected when you are doing maintenance. This is also your chance to change the recovery model to Full or Bulk Logged if desired.

16 thoughts on “Migrate vCenter database from SQL Express to full SQL Server

  1. Hello,

    any idea how to move from SQL Server 2005 to SQL Server 2005 Express from VI M2.5 point of view?

    Robert

  2. Robert,

    As far as I know, you shouldn’t have a problem so long as the database doesn’t exceed the maximum size for SQL Express (4 GB). You can right-click the database from SSMS and view the properties to check the size of it, or you can view the size of the .mdb file in the data directory for the SQL instance.

    If the file is too large, you can try to do a shrink on the data file, which should yield some space savings.

    The only issue that comes to mind is the rollup stats. Without SQL Agent to run the tasks, vCenter should execute the queries itself. I assume that when you reconfigure vCenter and it realizes that it’s using SQL Express it will take on the task.

    Andrew

  3. I want to say thank you very much for this wonderful and informative article on migrating from SQL Express to SQL full. I followed your advice and steps in the article and it worked perfectly. I was backup under 2 hours. Thanks.. 🙂

  4. Good post but make sure if you are using VSphere 4 potentially when you go to repair VCenter it might not have the option. You can manually tell VSPhere with below link

    http://kb.vmware.com/kb/1003928

    section

    Modifying the username and password VirtualCenter uses to connect to the database server

  5. I don’t seem to find the “change/modify” button in the add remove programs section. i only get, change linked mode configuration and uninstall.

    • IonutN,

      The only thing that comes to mind would be to ensure you are logged in as an administrator. I will have to ask a more experienced Windows administrator if that is not the answer.

      Thanks for reading,

      Andrew

  6. Andrew – Thanks for posting. This is a detailed, well thought-out post. I followed it and had no issues whatsoever. In my case, I was migrating the DB to another server, which was alos running SQL Server Express 2005. So, my modification was to skip the steps related to stopping / disabling the SQL Express services (“SQL Server (SQLEXP_VIM)”, as they were still needed after the migration to the new machine.

Leave a Reply

76171e8f956b817ed06ca75d7f545fee]