Setup found that multiple schemas exist in the database, please remove the extra schema(s) before continue

There is very little information about this, and it occurs when you try to upgrade from Virtual Infrastructure 2.5 to vSphere 4 and the issue of multiple schemas exists.  VMWare has an article (here), but unfortunately you have to have SQL 2000 for this to work.  If you have SQL 2000 and upgrade to SQL 2005 as part of the vSphere upgrade (as we did!), then this article is useless!

Here’s what to do:

First, determine the Schemas you have:

SELECT distinct sys.schemas.name AS schema_name FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id and sys.schemas.name <>’sys’

Hopefully it’s just two 🙂

Then run the following SQL commands, which will output ‘Alter Schema’ commands that must be run separately (Substitute ‘VMWare’ with the other schema name from the previous command):

SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘VMware’

SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Tables p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘VMware’

SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Views p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘VMware’

This is from Penncock’s Forum

As always, back up the database, etc, etc and don’t come to me if you break anything! :-[

---

After a night’s reboots, etc, there were still problems.  The VI Client was SLOW, but seemed to be working OK.  Did a reboot of the VI server, but the VI Service would not start and generated the following log:

Event Type:    Error
Event Source:    Service Control Manager
Event Category:    None
Event ID:    7024
Date:        4/14/2010
Time:        9:15:11 AM
User:        N/A
Computer:    VIC
Description:
The VMware VirtualCenter Server service terminated with service-specific error 2 (0x2).

Research showed this was not an uncommon log, but there didn’t seem to be a cohesive solution – just that it was a DB connection problem.  Remember, we have done a SQL 2000 to SQL 2005 upgrade, and also buggered with the Schema.

To fix, we did the following on the remote SQL Server:

Open the SQL instance and go to Security->Logins and select the VC User (in our case ‘VCUser’) and then go to ‘User Mapping’.  We changed the default Schema on ‘msdb’ AND ‘VirtualCenter’ (the only things that had a Schema defined) from ‘VCUser’ (the old/second schema) to ‘dbo’.  The service now starts on the VI Server.  After a reboot the service continued to fail to start, but at least does start on the second attempt.

More news:  I finally found the logs at “C:\Documents and Settings\All Users\Application Data\VMware\VMware VirtualCenter\Logs”, and saw the following entry during the service failures:

[2010-04-14 08:43:15.889 ‘App’ 4152 error] [VpxdVdb] Failed to init tableDef: Column VER_ID does not exist in table VPX_VERSION.  Database version may be incompatible.
[2010-04-14 08:44:15.945 ‘App’ 4152 error] Failed to intialize VMware VirtualCenter. Shutting down…
[2010-04-14 08:44:15.945 ‘App’ 4152 info] Forcing shutdown of VMware VirtualCenter now

This led me to this article:  http://communities.vmware.com/thread/116268 – which I think (after getting through the database-ese) is telling me basically what I did.  They suggest re-creating everything and verifying the settings I changed manually, but I think the end resulty is the same.