Hi guys,
Here is my problem. . About every hour or two Virtual Center will stop responding for at least one minute typically two. If you check task manager performance on the VC server SQLservice is consuming 50% of the CPU. I am running a local SQL instance for the VC. I run virtual center on a server that at one time also ran Veeam Backup server. The server runs nothing else. I have no idea on what SQL is trying to do during this period. I have used the SQL studio to check if there is some kind of compression job running against the DB or something but I dont see anything there.
Does anyone have any ideas? Is it possible to move the virtual center local database to a dedicate SQL server without reinstalling?
yes it is possible, detach the db and re-attach to your dedicated sql server.
Once you do this, modify your DSN (in ODBC connections) to point to the new sql server
When is the last time you've backed up your database (commit transaction logs)
yes it is possible, detach the db and re-attach to your dedicated sql server.
Once you do this, modify your DSN (in ODBC connections) to point to the new sql server
When is the last time you've backed up your database (commit transaction logs)
I have no idea on what SQL is trying to do during this period.
SQL does routine maintenance during this window, it optimizes databases, clean up for dead space in tables/queries and some other things to ensure proper optimizations. Primary reason why SQL needs to be a separate machine, not only for CPU but Disk IO. SQL can bring other things to a crawl, such as vCenter. But putting SQL on a separate machine will make things much better.
I dont typically back it up. However the logs didnt seem to that large. I have performed a full backup just now and that did clear up the logs. I will let it run for a bit and see how it manages. If it continues to hammer the cpu I am going to try moving the database off server and stop the local SQL and see how it runs. I am guessing I need to stop the virtual center services before detaching the database and moving it? Is there anything I need to be aware of before doing so?
See thats the thing. I am aware of the routine maintenance SQL typically runs but the database is small (300mb) so I wasnt expecting a drastic performance hit. Likewise I disable the database resizing and optimization options for the select db and there are no actual jobs configured against in the maintenance section. Mind you this is the local/express edition so of course I am limited. I think I will end up moving the database to a stand alone sql server. This is one of those started out small as a test project and made its way into production.
My previous reply still stands though. Anything imperative to watch out for when offloading the database aside from the odbc connection updates?
Thanks guys.
Anything imperative to watch out for when offloading the database aside from the odbc connection updates?
Well when you move the data, document ALL the security permissions you have to apply them to the new location. But other than that, none. SQL is pretty straightforward otherwise.
Thanks. I bit the bullet and just shifted the database now and brought it back online in the new location with upgraded odbc settings and its running again. I figured it was going to be a little more work then that.
Thanks for the help guys.
Don't forget to rebuild all indexes after moving a database. The most important ones are those on the columns in the following tables because they are usually quite big: VPX_HIST_STAT1-4, VPX_SAMPLE_TIME1-4, VPX_EVENTS and VPX_EVENTS_ARG.
Thanks I've indexed the mentioned tables.