Server consolidation & virtualization is a hot topic and also it is one of the biggest concerns for many DBAs is scalability/performance when it comes to virtualization.
At this point,performance for virtual machines can be close to, if not the same as, many physical server configurations. Just not in case if you have multiple SQL instances, its fuel to problem. Also the DBAs cannot ignore the fact that Virtualization is advantageous for the company and consolidation gets flexibility to manage the multiple instance easily. To that point I have been involved on such projects and to the affect there are definitely some considerations that will affect that decision whether to Virtualize the SQL environment or not. Outside world you will get the answer, it depends and never take it granted unless you have thouroughly satisfied with the enviornment availability & performance.
Coming to actual configuraiton on physical machine, its a fact that virtual machine throughput scales well for both 32-bit and 64-bit virtual machines. Whereas, 64-bit virtual machines provide better scalability for SMP virtual machines as user load increases along with SMP virtual machines provide better throughput scalability than UP virtual machines as user load increases, but with a higher CPU cost (reference to http://sqlserver-qa.net/blogs/tools/archive/2008/02/12/managing-sql-server-hot-topic-virtualization-and-consolidation.aspx link).
Obviously I don't want to repeat about VMWare related notes on SQL Server, as you can get VMWare-Community-SQLServer documents & SQLServer_on_VMWare-lessons learned are good ones. Then coming to Windows Server 2008 Hyper-V which is Microsoft's server-based virtualization solution for businesses of all sizes. Virtualization refers to a decades-old technology that allows you to run one or more virtualized, software-based PC-type environments under a host PC. Software-based virtualized PC environments or virtual machines (VMs)--are often referred to as guest PCs, though Hyper-V introduces slightly different naming conventions. Microsoft documentation refers: Hyper-V injects itself between the operating system and the hardware in a way that makes a Hyper-V-based Windows Server 2008 install quite different from a more traditional install. Hyper-V runs as a so-called Hypervisor (thus the name) that offers near-bare metal performance in the virtualized child partitions (similar, conceptually, to guest PCs) alongside the parent partition (which is always Windows Server 2008). The hypervisor approach also allows Hyper-V to offer better security--there is little code and no third party device drivers contained within, thus it has a minimal attack surface--and complete isolation between partitions. I recommend further reference on
The important aspect of Virtualization is server resources Memory & CPU: so memory is similar to processor, although it is not as much of a concern. With most hypervisors feature when you configure a virtual machine to use X GB of memory, it will consume that much under the hypervisor. That means that the server that functions as the hypervisor will generally have a lot of memory. You do have to also realize that virtualization has overhead. For instance if you configure a guest to use 8 GB of memory, it is really 8 GB plus whatever overhead of virtualization is imposed by the hypervisor. You will see the difference of such hypervisor environment will have better functioning as they generally have a lot of memory in them. Most of the content on web always talk about Memory & CPU but not about I/O which is one the high concern for virtualizing the nodes for the sake of data management, so whether iSCSI based hardware in the node is used or virtual HBAs, so all of the guests are sharing the hypervisor’s I/O architecture. I insist to take a good care of I/O too which is a key to a good portion of SQL Server's performance. So the best practice to obtain better performance from guests is to configure them to use physical disks, not the virtualized disk configuration through the hypervisor. This stands the real fact that using “real” hardware is better where you can in this case provides a big benefit since you have full bandwidth of the disk.
On the performance monitoring collection for baseline & benchmarking the primary measure of OLTP workloads is the throughput in units that specific to transactions per second, so keep an eye on I/O, CPU & Memory related counters. By default with any system-level workload the resource usage is critical aspect of database workoad and very important for sizing & capacity planning too on top of performance & availability features. So the data CPU usage data collected within VM is not useful for the reason that data does not always accurately reflect the overhead of virtualization that is incurred by physical server host and also the feature of way time is recorded in VM is not accurate. On the configuration side UP & SMP virtual machine configuration performance can be compare in many ways with different dimensions. On the consolidation process the key choice is processor & disks, CPU count of VM (UP or 2 way or 4 way SMP) will have higher virtualization overhead than UP VM which benefits the end user & database workload.
In this context for testing I recommend the storage layout of placement of database & log files is important, placew them on VMFS partitions located on independent disk arrays, place the files on RAID 5 LUN for the 32-bit virtual machine and usual RAID 5 LUN for the 64-bit virtual machine too (database is nominal OLTP based). As a best practice we can place the log files on RAID 1 LUN for the 32-bit virtual machine and 64-bit virtual machine (its a common configuration you think, eh!). By design RAID 5 arrays provide reasonable fault tolerance and performance with acceptable cost and overhead unless the workload is dominated by a large number of write operations having the RAID 1 provides the higher level of fault tolerance required for database log files. ON the other side of hardware parameters for SAN, HBA, and Fibre Channel switch are left at their default values. Finally the TEMPDB will be located on the operating system partition (C: drive) and depending on possibility to achieve higher throughput rates you can place TempDB to a SAN partition, especially for decision support or business intelligence workloads.
Finally coming to costing point of view, that virtualization brings cost reduction on licensing for SQL Server and Windows for each guest, see below:
Microsoft has changed their licensing to allow you to run as many virtual instances of Microsoft SQL 2005 or Microsoft SQL 2008 on the same server that you bought a license of the product for. So the more Microsoft SQL instance you need that can run on the same physical server the more money you can save. Below is the licensing statement for both SQL 2005 & SQL 2008 quoted directly from Microsoft:
For SQL 2005 Licensing in a Virtualized enviornment:
(Source: http://www.microsoft.com/sqlserver/2005/en/us/Special-Considerations.aspx)
When SQL Server 2005 runs inside a virtual operating environment, it requires at least one license per virtual operating environment. Several copies or instances of SQL Server 2005 can run inside a virtual operating environment. These must be licensed as follows:
- When Licensed per Server/Client Access License. Workgroup, Standard, and Enterprise editions of SQL Server 2005 now allow for unlimited instances within each virtual or physical operating environment. Previously, only the Enterprise Edition of the Server license allowed multi-instancing. This is a great incentive for customers to adopt the Server/Client Access License (CAL) model. For Workgroup and Standard, each virtual or physical operating environment containing a running instance of SQL Server requires a Server license. For Enterprise Edition, each physical operating environment containing a running instance of SQL Server requires a Server license and no separate licenses are needed for SQL Server instances running in virtual operating environments on the same machine.
- When Licensed per Processor. Workgroup, Standard, and Enterprise editions of SQL Server 2005 allow for unlimited instances in each virtual or physical operating environment. For Workgroup, Standard and Enterprise Edition, each virtual operating environment running SQL Server 2005 must have a processor license for each processor that the virtual machine accesses. If a copy of SQL Server is running on a physical operating environment, processor licenses are required for all of the processors on that physical server. For Enterprise Edition there is an added option: if all processors in a machine have been licensed, then the customer may run unlimited instances of SQL server 2005 on an unlimited number of virtual operating environments on that same machine.
For SQL 2008 Licensing in a Virtualized enviornment:
(Source: SQL Server 2008 Licensing Overview)
When SQL Server 2008 runs inside a virtual operating environment, it requires at least one license per virtual operating environment, except for SQL Server Enterprise edition. Several copies or instances of SQL Server 2008 can run inside a virtual operating environment. These must be licensed as follows:
When licensed Server / CAL
Workgroup and Standard editions now allow you to run any number of instances of the server software in one physical or virtual operating system environment on the licensed server at a time. Previously, only the Enterprise edition of the Server license allowed multi-instancing. This is a great incentive for customers to adopt the Server/CAL model.
For Enterprise edition, for each server to which you assign a software license, you may run, at any one time, any number of instances of the server software in one physical and any number of virtual operating system environments on that server.
When licensed Per Processor
With Workgroup, Web, and Standard editions, for each server to which you have assigned the required number of per processor licenses, you may run, at any one time, any number of instances of the server software in physical and virtual operating system environments on the licensed server. However, the total number of physical and virtual processors used by those operating system environments cannot exceed the number of software licenses assigned to that server.
Microsoft recommends that if you choose SQL Server 2008 Enterprise Edition and license all of the processors in the server with the hypervisor, you can configure SQL Server on any number of virtual processors under that one physical server. Also a note about the edition installation as per this licensing FAQ that if you do not purchase an Enterprise Edition license for the hypervisor, you will need to license each guest separately. For windows based see these http://www.microsoft.com/licensing/about-licensing/virtualization.aspx & http://www.microsoft.com/Windowsserver2008/en/us/hyperv-calculators.aspx pages. (also see these white papers from http://searchsystemschannel.techtarget.com/generic/0,295582,sid99_gci1508623,00.html & http://searchsystemschannel.techtarget.com/generic/0,295582,sid99_gci1507601,00.html links). “Green IT in Practice: SQL Server Consolidation in Microsoft IT” & “SQL Server Consolidation at Microsoft”.
