VMware Cloud Community
eric_kufrin
Contributor
Contributor

VC 2.5 upgrade - WIPED MY DATABASE!

Upgrading to VC 2.5 wiped my database!!

I bring up VC and ALL OF MY HOSTS AND CONFIGURATION IS GONE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I am so mad right now!

This is the second time a VC update has wiped my database!

Seriously, WTF!

Reply
0 Kudos
85 Replies
Michelle_Laveri
Virtuoso
Virtuoso

It is the problem.

The problem was i didn't realise that there was actually a DB called MSDB... VC has never before need rights to this DB before for an upgrade...

Regards

Mike

Regards
Michelle Laverick
@m_laverick
http://www.michellelaverick.com
Reply
0 Kudos
jasonboche
Immortal
Immortal

http://www.vmware.com/pdf/vi3_35/esx_3/r35/vi3_35_25_installation_guide.pdf

Page 69

The db_owner role on MSDB is for the new installation/upgrade process only. This role can safely be removed after the new installation/upgrade is completed.

Jason Boche

VMware Communities User Moderator

VCDX3 #34, VCDX4, VCDX5, VCAP4-DCA #14, VCAP4-DCD #35, VCAP5-DCD, VCPx4, vEXPERTx4, MCSEx3, MCSAx2, MCP, CCAx2, A+
Reply
0 Kudos
Michelle_Laveri
Virtuoso
Virtuoso

Ah, now that is interesting. You see under the RC1. I successfully did a clean install - without the MSDB set - and I didn't have a problem. I only had a problem with UPGRADES.... I'll definitely be checking this out in the New Year - because I could have an error in my RTFM guide which makes NO mention for this MSDB permission requirement...

Regards

Mike

Regards
Michelle Laverick
@m_laverick
http://www.michellelaverick.com
Reply
0 Kudos
bhargavs
Contributor
Contributor

I also tested this with SQL 2005 and it needs db_owner on VCDB and MSDB even for new install.

Reply
0 Kudos
steven_catania
Contributor
Contributor

We attempted an upgrade in our production environment yesterday using what we learned from Test Bed and DEV and the Database still got hosed. We wiped it and started over. The DB(s) were given proper permissions and all is well on a clean install.

The upgrade procedure on SQL is not working well at all.

Steve

Reply
0 Kudos
esiebert7625
Immortal
Immortal

There are 8 sql files that access the MSDB database as part of the VC inctsall, they are:

c:\program files\vmware\infrastructure\virtualcenter server\job_schedule1_mssql.sql 11/16/2007 8:50:38 PM 3325

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Stats Rollup') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'Stats Rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Day stats rollup'

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

c:\program files\vmware\infrastructure\virtualcenter server\job_schedule2_mssql.sql 11/16/2007 8:50:38 PM 3390

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Stats Rollup') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'Stats Rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Week stats rollup'

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

c:\program files\vmware\infrastructure\virtualcenter server\job_schedule3_mssql.sql 11/16/2007 8:50:38 PM 3375

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Stats Rollup') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'Stats Rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Month stats rollup'

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

c:\program files\vmware\infrastructure\virtualcenter server\vcdb_undo_mssql.sql 11/16/2007 8:50:38 PM 44895

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Month stats rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Week stats rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Day stats rollup'

c:\program files\vmware\infrastructure\virtualcenter server\dbupgrade\upgrade-v3-to-v4\t-sql\job_schedule1_mssql.sql 11/16/2007 8:50:40 PM 3090

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Stats Rollup') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'Stats Rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Day stats rollup'

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

c:\program files\vmware\infrastructure\virtualcenter server\dbupgrade\upgrade-v3-to-v4\t-sql\job_schedule2_mssql.sql 11/16/2007 8:50:40 PM 3107

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Stats Rollup') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'Stats Rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Week stats rollup'

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

c:\program files\vmware\infrastructure\virtualcenter server\dbupgrade\upgrade-v3-to-v4\t-sql\job_schedule3_mssql.sql 11/16/2007 8:50:40 PM 3105

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Stats Rollup') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'Stats Rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Month stats rollup'

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

c:\program files\vmware\infrastructure\virtualcenter server\dbupgrade\upgrade-v3-to-v4\t-sql\upd_rollback_proc_mssql.sql 11/16/2007 8:50:40 PM 11052

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Month stats rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Week stats rollup'

FROM msdb.dbo.sysjobs

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Past Day stats rollup'

Eric Siebert

VMware Communities User Moderator

-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-

Visit my website:

-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-

Reply
0 Kudos
YoMarK
Contributor
Contributor

Also just wiped my database on SQL2000?!?!?!

Reply
0 Kudos
jhanekom
Virtuoso
Virtuoso

I think it's not so much a case of the database being wiped as it is a case of the upgrade process not completing successfully. When the v3 --> v4 upgrade wizard runs, it copies all the data from all the user tables in to "tablename_bk" as a backup. It then updates the schema of the database (effectively adds a few tables, alters the data structure of existing tables, etc.)

The next step is copying the data from the _bk tables back into the main tables. It's probably here that things are going amiss.

There is a comprehensive log file for the entire database migration process. It's called "VCDatabaseUpgrade.log" and is stored in the temporary folder of the user account that upgraded VirtualCenter. (Tip on how to get to it real quick: go to Start, Run and enter - without the quotes - "%TEMP%\VCDatabaseUpgrade.log".)

Unfortunately the log file is just the output of the SQL commands that were run, so might not give a straight-forward answer as to why things failed. However, it will provide some information that will help you troubleshoot where things went wrong.

Reply
0 Kudos
YoMarK
Contributor
Contributor

No, this is not the case.

During installation i've used the virtualcenter SQL account, then the installer mentioned that there was an old VC database found. Next thing it mentioned is that the user doesn't has the right permissions.

So i've pushed previous and used the sa account(and changed the DNS so that "sa" used the VC database as default).

However, when I pushed next, it did not mention that there was an old VC DB found. It did however mention that SQL server agent must be running.

Also no database upgrade was started, it just wiped the DB.

Now i've restored a backup(from VC 2.0x) of the DB, and made the virtualcenter SQL user also db_owner on the msdb database(as metioned in http://www.vmware.com/pdf/vi3_35/esx_3/r35/vi3_35_25_installation_guide.pdf ). This time it does continue until the database upgrade, but to my frustration it stops with an error.

The error should be inside an errorlog, but even that does not seems to work, because there is no logfile in de specified directory!

(in C:\Documents and Settings\ADMINI1WSD\LOCALS~1\Temp )

If i ever saw this kind of behaviour with other software, I would kick it right out the company. Unfortunatally, can't do that with Virtualcenter with our 12 ESX hosts(the hosts work great however).

Reply
0 Kudos
jhanekom
Virtuoso
Virtuoso

Ah, OK.

You say it stops with an error, and that there is no indication of what the error is? Not even a number?

On your second try, are you still using the sa account, or have you reverted to the (now elevated) virtualcenter account? Just a hunch, but if the tables are owned by a different user in the database (other than dbo), things could be a bit messy. If you have access to SQL Management Studio, the easiest way to check this is to expand the database, then expand the list of tables, and see whether the prefix to each table is "dbo." or "virtualcenter." (or whatever your VC SQL account is called.)

Reply
0 Kudos
YoMarK
Contributor
Contributor

jhanekom: thanks for the input!

Used the virtualcenter SQL account.

I've know removed VirtualcenterServer(and other) as mentioned in this document: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=100334...

Rebooted the server, and made sure the VirtualCenter SQL account had the right permissions. Also checked if the SQL account had the right prefixes on the SQL tables. This looked al good.

Again restored the backup from the VC database(2.0x).

Now i'm back in the database upgrade window, where it sais "Upgrading database from version 3 to version 4" for a few minutes now (preserving all data).

Hopefully it goes right this time.

Nope Smiley Sad

Error log is in the attached file.

Reply
0 Kudos
YoMarK
Contributor
Contributor

Removed VC server 2.5, restored old DB, and installed 2.02 again.

Also restored the SSL certificates in "C:\Documents and Settings\All Users\Application Data\VMware\VMware VirtualCenter\SSL".

All is working again at the old version.

It seems that the upgrade procedure on SQL 2000 just isn't working correctly.

Looks like i'll have to do a fresh start next week if i have the time. Smiley Sad

Reply
0 Kudos
Michelle_Laveri
Virtuoso
Virtuoso

OK,

Now I have db_public & owner on the VCDB...

You say "MSDB"... what do you mean by this - do you mean the "master" db?

I wasn't aware this was used by VMware...

Forgive me for my SQL ignorance...

I a friend who says we can use the "Sql Profiler" to see what access allowed/denieds are...

Regards

Mike

Regards
Michelle Laverick
@m_laverick
http://www.michellelaverick.com
Reply
0 Kudos
Dave_Mishchenko
Immortal
Immortal

The msdb is a seperate system database from master. It contains backup history, alert setup, scheduled jobs. Looking at what Eric has posted in this thread it looks like they are adding some SQL jobs, but as I look at the actual script it contains the below clause, which basically causes it to bypass the creation of the jobs for SQL Express. That would explain why earlier I had seen no msdb activity by the upgrade process as I was running SQL Express. The Express edition lacks the SQL Agent service which would on the other SQL editions take care of running scheduled jobs.

http://communities.vmware.com/message/824151

IF ((select serverproperty('edition')) = 'Express Edition')

GOTO EndSave

Reply
0 Kudos
Dave_Mishchenko
Immortal
Immortal

I should add that regardless of the SQL edition you run, the upgrade program will check your rights in the MSDB database regardless of the edition you're running. Thus you have to grant the SQL login the dbo role in the MSDB even on the Express edition, even though the install process will not need to access the DB.

Reply
0 Kudos
Dave_Mishchenko
Immortal
Immortal

It looks like it is having some problems with jobs in MSDB. If you look at SQL agent \ jobs do you see for jobs with STATS in the name? Might be worth it to delete those first. Which edition of SQL 2000 are you using?

Reply
0 Kudos
jhanekom
Virtuoso
Virtuoso

It does look like a previous install didn't fully roll back.

In case you were wondering, all editions of SQL 2000 (including MSDE) came with the SQL Agent service.

Reply
0 Kudos
dlm1975
Contributor
Contributor

This is the crux of this issue. Why does this option even exist? Surely, VMWARE can concede the fact that if there is a database prior to install, this is indeed an upgrade and not prompt the admin with this needless question/step? Or at least, have the "(recommended)" option be to not initialize and have that be the default choice checked as well?

Reply
0 Kudos
margitza
Contributor
Contributor

I've got exactly the same problem as you described (compared your VCDatabaseUpgrade.log). I've down it with and without preserve of events and tasks and performance data, same result.

Please let me know when you have any new information and so do I. Seems to me that we are testing a buggy software.

Reply
0 Kudos