To set the scene: the new version is 4.1, which requires a new server because the server OS has to be 64bit. The existing management database is called 'VMware' and located on a SQL Server 2005 instance. On a previous install the SA was instructed to create a 'VMware' user, and create several tables under that account. And therefore automatically in the 'VMware' schema (which in SQL Server is not exactly the same as a user). So in the VMWare database about 1/3 of the objects lives in the dbo schema, and the rest in the VMware schema. While running the upgrade that leads to the following error:
"Setup found that multiple schemas exist in the database. Please the remove extra schemas before continuing."
Apparently, VMWare knows that such a thing may have happened before, and have written an instruction to solve it. It's here. Unfortunately, it is not enough to make it work. It took us two hours, so I write it down here to save you that time. And forget about the 8 steps on the aforementioned page.
- Make a backup from the original VMware database, before even starting the upgrade process.
- Start SQL Server Management Studio, and open a new Query Window.
- Execute: USE VMware;
- Execute: SELECT schema_id from sys.schemas where name = 'VMware';
- Execute: SELECT 'ALTER SCHEMA dbo TRANSFER ' + sys.objects.name from sys.objects where sys.objects.type in ('U', 'V', 'P') and sys.objects.schema_id =
; - Copy the result of step 5 from the result window to the query window, and execute this list of statements. You now have moved everything from the VMware schema to the dbo schema.
- I suppose you still want to use the VMware user to perform the upgrade, so you have to make sure that when you do, the upgrade scripts doesn't create new objects in the associated VMware schema again. It appears that SQL Server Management Studio lets you set the default schema for user VMware in the properties window. You can change it, but it won't remember the change... So, back to scripting:
- Execute: DROP SCHEMA VMware;
- Execute: DROP USER VMware;
- Execute: CREATE USER VMware WITH DEFAULT_SCHEMA = dbo;
- Finally, we came across another error in the upgrade log: "Error while upgrading: 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." Well, if you know that, VMware, include a statement in the upgradescript to make sure the setting is correct. But hey, we'll do it ourselves:
- Execute: SET ARITHABORT ON;
- Make another backup of the VMware database, prior to starting the upgrade.
- OK, by now the vCenter Server setup worked through the database upgrade script (using the VMware login for access to the database). I hope it does for you as well.
