I am being pushed to say yes for virtualizing our production server. We have already moved all our application servers, developement, stage and qa database environments to the virtualized environment (VMware). (These environments are not nearly the same size or power as production, can barely funtion at fraction of the actual activity in production. )
The databsee server in question has 32 GB memory right now but it has processes that can benefit from 48 GB or 64 GB memory. It has 4 quad core processors 3.40 GHz.
There are about 80 databases on this server supporting everything that we have: our business -file processing from clients, generating and fulfilling their orders, our actual production warehouse operations, shipping system, BIlling and GL, reporting and databases supporting our customer facing websites.
I see I/O subsystem pinch time to time and lately I have been seeing different wait types like resource_semaphor or scheduler_yield_SOS etc along with Pagelatch and Lck_M_x etc.
My concern is that first we have lot of tuning to do in our sql server. I am the first DBA in this company for last few months while the sql server has been in this company for over 10 years now.
These are the issues I see need to be worked on first:
1. Developres have created the designs over the years with no clustered indexes, massive queries joining 15-20 tables and group bys and comparisons with count(*)s in subqueries in where clause etc.
2. Some processes make CPU spikes to 50-60% sometimes over 90% and stays there until the process is done ( 2-3 hours ) and then returns to normal 18-25%.
3. there is no maintenance window defined. So, if reindexing or updatestats cause little slowdown, there is a lot of chaos.
I am not against virtualizing database but in this case we are not justifying the purpose of virtualizing as there is no way this box can be 'considered' underutilized. Two things we can gain by virtualizing are DR solution, which we don't have now. Also, database files can be moved to faster disks in SAN from local storage now.
I have to give my yay or nay by this weekend. I can not decide between say yes to virtualizing the database server or propose clustering solution with actual physical boxes and faster disks in SAN. I don't see us doing a load test before switing to virtualized environment and if it does not work, it will be a nightmare to switch back to the original production server
Have DBAs been ok with virtualizing their main critical high transactional database systems? Am I the only one still with reluctance about virtualizing the bucket where all our eggs are?
sql server 2005, Enterprise version, OS - 32 bit, 32 gb memory, 4 quad core processors, 3.4 ghz speed, Biggest DB size 300 GB
December 15, 2009 10:06 PM
December 16, 2009 9:18 PM