Dranizz
Contributor
Contributor

VCenter Server install: Multiple schemas

I get error in the VCenter Installation: Setup found that multiple schemas exist in the database, please remove the extra schema(s) before continue.

I had a SQL 2000 DB that I restored on a SQL 2005 so it was VCenter 4 compatible.

I have tried changing the compatibilty level to SQL 2005 (90) and even backing up the DB and restoring it back on the same server to make sure it was fully SQL 2005.

What should I do, I still get the error.

Thanks

0 Kudos
13 Replies
AlanJG
Contributor
Contributor

I'm getting the same:

" I get error in the VCenter Installation: Setup found that multiple

schemas exist in the database, please remove the extra schema(s) before

continue.

I had a SQL 2000 DB that I restored on a SQL 2005 so it was VCenter 4

compatible."

You appear to be the only other instance of this issue I can find. Have you worked through it yet?

0 Kudos
Dranizz
Contributor
Contributor

I installed VCenter Server anyway, and it worked fine.

I have read something saying that it was because the DB of VCenter and the DB of Update Manager where on seperate servers.

I have tried everything but still got the error.

Anyway, VCenter Server works fine.

I'll see this week if it cause any troubles during the ESX upgrade.

0 Kudos
AlanJG
Contributor
Contributor

I wanted to get back with you since you were kind enough to reply quickly to me. Here's some answers. At least enough that you can probably work through it. I'm not a SQL expert. We have an database admin for that. Our current VMware support is through Dell and after they couldn't come up with anything, they opened a ticket with VMware and this is the steps they proposed:

To check the database for multiple schema run the command:

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'

If this returns more than one schema all tables will have to be updated to be in the same schema.

This knowledgebase article mentions a stored procedure, sp_changeobjectowner, that is deprecated in SQL 2005. If you do a search for it in the SQL 2005 help file, here's what you get:

"Important:

This stored procedure only works with the objects available in Microsoft SQL Server 2000. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of SQL Server, this stored procedure will only change object owners when both the current owner and the new owner own schemas that have the same name as their database user names."

Our DBA used ALTER SCHEMA to change all object owners to <dbo>. It cleared the error and I have successfully upgraded to vCenter 4.

Thanks!

0 Kudos
Dranizz
Contributor
Contributor

Oh, thanks!

Anyway, so far everything worked anyway. I upgraded vCenter and both of my ESX host with no glitch at all.

Thanks for the feedback, I'll check that.

0 Kudos
vp102
Contributor
Contributor

Hi All,

I have run into similar problems to you with the multiple schema error and based on gathering information from a few sites I have decided to bring these resources together.

First off my environment is Upgrading from Virtual Center 2.5 to vCenter 4,1 with the Databse on a SQL 2008R2 Server.

Doing a straight upgrade i had the "Setup found that multiple schemas exist in the database. Please the remove extra schemas before continuing." message.

First off, stop the Virtual Center Services backup the databse.

Check for the existence of Multiple Schemas in the Database. In this case my Database name is vCenter.

USE

GO

ALTER USER WITH DEFAULT_SCHEMA=[dbo]

GO

Hope all this helps.

Regards

Shane

0 Kudos
peterjakobs67
Contributor
Contributor

thanks a lot Shane

It helped me upgrading my vsphere from 4.0 to 4.1 with my external mssql 2005 sp3 database (which was upgraded and upgraded and upgraded in the past)

Peter

0 Kudos
Oil_Slick
Contributor
Contributor

The formal solution from vmware on this does not work...Referencing a microsoft KB on how to manipulate the database metadata is not consistent with what VMware says to do...

We did NOTHING to have multiple schemas...the entire route from the start was based on default VMware settings...

This is utterly unacceptable that I have to do a run-around on this to get the upgrade working...

0 Kudos
steveb05
Enthusiast
Enthusiast

I completely agree. I've got a completely by-the-VMware-book fresh 4.0 installation thats been running less than a year. Go to upgrade vCenter to 4.1 and hit this right off the bat. This is indicative of the sloppy workmanship VMware has begun to exhibit...if you ask me, since EMC got involved.

I'd really like to know why this happens and, if this is not something the 4.0 installer cared about and/or documented changing the configuration for, why the 4.1 installer doesn't fix this automatically. BTW, I'm running against a SQL 2005 SP3 instance.

Now I have to schedule time with my DBA who has enough other things to do. If I didn't think Hyper-V was still behind the curve, I'd move since VMware is getting more costly with less quality. Its getting really hard to confidently tell finance I need to spend $60k a year (plus licensing) to keep this system in place, when with my Microsoft agreement, I could move to Hyper-V for a 1/3 of that.

- Steve Please consider marking this answer "correct" or "helpful" if you found it useful. Steve Brill Virtualization Junkie VMware, SAN/NAS, Networking and Server Infrastructure Engineer
0 Kudos
mikw73
Contributor
Contributor

I ran into this same problem, and the post from Shane (vp102) was a big help. It didn't quite get me there, though, mainly because of my limited SQL knowledge, I believe. I made notes of my experience. One thing I should point out first about Shane's post is that it looks like the web page comment upload interface took a couple of important characters out of his first line of code. he wrote:

schema, Delete user by same name just to be thorough in my case.

Change compaitbility mode of database from SQL 2000 to SQL 2005. (I ran into the "Exception thrown while executing SQL script" error later in the upgrade. http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=102513...

And then it worked. Sort of. I used a new server name and ip address, so I had trouble reconnecting my hosts, but that is a separate issue. Good luck!

0 Kudos
vm-au-user
Contributor
Contributor

I've found my database has mulitple schemas of dbo and db_owner so I'd like fix this before attempting the upgrade from 4.0 to 4.1

I've followed KB Article: 1011386 but its more specifIc to SQL 2000. What is the command to check for Mulitple Schema's in SQL 2008?

I've tried the process  suggested above (its seems quite complex compared to KB Article: 1011386), but when I re-check its still shows the two schema's. Have i missed something?

Can you temporailiy change the database compatability back to 2000 and run the old SQL commands as per the KB?

Any help would be great. I'm no DBA.

0 Kudos
vm-au-user
Contributor
Contributor

Found this article, the process worked, well.

http://theether.net/kb/100150

The listed texted missed some tables and stored procedure however running the checks at the end highlighted this and i was able to go back and re-run the commands for the tables and stored procedure that did not update.

0 Kudos
tkutil
Enthusiast
Enthusiast

I'm getting this error too. I have found I have two schemas named dbo and srv_vmware. I also have duplicate tables, etc... with prefixes of dbo & srv_vmware. Did anyone else have multiple tables too?

0 Kudos
mikw73
Contributor
Contributor

I did have multiple tables.  It's not all fresh in my mind anymore, but if you look at the text file attached to my previous post and read  (or re-read) Shane's post with the additional information from that text file, it may save you some time if you're seeingthe same thing I was.  I think it took me about five hours to puzzle through.

0 Kudos