If you are wondering how SQL might work in a virtual environment, consider the following paper from Microsoft.
To sum it up, SQL has been thought (incorrectly) to be one of the few databases that are best kept on physical, but here Microsoft IT has displayed a sensible approach to identifying and consolidating the right virtualization candidates, through a process they call RightSizing. The Microsoft IT application portfolio consists of about 2,700 applications. There are approximately 100,000 databases on 5,000 SQL Server Instances, most of which are on dedicated hosts. Approximately 20 percent of those hosts reach end-of-life each year and are replaced. Average CPU utilization across these hosts is below 10 percent, indicating significant opportunity for host consolidation.
Microsoft IT developed the RightSizing initiative to ensure effective utilization of servers in the data center and in managed labs. Because significant underutilization occurs, one of the initiative’s first tasks was for Microsoft IT to identify underutilized servers that might be good candidates for virtualization (for more information on RightSizing, see Resources). The Capacity Management team relies on RightSizing data.
To accurately compare the performance of server platforms of varying architectures, Microsoft IT has developed a Compute Unit (CU) formula for each server platform that utilizes an industry standard, architecture-agnostic, benchmark suite from the Standard Performance Evaluation Corporation (SPEC). The SPEC benchmarks are developed in such a way to allow a repeatable test with strict result submission requirements. The Microsoft IT CU formula uses a baseline (not peak) benchmark that measures the rate of integer calculation work a server platform can perform in a given amount of time.
They compare a lot of before and after scenario’s in the paper.
- “Before” is mostly an app on a server with direct attached storage.
- “After” is the new storage utility based model.
The new model places all of these SQL-based app’s atop an EMC DMX-based storage utility that dramatically raises the performance, scalability, and availability needs they had previously offered their users. They use the DMX Virtual Provisioning (our implementation of thin provisioning) to preallocate (400) 300GB drives and eliminate the need for host additions and re-zoning and all that junk. It’s a perfect use case for DMX VP. I attached a whitepaper on this at the end of this post.
Some highlights from the paper:
Their storage utility and virtualization approach, in comparison to their older DAS/legacy environment, is quite GREEN: it saves power and money.
Some performance tips for virtualizing SQL with Hyper-V:
- Crawl, walk, run. Don’t go for the maximum host consolidation ratio right away. Begin with the smaller workloads, validate your deployment, and refine the plan. Maximize your resource utilization in phases after establishing and evaluating your actual utilization.
- Use Hyper-V pass-through disk or fixed Virtual Hard Disks (VHDs) for storage (Figure 6). Fixed VHDs offer some manageability benefits but provide slightly lower performance. Moving a guest to another host, for example, is simplified when using VHDs.
- Do not overcommit processors for SQL Server guests. Begin with one logical processor for one physical processor. Verify your performance and refine your configuration as needed. At some point, this may include overcommitment, but begin without overcommitment to manage risks.
- Avoid the use of emulated devices in Hyper-V. Favor synthetic devices which provide better performance and lower processor overhead.
- Establish an operating level agreement (OLA) with performance requirements for your storage provider if they are a separate service. Microsoft IT SQL Server Utility has requirements for 1ms average disk/second read/write for log and 8ms for OLTP data.
Their Approach (build a solid foundation):
The Storage Utility service provides shared or dedicated SAN storage to which data center servers can connect. The service provides the SAN storage and all hardware required to connect a server to the SAN, as well as all maintenance and management functions, but does not provide the server itself.
An effect of the Storage Utility was that instead of engineering and purchasing small, medium, large servers with anticipated DAS for SQL Server and other server types, Microsoft IT was able to modify the standard builds so that minimal DAS was included in the purchase. This means that application support teams no longer had to anticipate disk capacity over the life of the server which usually resulted in underutilization.
Their Storage Utility Configuration:
You can learn more about EMC DMX Virtual Provisioning in this paper: