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
steven_catania
Contributor
Contributor

Problem is now when I remove the sysadmin role I cannot start the VC service. let me state our service is an NT account. I have read that NT authentication is not prefered for the SQL connection (SQL is not local its in a clustered environment). Do I need to start over with a clean DB, and use SQL Authentication? Our SQL DBAs do not want to leave us as sysadmins much longer but that the only way I can keep the VC service running.

Steve

Reply
0 Kudos
doubleH
Expert
Expert

Guys,

Is this problem only related to SQL 2000 and NOT SQL 2005 ?? The kb article points out SQL 2000, but doesn't mention SQL 2005.

Thanks

If you found this or any other post helpful please consider the use of the Helpfull/Correct buttons to award points
Reply
0 Kudos
jasonboche
Immortal
Immortal

Problem is now when I remove the sysadmin role I cannot start the VC service. let me state our service is an NT account. I have read that NT authentication is not prefered for the SQL connection (SQL is not local its in a clustered environment). Do I need to start over with a clean DB, and use SQL Authentication? Our SQL DBAs do not want to leave us as sysadmins much longer but that the only way I can keep the VC service running.

Steve

What you need to know about VirtualCenter database setup and configuration starts on page 66 of the following document:

Page 68:

"Microsoft Windows NT authentication is not supported with remote SQL Server"

= If you're running VirtualCenter on the SQL server, you can choose either NT authentication or SQL authentication.

= If your VirtualCenter and SQL server are running on separate boxes, only SQL authentication is supported (although it may work as you and Mike Laverick have explained, you're roaming in unsupported territory. What is best for your business?)

My recommendation would be to follow the supported path.

An additional upgrade requirement can be found on page 53 in this other document:

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

Page 53:

"If you are upgrading a SQL database, you must first enable bulk-logging in the database."

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
Sangokan
Enthusiast
Enthusiast

Fellows,

i have to run the upgrade too. I have a sql server 2005 and a VC server, separate boxes. I read that SQL Express is supported for small environments which is already a big change to have a free database product supported for production. Now i want to move to SQL Express because my IT buddies are complaining about the size of the VC db, actually it is right now almost 3Gb of data, and the sql server is almost full, no more free space. I made a backup of the VC db and will restore it inside SQL Express on the same box as where the VC service is running.

Now i am not sure, the max. size for dbs in SQL Express is for 4Gb, during the install (i made a test) the prompt says it will need about 5100 Mb of free space, i dont know if it's for the db only or the new features like the update manager, the converter...

What is weird is that i dont use a lot of Alarms, users, tasks, custom attributes, i have just created about 10 ressource pools to make vservers more easy to locate and administer. So considering i have a 2,5 Gb db plus 300 MB of logs, will i go the wrong path choosing SQL Express?

Sorry for breaking the permissions thread issue, i'd still like to know what the effective permissions needed are of course.

Reply
0 Kudos
jasonboche
Immortal
Immortal

You are going to let your IT buddies move you off the highly scalable but disk constrained SQL platform and put your VI3 environment at risk because they won't address a capacity issue on the SQL server? Which branch of the US Federal Governement did you say you worked for? ?:|

As you have already eluded to, SQL Express is not nearly as scalable as SQL Server.

Unless you are willing to significantly trim some of the VirtualCenter components and performance reporting intervals that contribute to the aggregate size of the back end database, you need to stay on SQL Server, or tell your IT buddies the alternative is to open the wallet and move to Oracle Server.

Jason Boche

VMware Communities User Moderator

Message was edited by: jasonboche

Wording change

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

jason - looking at the compatibility matrix sql server 2005 express is supported in a production environment with a max of 5 hosts and 50 vm's.

sangokan - have you truncated your transactions logs? these tend to get large if they are not truncated during a full backup.

If you found this or any other post helpful please consider the use of the Helpfull/Correct buttons to award points

If you found this or any other post helpful please consider the use of the Helpfull/Correct buttons to award points
Reply
0 Kudos
jasonboche
Immortal
Immortal

jason - looking at the compatibility matrix sql server 2005 express is supported in a production environment with a max of 5 hosts and 50 vm's.

My point was that sql server 2005 express isn't going to scale to meet his needs in terms of database size requirements, not that sql server 2005 express isn't supported.

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
doubleH
Expert
Expert

huh....i guess i misinterpreted your statement ..."You are going to let your IT buddies move you off the supported SQL platform" insinuating that SQL Express is not supported.

If you found this or any other post helpful please consider the use of the Helpfull/Correct buttons to award points
Reply
0 Kudos
jasonboche
Immortal
Immortal

Fixed - Thanks

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
steven_catania
Contributor
Contributor

All:

Update to SQL permission issues for the 2.5 upgrade/install

I blew away the VC Database and only had db_owner on the VC database and the MSDB. NO SYSADAMIN. Uninstalled VC and all other components except for the License Server. Recreated the DSN using SQL authentication not NT. Then I reinstalled the VC from the beginning and had no issues whatsoever. All the pluggins are available and the connection satisfies the DBAs as there is no elevated permissions at all. And oh yes, the db_owner was removed from the MSDB.

Everything is working as it should.

Good luck to all.

Steve

Reply
0 Kudos
esiebert7625
Immortal
Immortal

You can dramatically change the size of your SQL database by changing the statistic collection level. If you don't care about old statistics you can disable all together or increase the sample rate for daily, weekly and monthly statistics. You can also purge old event and task data manually. This data is kept indefinitely and not purged automatically by VC.

http://vmware-land.com/Vmware_Tips.html#VC4

http://vmware-land.com/Vmware_Tips.html#VC5

Eric Siebert

VMware Communities User Moderator

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

Visit my website:

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

Reply
0 Kudos
Michelle_Laveri
Virtuoso
Virtuoso

All:

Update to SQL permission issues for the 2.5 upgrade/install

I blew away the VC Database and only had db_owner on the VC database and the MSDB. NO SYSADAMIN. Uninstalled VC and all other components except for the License Server. Recreated the DSN using SQL authentication not NT. Then I reinstalled the VC from the beginning and had no issues whatsoever. All the pluggins are available and the connection satisfies the DBAs as there is no elevated permissions at all. And oh yes, the db_owner was removed from the MSDB.

Everything is working as it should.

Good luck to all.

Steve

I have no problem with perms with a clean install. What I don't understand is what are the correct perms for an upgrade. I don't understand why all my attempts at upgrading in RC1 resulted in the access denied message mentioned in the KB...

Anyone done and upgrade that worked - and if so - what permissions (EXACTLY) were you using. Bear in mind that some of us (myself included) are SQL novices...

Also is this an issue specifically with SQL2000 or does it affect SQL2005 as well?

Regards

Mike

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

Mike,

Here is what you do to upgrade successfully:

In SQL 2000, check the user who has access to the VC database and make sure it has db_owner permissions on VC database and MSDB database.

You can do this by going to Enterprise Manager and click Security -> Logins. Select the VC User and go to properties, go to Database Access tab. Scroll down the list of databases to select VC database (whatever you named it) and select public and db_owner roles. Do the same for MSDB database in the list.

I have no specific experience with SQL 2005 but it seems this problem is limited to SQL 2000.

Thanks,

Bhargav

Reply
0 Kudos
jhanekom
Virtuoso
Virtuoso

Continuing off-topic... In addition to reducing the size of a VC database by reducing the statistics collection level, you can also dramatically reduce your database size by upgrading to at least VC 2.0.1 Patch 2 (or preferably VC 2.0.2 Update x.) The optimised way this version of VC rolls up data reduces the database and transaction log sizes by almost an order of magnitude.

I've had a look at the VC 2.5 statistics rollup stored procedures and DB structure, and VMware seem to have re-architected the thing entirely. The new way of doing things should speed up performance of the statistics graphs tremendously for cases where regular index re-orgs are not done (i.e. the majority of installations), as they now seem to store different levels of summarised data data in four separate tables.

If I understand it correctly, this eliminates the need to do roll-up (summarise) inside of the same table by calculating - for example - 30 minute averages from 5 minute samples, inserting the new values and then discarding the 5-minute samples, as was the case pre-VC 2.5. This, in turn, allows the indexes (mostly clustered anyway) to stay fairly well sorted.

Reply
0 Kudos
Michelle_Laveri
Virtuoso
Virtuoso

Mike,

Here is what you do to upgrade successfully:

In SQL 2000, check the user who has access to the VC database and make sure it has db_owner permissions on VC database and MSDB database.

You can do this by going to Enterprise Manager and click Security -> Logins. Select the VC User and go to properties, go to Database Access tab. Scroll down the list of databases to select VC database (whatever you named it) and select public and db_owner roles. Do the same for MSDB database in the list.

I have no specific experience with SQL 2005 but it seems this problem is limited to SQL 2000.

Thanks,

Bhargav

OK, now i have the db_public and db_owner on the VC db... What's do you mean about MSDB...? Do you permission on the "master" database too?

Regards

Mike

Regards
Michelle Laverick
@m_laverick
http://www.michellelaverick.com
Reply
0 Kudos
Hairyman
Enthusiast
Enthusiast

I think MSDB = Master

Cheers

Aaron

Reply
0 Kudos
jhanekom
Virtuoso
Virtuoso

msdb <> master.

Both are system databases (i.e. required for basic operations of SQL Server, though master is much more important), but they're not one and the same.

The VC upgrade requires db_owner rights to msdb. It does not require any access to master at all. If I remember correctly, for SQL 2005, you can adjust this by obtaining the SQL user's properties and granting the appropriate priviledges on the "name mappings" page.

Reply
0 Kudos
bhargavs
Contributor
Contributor

Mike,

No you don't need anything on master database. If you look in the list of databases, you will see a database called MSDB. Just assign permissions as db_owner on that. This is required only for upgrade. Once upgrade is complete, you can remove it from MSDB.

Thanks,

Bhargav

Reply
0 Kudos
hicksj
Virtuoso
Virtuoso

Wow. After catching up on the last several pages of this thread, I'm glad we're running Oracle. Smiley Wink

Reply
0 Kudos
esiebert7625
Immortal
Immortal

Me too Smiley Happy

Oracle does also need special permissions for the 2.5 upgrade...

Oracle Database

For Oracle databases, either assign DBA role to the user, or grant the following permissions to the user:

grant connect to (user)

grant resource to (user)

grant create view to (user)

grant create any sequence to (user) # For VirtualCenter upgrade only

grant create any table to (user) # For VirtualCenter upgrade only

grant execute on dbms_job to (user)

grant execute on dbms_lock to (user)

grant unlimited tablespace to (user) # To ensure space limitation is not an issue

Eric Siebert

VMware Communities User Moderator

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

Visit my website:

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

Reply
0 Kudos