Thursday, March 31, 2011

Upgrade vCenter SQL 2005 Express to SQL 2008 Standard

vCenter comes with SQL Server 2005 Express - works fine for smaller environments, but after a while your environment (# ESX servers and # of VMs) will grow and you will exceed the 4Gb licensed limit for this "free" SQL 2005 Express instance.

Once exceeded, your vCenter service will crash and fail on restart.

The error in your vCenter server vpxd log file will look like:

Error inserting events: "ODBC error: (42000) - [Microsoft][SQL Native Client][SQL Server]Could not allocate space for object 'dbo.VPX_EVENT_ARG'.'PK_VPX_EVENT_ARG' in database 'VIM_VCDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup." is returned when executing SQL statement "INSERT INTO VPX_EVENT_ARG


you can buy yourself some space by purging old records - but to address the root cause I chose to upgrade to a full SQL Server 2008.
Below are the steps to accomplish a vCenter SQL Server 2005 Express upgrade to SQL Server 2008 Standard (casting off the 4Gb limit). My vCenter 4.1 U1 runs as a VM in Windows 2008 64-bit Standard guest OS. Since the vCenter runs as a VM I forked off a clone to test the upgrade steps while not affecting the production vCenter. The upgrade path I used: SQL 2005 Express -> SQL 2008 Express -> SQL 2008 Standard:

SQL 2005 Express -> SQL 2008 Express:

1) Free up 5-10Gb on your vCenter server - windirstat is one of my favorite tools for identifying what is eating space on windows VMs
2) Take a snapshot of your vCenter VM "preSQL upgrade" (just in case)
3) download SQL 2008 Express: http://www.microsoft.com/express/Database/
4) run the SQLEXPRWT_x64_ENU.exe to upgrade to SQL 2008 Express
5) You will likely get an error towards the end:
error on SQL Server 2005 tools - “Please remove” -

To Re-run with success, you need to rename the registry entry:
HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\Tools out of the way (eg HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\Tools_old)
6) once the registry entry is renamed, click the re-run button to retry without restarting the upgrade

SQL 2008 Express -> SQL 2008 Standard:

1) Purchase a SQL Server 2008 Standard license to obtain a valid product code
2) Download the SQL Server 2008 Standard install
3) run the setup.exe with a special parameter "SKUUPGRADE=1" from the cmd prompt
4) Don't choose upgrade existing - choose "New install or add features to existing..." for the type of install.
5) Select all features when prompted
5) Supply the credentials for SQL services
6) This will run for a good 30+ minutes.
7) At the end your vCenter service will fail to start with the following in the log:

Failed to create https proxy: An attempt was made to access a socket in a way forbidden by its access permissions.


8) Turns out (http://kb.vmware.com/kb/1026305 clued me into this) SQL 2008 brings a reporting service on port 80 - CONFLICTING with vCenter! - disable the reporting service
9) restart vCenter (I rebooted to ensure clean startup)
10) connect with VI Client to verify all is good!
11) Once the vCenter functions are all verified remember to delete the preSQL snapshot

While figuring out how to jump through these upgrade hoops I was wishing for a MySQL option for vCenter - apparently in 2009 VMware attempted a beta of this, but it was abandonded - today the options are SQL or Oracle for the vCenter DB.

UPDATE 4/4/11:
I noticed the SQLEXP_VIM database was not migrated to the SQL Server 2008 instance.
I needed to detach it from the 2005 instance and re-attach it to the 2008 instance according to:
http://get-admin.com/blog/how-to/migrate-vcenter-database-from-sql-express-to-full-sql-server/

Once this was done, we were finally rid of the 4Gb limit on the vCenter SQL DB!

32 bit DSN required by Update Manager:
per
http://blog.eight02.com/2010/11/bits-of-odbc-advice-for-vcenter-update.html
"The DSN, ‘VUM’ does not exist or is not a 32 bit system DSN. Update Manager requires a 32 bit system DSN."

Cause: Using the ODBC tool in the Control Panel will create a 64-bit DSN. You need to use the 32-bit ODBC tool which is located at C:\Windows\SysWOW64\odbcad32.exe. Do NOT use the odbcad32.exe located in the C:\Windows\System32 folder. While it has the same file name, they are two different files!! - I was running the wrong exe by default...whew!

2 comments:

techtalk87 said...

Starting this process this week. Thank you for your hard work!

-Cesar

TRansom said...

During the uppgrade from SQL 2008 Express to SQL 2008 Standard, at the Snstance Configuration step, I don't know if I should select a named instance or the default instance radio button. Called Microsoft for help.