Friday, March 16, 2012

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:

image

… and what the final state looked like:

image

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:

image

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

image

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

image

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

image

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

image

Select the Remove option:

image

Proceed through the setup wizard:

image

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

image

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

image

Select the following 2 checkboxes under the VIM_SQLEXP node:

  • Database Engine Services
  • SQL Server Replication

image

Proceed with the wizard:

image

image

The uninstall will now proceed:

image

image

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

image

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

image

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:

image

Do the same for the log file (ldf):

image

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'

image

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:

image

image

image

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

image

image

**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.

image

image

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

image

image

image

image

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:

image

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

image

image

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

image

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

image

image

image

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:

image

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

image

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:

image

Next, assign the VPXUSER service account the appropriate permissions:

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

image

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

image

image

image

image

image

image

image

image

image

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

image

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.

image

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:

image

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

image

image

image

image

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

image

The vCenter service would just get stuck on starting:

image

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:

image

image

image

image

image

image

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

image

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:

image

2 comments:

Anonymous said...

Thanks Terence
 
I used you post as the basis for my upgrade from SQL Express to 2008 R2. I had a few issues along the way but the basic process was sound.
 
I could not get the SQL script to run for various reason (Initial size for one) so I just used the GUI to create the initial datbase and the accounts. Also you should note that when you remove vCenter it also removed the ODBC DNS entry that you just modified! No to much hassle to re-create but was an unexpected wrinkle.
 
This procedure also work for migrating the update manager database although this requires a 32bit ODBC connection. The default ODBC administrator in control panel is the 64bit flavour. To access the 32bit ODBC administrator look in c:\windows\syswow64 and look for odbcad32.exe. The install will not find the system DNS unless you have created it using this version of the ODBC administrator.
 
Cheers
 
Mark Humphries
IT Network Administrator
Aylesbury
UK

Anonymous said...

Terence,

Just wanted to say nice Blog. I must say that Mark is correct, I also had problems with the scripts and the ODBC connector is removed.

There is an additional step that I think is also missing.
You may need to set up or recreate SQL agent jobs to manage historical data

The below link is a great link for doing this

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004382

Regards

DJ