VMworld
Up to Discussions in Application Virtualization
2 Replies Last post: Oct 3, 2007 8:03 AM by Benny Hauk  
Click to view mfleener's profile Candidate
mfleener
1 posts since
Sep 10, 2007

Sep 18, 2007 10:09 PM

Best practices for SQL in VI3


I'm getting tremedious push back from our DBAs on running SQL inside a VM. Thier main arguments are that: SQL on a windows box does a better job of handeling query managment. ie, 30 1 second queries and one large query where to run at the same time they would somehow happen serially in VMware, but not in Windows? I don't even know if this is true. To be honest my only real issue has been SMP and I see this as a non issue espeicaly if I use processor affinity and give any SQL box exclusive cores, so we can avoid ready time (excluding CPU 0 of course). We have large gaps between my knowledge of VMware, and thier knowledge of SQL. No single DB we have is larger then 6GB, our IOPS are low and we have no memory pressure in our SQL 2005, or SQL 7 boxes. All current SQL boxes are 32bit.

At the show I asked for white papers on the subject but have not been able to find anyting of value. Any help would be greatly apprecited.

BTW, all these boxes (SQL 7, W2K Advanced, SQL 7W2K Advanced, SQL 2005, W2K3ent) are currently running in VI3, all on a FB SAN, with very high IOPS. All SQL boxes have 4GB RAM, with reservation set to 4GB. They are worried that the added compexity of Virtualiztion may be causing problems.

To add insult to injury, We P2Vd our SQL 7 boxes over a year ago on hardware from 2001, as the install would have taken to long and would have affected produciton. This is absoultley not optimal, I know this. Our SQL 2005 box was built from scratch as a VM and works a hell of a lot better then our SQL 7 boxes, (shocker I know).


Click to view rexchoi's profile Apprentice
rexchoi
12 posts since
Sep 19, 2007
1. Sep 19, 2007 12:17 PM in response to: mfleener
Re: Best practices for SQL in VI3

SQL is still running in windows (assuming you setup a windows guest) regardless if it is running on physical hardware or virtual hardware. I'm assuming you're using the SMP features in VMWare which presents multiple virtual CPU's to the guest/Windows instance.. In which case, there is even less difference between running SQL on a physical box or a virtual machine as far as process threading goes.

Depending on how heavy your SAN is being used, that would be the main concern.. of course, if your database are already hosted on the SAN, then I'd probably venture to guess that moving to VMWare would have little to no impact on the load on the SAN. You could even look at just virtualizing the windows instance and connecting the windows guest to the SAN seperatly outside of vmware.

Click to view Benny Hauk's profile Candidate
Benny Hauk
2 posts since
Oct 3, 2007
2. Oct 3, 2007 8:08 AM in response to: mfleener
Re: Best practices for SQL in VI3
Until someone can address this issue that IBM has raised and our company has observed, we aren't going forward with any database virtualization except for extremely light usage (in fact we aren't doing anything requiring heavy disk I/O):

http://communities.vmware.com/thread/102491

What we've found is that "consolidating" all little SQL Server databases down onto 2 or 3 really stinking haus boxes is a much better solution than having multiple VMs, each running SQL Server with one database on it. Some databases we run on their own instance, and others we just lump together on the same default instance. Either way it's all on the same hardware. Sometimes we have to jump through an extra hoop or two when we deal with vendors' 3rd party databases, but it ultimately works. I/O and CPU Performance is better, we still get the benefits of consolidation of one form or another, cost is MUCH less (referring to the per cpu cost of SQL Server, which when virtualized, technically you are now licensing per core, not per socket - since VMs see a core as full CPU with its own socket).

This issue brings me to my biggest "wish list" for server virtualization:

1) Microsoft to open their eyes and adjust their licensing scheme to accomodate the fact that a "CPU" in virtual-land can actually just be a core which technically comes out to less than 1 real CPU and also change their licensing so it's palettable for 3rd party vendors to deploy their Windows-based solutions as virtual applicances so I don't have to switch to Linux to enjoy the benefits of pre-configured solutions.

2) VMWare to find a way to offload I/O operations from the cpu to the HBA (like what happens in the physical world)

Actions

More Like This