Upgrading / migrating vCenter 5.0 SQL Express database to a full SQL Server 2008 R2

I’ve gone through a few migrations from a vCenter server SQL Express database to a full blown database and while it’s usually pretty straight forward, I find that I’ve always had to stop and think about what I needed to do so after going through this process a few times over the past few years, I decided to document the process and blog it so I can have something to reference to in the future.

The following are assumptions about the environment:

• You have vCenter 5.0 installed onto a Windows Server 2008 R2 server.
• The database you installed is the SQL Express instance that gets automatically installed by the vCenter 5.0 installer.
• You are going to change the database to SQL Server 2008 R2 Standard Edition (Enterprise would be the same).
• The new instance of SQL Server 2008 R2 will be installed directly onto the vCenter server.

What I did at the very beginning is to document what the Programs and Features list looked like prior to the change with SQL Express installed:

… and what the final state looked like:

The first step is to start the installation for the standalone SQL Server 2008 R2 instance so mount the ISO or run the installer from a folder with the installation binaries:

Where you use the Default instance or Named instance for the new SQL installation is up to you:

The following is what the the Programs and Features would look like after the install:

Once you have the new instance of SQL installed, proceed with stopping the vCenter service:

With the vCenter service stopped, proceed with uninstalling the SQL Express instance:

Select the Remove option:

Proceed through the setup wizard:

Notice how there are 2 instances listed.  The one we would want to uninstall is the VIM_SQLEXP instance:

Select the VIM_SQLEXP instance for the Instance to remove features from drop down menu:

Select the following 2 checkboxes under the VIM_SQLEXP node:

• Database Engine Services
• SQL Server Replication

Proceed with the wizard:

The uninstall will now proceed:

Once the uninstall of the SQL Express engine completes, the Programs and Features will look like the following:

With the SQL Express edition uninstalled, now is the time to start the SQL database relocation process so navigate to the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.VIM_SQLEXP\MSSQL\DATA

Copy the database file (mdf) to the appropriate drive and directory you want to store the database files.  For this example, I’m storing it on the K drive in the a folder named Databases:

Do the same for the log file (ldf):

Rather than mounting the databases and configuring the permissions, I chose to just create a new database and service account (VPXUSER), delete the database, then mount the database and set the permissions.  The more elegant solution (I’ll make sure I document it the next time I do this) is to just cut out the database creation code and create the account and then set the permissions so feel free to change the code below if you want to do it that way:

use [master]
go
CREATE DATABASE [VCDB] ON PRIMARY
(NAME = N'vcdb', FILENAME = N'K:\Databases\VCDB.mdf', SIZE = 2000KB, FILEGROWTH = 10% )
LOG ON
(NAME = N'vcdb_log', FILENAME = N'L:\Logs\VCDB.ldf', SIZE = 1000KB, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
go
use VCDB
go
sp_addlogin @loginame=[vpxuser], @passwd=N'P@ssw0rd', @defdb='VCDB', @deflanguage='us_english'
go
ALTER LOGIN [vpxuser] WITH CHECK_POLICY = OFF
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
go
use MSDB
go
CREATE USER [vpxuser] for LOGIN [vpxuser]
go
sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'

For more information about how to create a vCenter 5.0 database, see one of my previous posts: http://terenceluk.blogspot.com/2012/03/preparing-creatingvmware-vsphere.html

With the account and database created, proceed with deleting the newly created database:

With the database created, now is the time to rename the SQL Express vCenter database filename removing the VIM_:

**Note: that this is obviously just cosmetics but I’m a true believer of detail so whether you want to rename it is up to you.

With the database filenames renamed, proceed with attaching the database to the SQL server:

Notice that once you’ve attached the database, the file names and path are incorrect so club on the square box to change the location for the mdf file:

Now that the mdf file is correct, proceed to do the same for the ldf file:

With the database details correct, click OK to close the window and attach the database:

Once the database is mounted, you’ll see that the database name is incorrect so proceed with renaming it:

Again, we can leave the database in the current state but there are still some values of this database that were carried over from the SQL Express edition so continue with the following steps to tidy up the database.  Open up the properties of the vCenter database (VCDB) and navigate to the Files section:

Change the logical names to vcdb for the database and vcdb_log for the log file:

If you’d like to double check the initial size and growth of the database to ensure that it mirrors the settings applied via the transact-sql for creating a vCenter database, you can compare it with the following:

Database: SIZE = 2000KB, FILEGROWTH = 10%
Log: SIZE = 1000KB, FILEGROWTH = 10%

Proceed with confirming the Options settings:

Next, assign the VPXUSER service account the appropriate permissions:

use VCDB
go
sp_addrolemember @rolename = 'db_owner', @membername = 'vpxuser'
go
use MSDB
go

Launch the ODBC Data Source Administrator to change the DSN settings:

What I noticed from here on was that the vCenter service will not start:

Windows could not start the VMware VirtualCenter Server service on Local Computer.

Error 1075: The dependency service does not exist or has been marked for deletion.

This immediately reminded me to check the dependency of the vCenter service because it’s probably set to depend on the SQL Express service which no longer exists:

The following are 2 blog posts I wrote about this:

http://terenceluk.blogspot.com/2010/07/addressing-virtualcenter-service-not.html

http://terenceluk.blogspot.com/2010/07/vcenter-virtual-center-service-fails-to.html

With the dependency removed, I noticed that I still wasn’t able to start the service:

The vCenter service would just get stuck on starting:

So from here on, what I did was just uninstall and reinstall vCenter from the server since all of the information was stored in the database.  This is the equivalent of treating this as a DR situation:

Make sure you choose Do not overwrite, leave my existing database in place during the install:

Once I completed the install, vCenter was now able to start and everything was back to normal.

Another item to clean up at the end is the folder left over from your SQL Express instance so go back and delete those folders as they are no longer needed: