SQL Upgrade Error - vCenter 4.1 Upgrade

As I said today on Twitter - this one took up some of my time today.

During the upgrade of a vCenter 4.0 Server to 4.1 - which included installing a new 64 bit Windows 2008 R2 Machine and moving the Database from one server to another, I encountered an error during the process of the upgrade of the SQL database which stopped the installation.

The installation log (which is usually located under C:\Users<username>\AppData\Local\Temp<digit>\vminst.log gave me an error

The SQL upgrade log in the same location said:

Error: Failed to execute command: create unique clustered index VPXI_DEVICE_COUNTER_INDEX on dbo.VPXV_DEVICE_COUNTER(entity_id, device_name, stat_id)

[24-08-2010 13:50:26] Got exception: ERROR [42000] [Microsoft][SQL Native Client][SQL Server]CREATE INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Which in-turn sent me to Prof. Google and KB 1024449 which in essence says that you will have to make a change to the database server configuration due to some SQL options not being set. Which struck me as strange because this was a default installation - the same as many before.

Which brought me to this thread which suggested an issue with the compatibility mode of the database itself, which was spot on.

A quick change of the compatibility level from 2000 to 2005 and the installation went on without a hitch.


Another successful upgrade completed!!