VC 2.5 upgrade - WIPED MY DATABASE!

[eric_kufrin]

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!

[Michelle_Laveri]

Being someone who likes to try and re-try upgrades as part of my documentation process - I usually, shutdown the SQL/VC boxes and take a snapshot of them... Then I bring them back up and try the upgrade - if it fails - I can always revert back the snapshot and try again...

I simply couldn't get a VC2.5 upgrade to work on the RC1. That's why I choose to hold off until the GA... The upgrade would run fine, but then fail with a lack of permissions to run the Upgrade DB wizards. I never did get round the problem... I'm hoping the GA will fix it... otherwise I will be doing a clean install...

snapshots are for free...

Regards

Mike

[team-ip-service]

Hello,

I had the same issue with upgrade from VC 2.01 to VC 2.02.

For me it was quite clear that I lose the hole information in my Database but I though that the VC 2.02 reinit the database that way that at the next upgrade it isn't necessary to reinit at all.

When I considered an upgrade to VC 2.02 update1 I got the same prompt. I guess if I upgrade to VC 2.5 next year I will read the hole doc and will set up a complete new database at all.

Anyway it is always necessary to make backups or if not you should know that it is possible to lose data.

VMWare could/should clearify things in the prompt but it is still your fault.

Markus

[hicksj]

> VMWare could/should clearify things in the prompt

I don't think VMware could get any clearer, see Dermot's post. It is our responsibility as administrators to make the right choice the first time, or to have a backup in the event we screw up. But that never happens, right?

[Michelle_Laveri]

I think what's interesting is the fact that this question EVEN EXISTS...

Surely, if im doing an UPGRADE, I want to KEEP my stuff... If not I'd be doing a clean installation...

Regards

Mike

[hicksj]

Excellent point!!!

[team-ip-service]

Yes that's what I said.

If you don't do backup if you upgrade things then it is you that is reponsible.

From my point of view the prompt in VC 2.0x was quite easy to understand. I don't know if they change it.

Anyway if a relevant number of user go in the trap a delete all there data cause the misinterpretated the prompt -> VMWare should considere to clearify it.

[esiebert7625]

Yes that is a good point, there is a command line switch you can use at anytime if you truly want to wipe your db and start clean.

C:\>"\Program Files\VMware\VMware VirtualCenter 2.0\vpxd.exe -b"

Eric Siebert

VMware Communities User Moderator

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

Visit my website:

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

[AWalsh-cvs]

I am doing my upgrade now. I have not seen the prompt yet, but in the Original Poster's defense.

Let's say he did make a backup. But for some reason the backup was not valid. Then he would be in the same boat as now. I think that's a good enough reason for some kind of blatant warning that you are wiping the database out.

[steven_catania]

I've never had issue in the past with upgrading the VC in both DEV and Test Bed environments before moving on to Production. However this last upgrade went terribly wrong. We too lost the database, yes answering questions correctly. This being said, recreating the VC environment is not cumbersome.

Most troubling now is the SQL elevated permissions needed to upgrade the DB. DBO and or Sysadmin rights on the VC DB AND the MSDB, then they are reverted back. Now the VC Service will not start unless the sysadmin is added back. Anyone see this?

Steve

[jasonboche]

Welcome to the club. I lost my database too (in the lab).

I know what's going on. The original poster didn't answer the question incorrectly. The OP wasn't presented with the question to begin with. The issue is that during the installation process, VirtualCenter doesn't see the populated database, or chooses to ignore the tables because of a bug or a permissions issue and thus never prompts the question "Do you want to initialize the database?". What it does is it uses the same database and decides upon itself to create a parallel set of tables for the new VC 2.5 installation. Believe it or not, your old tables probably still exist in the database. Have your DBA check it out.

Now why VC installation is doing this in some cases is a mystery. I believe this behavior can be provoked if the DSN doesn't have the proper DB permissions, but I followed the instructions giving my DSN user account DBO to the database (he has this anyway), and also gave the account server sysadmin privs.

Jason Boche

VMware Communities User Moderator

[bhargavs]

Eric,

It seems that you are experiencing what I have recently...

WHen I ran the VC 2.5 upgrade, I encountered the same issue. I had access to database server running SQL server 2000 and I Saw that the tables were duplicated. My original data was not wiped but new tables were empty. Virtual Center was reading new tables obviously and showed me empty datacenter with nothing in it.

I uninstalled 2.5 completely and performed a full restore with overwrite on my database. Then I installed VC 2.0.2 back and everything was back to normal.

VMware has identified this as a problem with SA rights on DB. Please check this KB article: 1003346. If you do not give SA on the DB and give only db_owner on VC database and msde database, you should be able to upgrade without problems.

Hope this helps to all who are experiencing same issue.

I am disappointed in VMware support however as I opened critical case 2 days ago and i have no response from them on this issue. Luckily i found this article and I am back in business but how can Premium support drop critical case for 2 days!!! Maybe that's topic for another day.

Thanks,

Bhargav

Message was edited by: jasonboche

Go to http://kb.vmware.com/kb/1003346

[jasonboche]

I've never had issue in the past with upgrading the VC in both DEV and Test Bed environments before moving on to Production. However this last upgrade went terribly wrong. We too lost the database, yes answering questions correctly. This being said, recreating the VC environment is not cumbersome.

Most troubling now is the SQL elevated permissions needed to upgrade the DB. DBO and or Sysadmin rights on the VC DB AND the MSDB, then they are reverted back. Now the VC Service will not start unless the sysadmin is added back. Anyone see this?

Steve

Yep

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

Jason Boche

VMware Communities User Moderator

[jasonboche]

WHen I ran the VC 2.5 upgrade, I encountered the same issue. I had access to database server running SQL server 2000 and I Saw that the tables were duplicated. My original data was not wiped but new tables were empty. Virtual Center was reading new tables obviously and showed me empty datacenter with nothing in it.

I uninstalled 2.5 completely and performed a full restore with overwrite on my database. Then I installed VC 2.0.2 back and everything was back to normal.

VMware has identified this as a problem with SA rights on DB. Please check this KB article: 1003346. If you do not give SA on the DB and give only db_owner on VC database and msde database, you should be able to upgrade without problems.

Hope this helps to all who are experiencing same issue.

Nice find. That is the solution to the problem (at least in my scenario). But what a cludgey fix... Good Lord.

Jason Boche

VMware Communities User Moderator

[steven_catania]

Thanks Jason. I will re-evaluate before moving to production. Is it safe to say I can blow away the DB, start over and only have DBO on the DB? Or do I still need access to the MSDB.

my end result is not to have sysadmin access at all and do a new install.

[jasonboche]

Thanks Jason. I will re-evaluate before moving to production. Is it safe to say I can blow away the DB, start over and only have DBO on the DB? Or do I still need access to the MSDB.

my end result is not to have sysadmin access at all and do a new install.

The KB article says the db_owner role is needed on the MSDB database during installation and upgrade only. Before upgrading, I had granted DBO to the VC database and granted sysadmin but somehow I missed the part where I needed to grant DBO to MSDB and that was my failure.

If you want to nuke your database and start over, that's your business, but if you have a DBA or if you have DBA skills yourself, you can probably recover the old tables in your existing database before you nuke it. My test lab datacenter structure was pretty simple so I immediately cut my losses and rebuilt using a fresh database. But this will be a much much different story when I get to production and I have a boatload of things stored in the database such as permissions, alarms, clusters, rules, blah blah blah blah. Nightmare to have to rebuild that environment. It's easy to spin up a datacenter pretty quickly and add hosts. It's all those little tweaks that take a while to get put back in place.

Jason Boche

VMware Communities User Moderator

Message was edited by: jasonboche

[bhargavs]

Jason,

I think Steven meant MSDB database in SQL server and you are confusing that with MSDE? Steven is correct in saying, you need db_owner on both VC database and MSDB but avoid giving SA to entire SQL instance. This will allow smooth upgrade or install without opening entire SQL server to VC database user.

Thanks,

Bhargav

[jasonboche]

Yeah I got that turned around. Thanks for catching. I'll fix my post to cut out the mis-information.

Jason Boche

VMware Communities User Moderator

[bhargavs]

No problem. I also agree with your earlier post in reply to steven that it is always good to have backup BEFORE the upgrade in production environments (or any environment for that matter). Backup saved my day in the end because I quickly reverted back to backup and then redid the upgrade.

Thanks,

Bhargav

[Michelle_Laveri]

I'm no MS SQL expert myself - and have occasionally had problems with upgrades from 1>2. My problem in my 1>2 upgrade was I used Windows Authentication on a remote SQL which although works (if you know how to manage services and grant "logon as a service" and so on) is not supported by VMware, and doesn't work with upgrades/upgrade DB wizard.

This time out on the RC1, I found I got this "Insufficent Permissions" message on quick test of VC 2>2.5 upgrade. Permissions hadn't changed and had in the past been able to do 2.0.0>2.0.1>2.0.2 upgrades with very little pain. This time it turned out to be a problem. Interestingly, with clean installations of VC2.5 i used the SAME perms on SQL that I had for clean installs to 2.0.x. So from this it seems to indicate that UPGRADES and the upgrade DB wizard needs "additional" permissions to work properly - permissions that are NOT required during a clean install. Hmmmm...

I never did get to the bottom of these access denieds. One thing I did try was trying to play with SysAdmin rights to try increase the permission. Interestingly, I stumbled across this issue before knowning about the KB. It just didn't do an upgrade at all. I didn't get the prompt and I got clean DB. Just as the KB articles says. I wasn't aware that the old DB tables were still there. Fortunately, it wasn't a problem as I'd snapshoted (cold) both the SQL VM and the VC VM....

Anyway. I'm loosing touch with this thread because of the terms that people are using such MSDB (you just mean the Microsoft SQL?) and DBO... So for clarity sakes - what are the right permission for SQL 2000 to be upgraded with VC 2.5

All have is an account called domain/vcdb with db_owner and db_public. Are we saying additional permissions need to be allowed for upgrades to work?

Regards

Mike

[Dave_Mishchenko]

> So from this it seems to indicate that UPGRADES and the upgrade DB wizard needs "additional" permissions to work properly - permissions that are NOT required during a clean install. Hmmmm...

That would seem to be the case. A clean install will still check that the login has DBO rights in the MSDB database, but when I ran a SQL trace on a clean install there was no activity in MSDB beyond the initial permissions check.

Looking at the KB article and specifically this part below, it makes sense as to why it seems that the database is "wiped". When you have a SQL user with the role db_owner, objects created by that user are created with the user's id as part of the full object name so the full object name for the sql login VC, would be server.database.VC.table_name. When you grant a login the sysadmin role, they have complete rights to the server and they have access to every database as the DBO user, which is distinct from the dbo_owner role. So any objects then would be created as server.database.DBO.table_name. One of the posters on Jason's thread stated the following: "Looking at the database, it looks like there were quite a few new tables created during the install, in addition to the existing tables from the original install. " Im guessing here, but he may have ended up with a complete schema for a VC version 4 database and a version 3 database all within the same databese. If the login retained the sysadmin role when the VC service was started, it would access the DBO.V* tables and thus the version 4 database. Once you remove that role, it would accessing the VC.V* tables which would be a version 3 database. That would seem to be confirmed in Jason's original post http://communities.vmware.com/message/820079 where he had the error "Database version id '3' is incompatible with this release of VirtualCenter." after he revoked the sysadmin role.

> From the KB article -- If you are upgrading from a previous release of VirtualCenter that uses Microsoft SQL Server 2000, and the database user does not have the proper permissions, the installer notifies you that you do not have the required permissions. If you try to resolve this problem by granting the System Administrators role to the user, the installer performs a fresh installation of VirtualCenter instead of an upgrade.

Basically, if you just grant the login the db_owner role in the MSDB database, then you won't have this issue. So for both a new install or upgrade, the SQL login just needs to have the db_owner role in the VC and MSDB databases. The sysadmin role isn't needed. The MSDB is a system database, it's not a typo for MSDE. It stores information on replication, sql agent jobs, backup history and other system data.

Now what the database is doing in the MSDB during an upgrade is a good question.