Solved: Re: Virtual Center Database migration sql 2000 200...

acidric · ‎09-10-2009

hi, (sorry for my bad english)

I moved the db from a remote sql 2000 installation to a remote sql 2005 server. I followed these steps basically:

To migrate a vCenter 2.5 Server database from SQL Server 2000 to SQL Server 2005:

1 Stop the vCenter Server service.

2 Stop the vCenter database on SQL Server 2000.

3 Take a complete backup of the vCenter database.

4 Copy the backup of the database to the SQL Server 2005 host.

5 Create a new database on SQL Server 2005.

6 Restore the backed up SQL 2000 vCenter database to the newly created database.

7 correct permissions (same account, same password) see KB6565318

8 change ODBC connection to SQL NATIVE

9 Perform a Change/Repair of vCenter from Control Panel > Add/Remove Programs.

10 During the Repair wizard, i select Custom and choose the newly created database on SQL Server 2005.

11 the authentication type used on SQL 2005 is the same as was used on SQL 2000.

the license screen has an option box that says "I want to evaluate the product first," the installer has not correctly recognized the previous VirtualCenter database." ???

Now I can access the db via ODBC, and I'm able to start the VC service. However, when I open the client- I SEE NOTHING. Everything is gone. Totally empty. The data is still there though. I can go into sql 2005 and open the tables and see my hosts and VMs listed.

Please help.

Thanks!

Gerrit_Lehr · ‎09-11-2009

I really think this is a permission problem but I'm not that deep into SQL Servers.

Did you check for orphaned users?

What permissions are set for the VC Database?

Kind Regards,

Gerrit Lehr

If you found this or other information useful, please consider awarding points for "Correct" or "Helpful".

Kind regards, Gerrit Lehr If you found this or other information useful, please consider awarding points for "Correct" or "Helpful".

View solution in original post

Gerrit_Lehr · ‎09-10-2009

Did you fix the orphaned DB user using sp_change_users_login ‘auto_fix’, ‘vmware’ ?

Also, have alook at the vcenter logd and see if there is any further information contained.

Kind Regards,

Gerrit Lehr

If you found this or other information useful, please consider awarding points for "Correct" or "Helpful".

Kind regards, Gerrit Lehr If you found this or other information useful, please consider awarding points for "Correct" or "Helpful".

acidric · ‎09-10-2009

No, do you have the number ok kb corresponding to this ? (sp_change_users_login)

(I created an identical user based on SQL 2005)

What log file should I look? I watched some, I found nothing

Regards,

Acidric

Gerrit_Lehr · ‎09-10-2009

http://technet.microsoft.com/en-us/library/ms174378%28SQL.90%29.aspx

vCenter Logs shold be located in %TEMP%\vpx

Kind Regards,

Gerrit Lehr

If you found this or other information useful, please consider awarding points for "Correct" or "Helpful".

Kind regards, Gerrit Lehr If you found this or other information useful, please consider awarding points for "Correct" or "Helpful".

acidric · ‎09-10-2009

I redo the login 'VirtualCenter' manually, without using the Microsoft procedure

it's a problem ?

Log of virtualcenter:

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 Begin Logging

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 ======================================================================

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 --- CA exec: VMResetRegSecurity

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 Reseting security for HKLM\SOFTWARE\VMware, Inc.

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 Reseting security for HKLM\SOFTWARE\VMware, Inc.\VMware VirtualCenter

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 End Logging

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 Begin Logging

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 Saving installer window caption

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 Getting Property ProductName = VMware VirtualCenter Server

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 End Logging

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 Begin Logging

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 --- CA exec: VMCheckMsde

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 Setting property MSDE_INSTALLED =

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 Getting Property MSDE_INSTANCE = SQLEXP_VIM

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 'HKLM\SOFTWARE\Microsoft\Microsoft SQL Server' key not found.

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 There is no supported MS SQL Server installed.

VMware VirtualCenter-build-147633: 09/10/09 14:56:03 End Logging

VMware VirtualCenter-build-147633: 09/10/09 14:56:18 Begin Logging

VMware VirtualCenter-build-147633: 09/10/09 14:56:18 --- CA exec: VMCheckDsn

VMware VirtualCenter-build-147633: 09/10/09 14:56:18 Getting Property DB_DSN = VirtualDB

VMware VirtualCenter-build-147633: 09/10/09 14:56:18 Getting Property DB_USERNAME = virtualcenter

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Successfully connected to the custom database

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Database Name is Microsoft SQL Server

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Database Version is 09.00.4035

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Client Driver is SQL Native Client

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Driver Version is 09.00.1399

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Database Server Name is VMWCONSOLE03

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Setting property SQL_DB_TYPE = SQL Native Client

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 sql query: SELECT serverproperty('Edition')

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 sql server edition installed is <Standard Edition>

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 SQL Server is not a desktop edition.

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 SQLFetch: DB Name = VCDB

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 The DSN is NOT pointing to the master database.

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Query :SELECT * FROM virtualcenter.VPX_VERSION;

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Query :SELECT is_srvrolemember('sysadmin')

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 The database name is VCDB

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Normalized user name is :virtualcenter

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Query :select * from msdb.dbo.sysjobs where name='Past Day stats rollup' AND originating_server_id=(select server_id from sys.servers where name='VMWCONSOLE03') AND NOT owner_sid=SUSER_SID('virtualcenter')

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Query :select * from msdb.dbo.sysjobs where name='Past Day stats rollupVCDB' AND originating_server_id=(select server_id from sys.servers where name='VMWCONSOLE03') AND NOT owner_sid=SUSER_SID('virtualcenter')

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Query :select * from msdb.dbo.sysjobs where name='Past Week stats rollup' AND originating_server_id=(select server_id from sys.servers where name='VMWCONSOLE03') AND NOT owner_sid=SUSER_SID('virtualcenter')

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Query :select * from msdb.dbo.sysjobs where name='Past Week stats rollupVCDB' AND originating_server_id=(select server_id from sys.servers where name='VMWCONSOLE03') AND NOT owner_sid=SUSER_SID('virtualcenter')

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Query :select * from msdb.dbo.sysjobs where name='Past Month stats rollup' AND originating_server_id=(select server_id from sys.servers where name='VMWCONSOLE03') AND NOT owner_sid=SUSER_SID('virtualcenter')

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Query :select * from msdb.dbo.sysjobs where name='Past Month stats rollupVCDB' AND originating_server_id=(select server_id from sys.servers where name='VMWCONSOLE03') AND NOT owner_sid=SUSER_SID('virtualcenter')

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Getting Property MINOR_DBUPGRADE_SUPPORTED = 1

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Failed to execute query: -1

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 ODBC Error: [SQL Native Client][SQL Server]Invalid object name 'VPX_VERSION'.

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Setting property SHOW_EVAL_UI = 1

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Setting property EVAL = 1

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 The database is empty. Setup will create a new database repository.

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 sql query: select DB_NAME();

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 db name is : <VCDB>

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 going to execute query 2 for MS SQL DB

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 sql query: declare @retcode int exec @retcode = sp_MSreplcheck_subscribe if @retcode<>0 raiserror('User does not have sysadmin or db_owner privileges on the database for VirtualCenter installation.',16,1);

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 SQLRETURN value is: 0

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 going to execute query 3 for MS SQL DB

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 sql query: use msdb;

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 going to execute query 4 for MS SQL DB

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 sql query: declare @retcode int exec @retcode = sp_MSreplcheck_subscribe if @retcode<>0 raiserror('User does not have sysadmin or db_owner privileges on the msdb database.',16,1);

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 SQLRETURN value is: 0

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 sql query: use "VCDB";

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Master DB case is : French_CI_AS and len is 12

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 SQLFetch: Master DB is Case-Insensitive

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 DB case is : SQL_Latin1_General_CP1_CI_AS and len is 28

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 SQLFetch: DB is Case-Insensitive

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 sql query: SELECT serverproperty('Edition')

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 sql server edition installed is <Standard Edition>

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 SQL Server is not an express edition

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Getting Property UILevel = 5

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Attempting to retrieve error message for error Id: 25111

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Msi Error table Query is <SELECT Message FROM Error WHERE Error=25111>

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Error message is Please make sure SQL Server Agent service is running on the database server.

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Successfully retrieved error message from Error table

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 Getting Property ProductName = VMware VirtualCenter Server

VMware VirtualCenter-build-147633: 09/10/09 14:56:21 SQLFetch: DB Recovery Model = BULK_LOGGED

VMware VirtualCenter-build-147633: 09/10/09 14:56:21 Setting property DatabaseOK = 1

VMware VirtualCenter-build-147633: 09/10/09 14:56:21 Setting property FORMAT_DB = 1

VMware VirtualCenter-build-147633: 09/10/09 14:56:21 Setting property LAUNCHWIZARD =

VMware VirtualCenter-build-147633: 09/10/09 14:56:21 Setting property VPX_START = 1

VMware VirtualCenter-build-147633: 09/10/09 14:56:21 End Logging

regards,

Acidric

acidric · ‎09-10-2009

i don' t understand

I tried this (on local Database Server with VCLOGIN):

SELECT * FROM virtualcenter.VPX_VERSION

but, in the log i have ;

...

VMware VirtualCenter-build-147633: 09/10/09 14:56:19 ODBC Error: Microsoft[SQL Native Client|http://communities.vmware.com/community-document-picker.jspa?communityID=&subject=SQLNativeClient][SQL Server|http://communities.vmware.com/community-document-picker.jspa?communityID=&subject=SQL+Server]Invalid object name 'VPX_VERSION'.

I really do not understand anything here

Acidric

Gerrit_Lehr · ‎09-11-2009

I really think this is a permission problem but I'm not that deep into SQL Servers.

Did you check for orphaned users?

What permissions are set for the VC Database?

Kind Regards,

Gerrit Lehr

If you found this or other information useful, please consider awarding points for "Correct" or "Helpful".

Kind regards, Gerrit Lehr If you found this or other information useful, please consider awarding points for "Correct" or "Helpful".

acidric · ‎09-11-2009

I'm not sure it is a permission problem

The owner of database is SA and Virtualcenter account is DBowner on VirtualcenterDB, Master and MSDB

I checked the orphaned user. there is none

The permission for the VC database is Virtualcenter grant 'connect'

Too bad there have not more information in the log file

Anyway, thank you for the help you bring

acidric · ‎09-14-2009

Finally, while I again calmly.

There was an orphaned user. But I tried so many things that I ended up doing anything.

Thank you very much for your help.

Problem solved.

NotAvailable · ‎03-31-2010

Hi everyone,

We had the same problem: after migrating to SQL2005, the service didnt come up; after repair inst. (it did NOT ask whether to re-initialize DB during repair inst!), the DB was empty.

Finally found it: the sql user's default schema has to be the same as it was in sql2000 . Check the DB Table, the string before the first dot is the schema the user has to use accessing the DB.

hope that helps...

All

Virtual Center Database migration sql 2000 2005 data loss