question

Michael.McFarland_ans avatar image
Michael.McFarland_ans asked

Creating HyperV System with Multiple Servers Running SQL

I am planning on creating a host system that will run 4 VMs using Hyper V. Three of these VMs will be hosting SQL based applications. Do you see any issues with this idea? Will I create performance issues with multiple servers all utilizing SQL?
sql-server-2008virtualization
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you clarify, are these VMSs running SQL-based apps with SQL Server hosted elsewhere, or did you mean that SQL Server would be running on these as well?
0 Likes 0 ·
These are individual applications all running their own version of SQL
0 Likes 0 ·
Kevin Feasel avatar image
Kevin Feasel answered
Short answer: yes with an if; long answer: no with a but... There's no inherent reason why virtualization would, by itself, create a performance **issue** (although virtualizing does have performance **costs**). There are, however, a few things to be aware of before virtualizing. My experience is all with vmWare, but I'd imagine that they also apply to Hyper-V and Citrix just as well. First of all, it's harder to get good metrics in a virtual environment. You have another set of tools and another layer to analyze (and for things to go wrong in). Make sure that DBAs have access to the VM stats ([Brent Ozar on the topic][1], though dealing with vCenter). They'll need that to analyze performance problems. There are some SQL Server-specific tools that have some level of VM awareness and can collect at least vmWare metrics (Idera's SQL diagnostic manager is one that I have personal experience with), but if you have a performance problem, you'll need to go through different layers to determine the root cause. Secondly, another layer and another set of tools means potential inconsistent results. Your hypervisor layer is probably going to report that the SQL Server instances don't use that much RAM. This is a dirty, dirty lie. Well, actually, it's true but not as meaningful as you'd first think. All of those pages stored in memory are just sitting there, waiting for somebody to use them. From the hypervisor's perspective, this is a "waste" because nobody's using those pages at this exact moment. But having them in memory prevents future disk activity, which is a great performance improvement considering how much slower disk is than RAM. The third issue to think about ties back to the first two. When you virtualize, management now sees the ability to "reduce waste" and "maximize use." This is nice in theory: if you have 10 machines at 10% capacity, put them virtualized on one machine and have it run at 100%. The problem is that server use spikes. One VM spiking could now have effects on other VMs by starving them of resources, and a DBA without hypervisor-layer awareness will not be able to tell what's going on because Windows itself doesn't know. Once you think through those three issues, though, I'd say that there's no inherent problem with virtualization **as long as you have sufficient resources**. You might not need to size for 100% * [# of servers], but if average utilization is 10%, you definitely can't size for 10% * [# of servers]. It's safer to err closer to assuming that all servers will be at 100% resource capacity. I see virtualization as being similar to having a SAN. There can be a performance cost to having a SAN rather than direct-attached storage, but there are benefits (which can be huge in an enterprise environment) to having them. Similarly, using a hypervisor does carry some performance cost, but with some great business benefits including easier hardware migration, additional high availability options, etc. [1]: http://www.brentozar.com/archive/2011/12/readonly-view-permissions-roles-vmware-vsphere/
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Incidentally, one thing to think about in whether to virtualize (or whether to put all of your servers on one host) is how interconnected the servers are. If the SQL Servers have orthogonal use patterns--if Server A's use has no bearing on Server B's--then it's safer putting them on the same hardware. But if Server A and Server B get used heavily at the same time, you won't realize nearly as many of those management-friendly virtualization gains because you'll need closer to 100% * [old server resources]. Balancing out hosts can be tricky and there are hypervisor-based tools which help (vCenter, for example, can auto-migrate resources to have approximately equal resource usage across blades), but if you only have one host, you have to plan resource allocation around spikes.
3 Likes 3 ·
+1 to you sir. The only thing I would add is that if you only have one host for your VMs then any maintenance on it brings down everything.
3 Likes 3 ·
Thanks for all the advice... This has been great feedback form everyone!
0 Likes 0 ·
Missed this first time round... Gavin Payne did a good session at SQLBits on monitoring virtual servers. See: [ http://www.sqlbits.com/(X(1)S(xkq3bqj54ulh1rmknteodx3p))/Sessions/Event9/Through_the_virtual_looking_glass] for more information; unfortunately, the video isn't available at the time of writing this comment...
0 Likes 0 ·
Tim avatar image
Tim answered
SQL running in a virtual environment is 100% supported. You just have to balance your workload, I/O, memory, etc. If the host you are running from can sustain the requirements of all four environments then you are fine to move forward. The issue with virtual environments is when people try to over subscribe the host by provisioning more CPU and memory than they have to hand out. This is not a bad practice with app and web servers but with servers with higher I/O requirements it can be detrimental to the performance. It is typically best to set reservations for your SQL Servers and avoid over provisioning your memory and CPU. The big issue is with your storage. Make sure that your disk subsystem can support the I/O requirements. If you are good there then the only other big concern is with network. If you are taking a server with a 10/100 NIC and using it for a VM host, you need to upgrade to at least a gigabit or higher NIC. There are lots of best practices out there, VMWare has a white paper on SQL Server and virtualization. [Check this one out]( http://www.vmware.com/files/pdf/solutions/sql_server_virtual_bp.pdf)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.