SQL server production virtualization – to do or not to do

5 pts.
Tags:
Database
Database administration
Enterprise Virtualization
SQL Server Virtualization
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?

Please advise.

Thanks,

Nikki



Software/Hardware used:
sql server 2005, Enterprise version, OS - 32 bit, 32 gb memory, 4 quad core processors, 3.4 ghz speed, Biggest DB size 300 GB
ASKED: December 15, 2009  10:06 PM
UPDATED: December 16, 2009  9:18 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Typically large high transaction SQL Servers are not typically good candidates for virtualization. First when you virtualize a server you can’t assign as more cores to the VM as you currently have. The most cores you can assign are the number of cores on a single physical CPU. So if your VMware server has 4 quad core CPUs each VM can have access to only 4 cores at most.

So if your SQL Server is running up to 90% CPU load on 16 cores, running the machine on 4 cores kills the idea of moving it to a VM.

In this case the SQL Server should be moved to an Active/Passive cluster on physical servers and not be virtualized.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 1craig
    We've virtualized (vmware ESXi) all of our production database applications, but haven't virtualized all of our database servers. One of the database servers had many databases on it and we had to create a number of VMs to spread the load across them. Also we had a problem with the io load on the SAN lun for the datastore and had to increase the number of physical drives at the SAN
    60 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following