vCenter 2.5 U3 DB move from SQL Express 2005 to SQ...

alvinswim · ‎12-05-2008

I've recently moved my Vcenter database from SQL express (local) to one of our 4-way clusters. I pretty much did the usual detach and reattach while the vc services were offline.

I also did some DB stuff like, update statistics, etc, AND I went ahead and created the 3 SQL agent jobs for statistics rollups that were part of another VC 2.5 installation we have and were missing from this vc setup because I had installed it with SQL express..

Since I've moved the database over, every time i look at perf stats for the cluster, graph results aren't returned for at east 5-10 mins, and then during that time a query on the DB side runs and takes up 25% of cpu on the DB cluster.. INSANE! and that cluster has 10 Fiber drives for the data partition too.. eventually the graph shows up, but thats after 5-10 mins of resource hogging on a 4CPU cluster...

Then I decided that maybe my data was corrupt, so I went through procedures to purge stats data as per the vmware documentation, that didn't help. and by the end of the day, the perf stat graphs all took a long time to return, and used up significant amounts of CPU and time.

so my question here is, do any of you out there have any idea how I would fix this issue? other than "reinstall Vcenter"..

any help is much appreciated

thanks

alvin

RParker · ‎12-05-2008

SQL server is pretty uncomplicated when it comes to this stuff, and since VC is spiking I would say it was because of the method you used to move the data.

A simple dump (export) and import into the new table should work. There are a few differences between the structure of a SQL Express table and SQL 2005/2008 Standard table If you have to you can simply tell your core SQL servers to point to the SQL Express instannce and import direct across the network. If you can't get this to work, I highly advise you to consult a DBA, because you could end up really corrupting your data.

Also the method should be since you are creating the new VC DB instance is to allow VC to create the table (fresh) and then import the table data, which is why I say use export and not copy the entire table.

And just curious why did you ignore the recommendations in the first place and go with SQL Express? Because it's always been advisable to use SQL Server or Oracle and not use Express, because as you no doubt have seen it's a pain to do this after that fact..

Not picking on you, but this is a forum this might be lesson learned for those people out there that are using SQL Express now, and why they should it the preferred way from the beginning.

alvinswim · ‎12-06-2008

Thanks RParker,

I think the best course of action for us here is to just reinstall VC server, we've only had our system in Production for about a week.

What's the best way of reinstalling VC without disrupting the VMs?

I was thinking that I would remove each of our hosts from the cluster we set up, so they are standalone managed hosts, then I would reinstall just Virtual center, attaching to our SQL2005 STD database server directly. I would leave the license server and the rest of the bits and pieces untouched, then once VC is up again, I'll readd the hosts to VC and recluster them...

does that sound right?

Thanks in advance

alvin