Green IT in Practice: SQL Server Consolidation in Microsoft IT
by Mark Pohto
Summary: The Microsoft environmental sustainability effort impacts all phases of the software development life cycle and operations. Several teams have committed to optimizing resource utilization. Current efforts include data gathering and analysis to identify areas for improvement, platform standardization, capacity management, consolidation and provisioning of energy efficient facilities and hardware. This article describes how Microsoft IT SQL Server consolidation activities are contributing to effective resource utilization and environmental sustainability.
According to a recent report to Congress provided by the Environmental Protection Agency and Energy Star, data centers consumed about 1.5 percent of the total U.S. electricity consumption or 61 billion kilowatt-hours (kWh). Estimates indicate that by 2011 data center energy consumption could nearly double. The report recommends several strategies for reversing this trend, such as financial incentive programs, best practice guidance, and energy star ratings for data center hardware.
Strategies for lowering overall energy consumption include a broad range of activities that, when combined, could reverse the historical consumption trend. The study indicated that, if implemented, such activities could reduce data center electricity consumption by amounts equivalent to the power produced by as many as 15 new power plants. The savings correspond to carbon dioxide emission reductions of 15 to 47 million metric tons. (For more on data center energy consumption trends and recommendations, see the Report to Congress on Server and Data Center Energy Efficiency Public Law 109-431, listed in Resources.
When it comes to protecting the environment, everyone must demonstrate social responsibility and initiative. Microsoft IT has been engaged in related activities for several years. The efforts have targeted data centers, various layers of the application stack as well as specific server workloads. Major consolidation efforts have included RightSizing, Storage Utility, Compute Utility, File Server Utility, and recently, the SQL Server consolidation initiative. Each effort has significantly reduced the data center footprint and has contributed to environmental sustainability. Areas of opportunity targeted by the SQL Server consolidation initiative include:
- Energy efficient hardware
- Hardware consolidation
- Intelligent allocation of hardware resources
- Energy efficient data center design
- Intelligent placement of data center facilities
- Elimination of unnecessary hardware
- Enabling power management where practical.
In addition to environmental sustainability benefits, SQL Server consolidation also presents clear business benefits, such as:
- Reduce operating and capital expenses. New hardware has far greater computing power than hardware that is nearing end-of-life. Newer hardware generally requires less power and cooling.
- Environmental sustainability. For example, lower power and cooling requirements are the primary areas where the SQL Server Utility addresses environmental concerns, but an overall reduction in data center space will also contribute over the long term.
- Provide business continuity, scalability, and availability. Requirements do not change for the SQL Server Utility; the goal is to find opportunities to standardize and to improve best practices in these areas.
- Provide a standardized server build library. New technologies such as Hyper-V open new opportunities for standardization. Part of the vision of the SQL Server Utility is to eliminate or streamline many of the manual steps needed to build an application environment. This can be achieved by establishing Hyper-V guests which are built with standard software configuration, including the operating system, SQL Server, tools, and approved configurations which can be provided for use in different phases of the software development life cycle.
Currently, 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.
Fortunately, the landscape for SQL Server consolidation has changed dramatically over the past few months. New technologies such as Windows Server 2008, SQL Server 2008, Hyper-V, System Center Virtual Machine Manager, System Center Operations Manager, improved storage technologies and more powerful servers provide greater opportunities for consolidation than ever before.
In addition to server consolidation, other virtualization opportunities exist. Those benefits are not the focus of this article but are described under General Benefits later in this article.
The objective is to design and deploy a SQL Server Utility service to reduce operating and capital expenses through consolidation and multi-tenancy. The SQL Server consolidation initiative will be based on the already successful shared service models of Storage Utility, Compute Utility, and File Server Utility. Each of these utilities has contributed to standardization of the Microsoft IT infrastructure, and each provides a more predictable and reliable platform for other applications and services. The Storage Utility and Compute Utility will be discussed in more detail later but are both important to the SQL Server Utility design.
Not all SQL Server instances will be good candidates for consolidation. Initially, this solution will be developed with the most typical OLTP databases in mind. Multi-tenancy scenarios will also be supported for those customers who only want a single database. We expect that consolidation will be appropriate for thousands of instances and that multi-tenancy will be appropriate for hundreds of databases.
In fiscal year 2009, the goal is to engineer and provide a SQL Server Utility that will reduce dedicated single instance SQL Server hosts by 10 percent and position Microsoft IT for continued consolidation.
The solution mindset requires executive sponsorship, data-driven discussion, coherent storage and computing foundations, and an articulated consolidation strategy.
Executive sponsorship. Executive commitment to maximize data center resource utilization and promote environmental sustainability is critical. Several activities described in this section provided the foundation needed for consolidation. None of these activities would have been likely to succeed without executive sponsorship and investment.
A data-driven discussion. 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.
The servers available for purchase today represent a massive increase in performance over systems available in the past. Today’s 2-way server provides the equivalent computing power of a 4-way from 12 to 18 months ago and even matches the Compute Unit capabilities of a four-year-old 8-way server. By collecting information about current hardware and processor utilization, the RightSizing team can make recommendations on how to maximize server utilization.
Data center servers are underutilized, with an overall average CPU utilization of ~9.75 percent. As new server platforms are introduced with performance capabilities far surpassing their predecessors, this already low CPU utilization number will continue to fall.
Table 1 and Figure 1 depict current processor utilization for a sample set of servers in Microsoft IT. A “temperature” was assigned to each consumption category for reference in RightSizing discussions. These numbers indicate substantial opportunity for host consolidation.
Table 1. Server Temperatures
|Server Temperature||Mean CPU%||Maximum CPU%|
Figure 1. Server Temperatures Sample Set
Storage 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.
Computing foundation. The Compute Utility strategy abstracts the services provided by hardware at Microsoft data centers. Rather than having a business unit address its computing requirements by purchasing a server, in this approach, a business group provides its computing capacity requirements, and Microsoft IT then determines whether a virtual or physical server can meet those requirements and provides the service. The Compute Utility strategy sought to create this level of abstraction for business groups to encourage them to purchase computing power and storage without worrying about the server hardware.
Other utilities such as the File Server utility rely on RightSizing data and reside on Storage and Compute Utilities. SQL Server consolidation will further contribute to environmental sustainability and will also rely on these utilities.
Consolidation strategy. There are multiple approaches to database consolidation. More common approaches include instance consolidation and host consolidation. In instance consolidation, databases from multiple SQL Server instances are consolidated under fewer instances, and considerations range from CPU, memory, and I/O subsystem management to sort/collation sequences to endpoint usage. In host consolidation, the host is partitioned (typically with Hyper-V or Windows System Resource Manager) and a larger number of instances are placed on each host. Each method of consolidation has its own considerations but many areas are much simpler to manage in a host consolidation approach.
Microsoft IT has compared consolidation approaches and has selected host consolidation for its ability to meet our consolidation objectives while introducing minimal risk. Since host consolidation still requires shared resources such as CPU, memory, IO, and network, selection of a manageable and flexible host partitioning method will have a significant impact on day-to-day operations. The General Benefits section of this article describes some important advantages of using Hyper-V compared to Windows System Resource Manager and multiple named instances.
All SQL Server Utility offerings will leverage layers of the existing Microsoft IT utility stack (Figure 2). Later phases of the consolidation initiative will consider other forms of consolidation as well as multi-tenancy.
Figure 2. MSIT SQL Server Consolidation Stack
Host consolidation requires that resources on a single host be managed to ensure that each instance receives predictable and reliable memory, processor, network, and I/O. Windows System Resource Manager and Hyper-V host partitioning technologies were evaluated; Hyper-V was selected for its manageability, scalability, and standardization benefits. Since Hyper-V on Windows 2008 scales to a maximum of four processors per guest (two processors for Windows 2003), consolidation of physical SQL Server instances will also be used to consolidate instances that require more than four processors. Physical SQL Server instances will be less manageable than virtual instances but will provide a consistent, proven approach to consolidation until Hyper-V scales to more than four processors.
The approach to host consolidation primarily targets servers that are approaching end-of-life. This provides minimal service disruption (since server replacement would occur with or without consolidation).
Multi-tenancy instances will also be deployed on Hyper-V and will be scaled to maximize host resources. As server and virtualization technologies improve, the Hyper-V approach to multi-tenancy will provide increasingly improved manageability and scalability.
Figure 3 depicts six end-of-life servers with 30 compute units each being replaced by equivalent Hyper-V guests on a single server.
Figure 3. Host Partitioning with Virtual Instances on Hyper-V or Physical Instances on WSRM
Figure 4 depicts SQL Server Utility offerings that cover different tenant needs. Hyper-V will be used to partition host resources for those tenants who need up to four processors and named instances either on a shared or dedicated server will be used for those who require additional processor or memory resources. As Hyper-V, hardware, and other technologies evolve, more and more tenant needs will be met using Hyper-V.
Figure 4. MSIT SQL Server Utility Offerings
Table 2 shows the standard Hyper-V guest configurations that will be used in the SQL Server Utility.
Table 2. MSIT SQL Server Utility Offerings
|Virtual Instance Low||2 - 4||1|
|Virtual Instance Medium||4 - 8||2|
|Virtual Instance High||16||4|
The primary requirements of the SQL Consolidation are to reduce operating and capital expenses by more effectively utilizing data center resources. System qualities, such as availability, that existed in the non-consolidated legacy environment are still requirements for the new consolidated environment. This architecture design followed basic IEEE-1471 guidance in identifying stakeholders, gathering and understanding requirements, and selecting and designing for specific architectural viewpoints. Architectural viewpoints were selected from traditional IT service management functions and were evaluated against Microsoft Operations Manager 4.0 IT service life cycle and current technology trends to anticipate future effectiveness of the design and to identify opportunities for improvement. Environmental sustainability has become an architectural consideration for business/home construction, automotive, and other industries; it is also a relevant architectural framework viewpoint for IT.
Availability and Business Continuity requirements provide the application development teams and operations teams with the flexibility to employ any SQL Server feature that is appropriate for an application. At the time this article is being written, SQL Server clustering on Hyper-V has not yet been approved as a supported implementation. However, database mirroring and log shipping are supported. SQL Server clustering will be introduced to the SQL Server Utility service as soon as that support is available. This project will be executed in phases, deploying implementations as support becomes available, so non-clustered deployments will be completed ahead of those that require clustering.
Figure 5 depicts Hyper-V SQL Server Guests Deployed from a standard build library with optional business continuity and high availability options. Tenant instances will be colocated and will be placed to distribute workload based on application business cycle requirements.
Figure 5. Hyper-V SQL Server Guests with Business Continuity and High Availability (Click on the picture for a larger image)
Manageability in the consolidated environment is improved but very similar to what it was in the non-consolidated environment. New and future solutions like System Center Virtual Machine Manager, Data Protection Manager, and System Center Operations Manager will help ensure continuous improvement in the area of manageability.
Provisioning. One key benefit of this consolidation architecture will be the ability to quickly provision new SQL Server guests. Since storage and host servers will be preconfigured, the turnaround time for providing a new SQL Server guest will be reduced by weeks when compared to the acquisition process for dedicated physical hosts.
Virtual Machine Manager Library, a standard build library, developed by Microsoft IT, with consistent Windows Server and SQL Server installation and configuration is another key manageability improvement for consolidation with Hyper-V which will allow IT developers, testers, and production support teams to ensure a consistent experience across phases of the Software Development Life Cycle.
Imagine replacing less efficient build processes with a standardized and possibly automated deployment process. Colleagues with many years of experience in development and production support express excitement when contemplating the increased environmental consistency and stability offered by this approach.
Relocation of a virtual SQL Server instance is simpler and lower risk than the method of building a server, installing Windows Server and SQL Server then migrating databases to the new physical SQL Server instance.
Basic database administration tasks for the consolidated SQL Server environment will continue to leverage the backup utilities that were written by Microsoft IT (see the September 2007 article on SQL Server Automation Scripts in SQL Server Magazine). Data Protection Manager has been deployed within Microsoft IT and adoption for SQL Server backups is on the roadmap for this initiative.
Finally, the snapshot feature of Hyper-V will improve the ability to deploy and roll back host changes. You simply take snapshots at key points in your change deployment so that, instead of having to start from scratch and rebuild a server, you have the option to roll back. While there is overhead associated with taking guest snapshots, as a tactical deployment tool, Hyper-V snapshots have advantages over a manual roll back.
Performance requirements and abilities in the consolidated environment are also very similar to the previous environment. Since the Storage Utility had been implemented prior to SQL Server consolidation, a track record of performance from the storage layer already exists. In the consolidated environment, SQL Server operations teams will still be able to provide I/O performance expectations to the Storage Utility team and will be able to obtain Hyper-V guests with sufficient processor and memory from the Compute Utility team. Here are a few performance guidelines that are used in this consolidation effort:
- 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.
Figure 6. Hyper-V SQL Server Guest VHDs
Since Windows 2008 Hyper-V guests may use a maximum of four processors, native SQL instance consolidation is planned for workloads that require more than four processors. Native SQL instance resource management can be effectively accomplished using both SQL Server configuration and Windows System Resource Manager. Once Hyper-V scales to more than four processors, SQL instances that require more processing power will be deployed and managed on Hyper-V. (The recently published “Running SQL Server 2008 in a Hyper-V Environment - Best Practices and Performance Recommendations” offers more detailed Hyper-V guidance; see Resources.)
Predictability/repeatability can be improved by developing and adopting a configuration and deployment strategy that spans multiple phases of your software development life cycle. In today’s environment developers, testers and production support teams build servers using standard install bits for the operating system, SQL Server, and additional tools. This time-consuming approach can sometimes result in inconsistencies between environments. These inconsistencies can ultimately result in unanticipated or unpredictable behavior once an application is deployed into production environments. Using Hyper-V guests that are preconfigured with standard builds that the SQL Server Utility team expects will reduce or eliminate inconsistencies across the software development life cycle.
Reliability was a key concern for SQL Server consolidation tenants. The concern was that consolidated workloads and configurations would interfere with one another and that changes made for one application/tenant would impact others and cause additional application downtime. Hyper-V provides operating system isolation so production support teams can prioritize their upgrade activities without dependencies on other application support teams. This greatly improves flexibility when it comes to test, change, and release; and improves overall reliability for applications due to fewer conflicts.
Scalability and Capacity Management are easier with virtualization. RightSizing teams and capacity management teams have greater agility when it comes to sizing up or down. If a tenant requests a medium-sized two-processor guest with 4 GB of memory, but after deployment, learns that they underestimated their resource requirements, it is a simple matter to add memory and processors to the guest, even if it means relocating the guest to a host with available resources. This flexibility to reconfigure and relocate Hyper-V guests means that IT teams no longer have to over-purchase hardware resources which will result in less underutilization of data center resources.
SQL Server instances that require more than four processors will be in scope for consolidation but will be consolidated using physical rather than virtual instances. Windows System Resource Manager will be used in cases where multiple physical instances share a single host. Physical hosts will be dedicated to specific tenants for improved SLA management, security and customer satisfaction. As Hyper-V supports increased numbers of processors, more databases will be appropriately hosted in virtual instances.
Physical Resource Optimization (PRO). Using System Center Virtual Machine Manager 2008 and System Center Operations Manager 2007, administrators can assess historical performance data and intelligently place new SQL Server guests to optimize physical resource utilization and distribute workloads.
Security presents one of the greatest concerns. Microsoft IT production support teams have traditionally had access to host configurations. They are responsible and accountable for changes made to hosts and though changes are made through established change control processes, these teams wish to maintain the level of access and control that they’ve had historically. The Hyper-V approach to host partitioning provides production support teams with the flexibility to schedule, test, and apply changes and security patches in a timely manner.
Other methods of host partitioning, such as Windows System Resource Manager, meant that each instance on any given host would need to be on the same build of Windows Server and may need to coexist with other tenants who would likely have different service level agreements, maintenance windows, priorities, and application business cycles. Other partitioning methods also introduce security questions for data access, service account usage, certificates, logins/roles, and access to the operating system and hardware.
Service Monitoring and Control changes for this project are minimal in early phases. Monitoring requirements are somewhat increased by introducing an additional layer for virtualization, but since virtualization has been broadly adopted by other platform layers such as for file servers and Web servers, service monitoring and control is expected to become a standardized and effective part of our environment. Monitoring services currently provided using System Center Operations Manager 2007 by the Microsoft IT Enterprise Monitoring Team for the preconsolidation environment will also be leveraged by the SQL Server Utility. The SQL Server Utility team believes that availability monitoring for databases and SQL Server services are a fundamental requirement for this service offering. Therefore, adoption of a database availability reporting/scorecard system and for improving the System Center Operations Manager SQL Server management pack rules are in scope for early project phases.
Another product that will be used to ensure consistent and optimal configuration is System Center Configuration Manager. Using this product with Desired Configuration Management, detection of configurations which have deviated or “drifted” from the known good and approved configurations will be much easier.
Supportability is important to application owners. The SQL Server Utility project will not deploy unsupported configurations. We will, however, anticipate future supported configuration scenarios and posture accordingly.
Consolidation in the Data Center
Operating costs are expected to drop sharply but not as dramatically as power and space. That is primarily because, even though we will have fewer physical servers, there will still be a cost associated with managing the Hyper-V guests. Annual operating costs for the SQL Server utility are expected to be $11 million/year lower than previously.
A typical end-of-life SQL Server host in the Microsoft data center occupies 6.8 rack units. Servers provided by the Compute Utility team occupy less than 1 rack unit and provide enough computing power to host five or six instances of SQL Server. This comes to a savings of over 33,000 rack units or about 700 racks! This number does not even take into account that the DAS being replaced by SAN also comes with significant savings.
On average, end-of-life servers use 369 volt amps while new servers use 313 volt amps and can run at slightly higher temperatures. Similar power requirements exist for cooling. This means that there will be a dramatic reduction in power requirements, over 3 million volt amps, and eventually, there may be opportunities to modify data center cooling requirements to further reduce power consumption.
Recycle costs for this project were estimated, but it is clear that deploying fewer servers has an impact on recycling and the environment. Figure 7 depicts the impacts of a 6:1 consolidation ratio for 5,000 SQL Server hosts (recycle costs were estimated).
Figure 7. Impacts of Consolidation (Click on the picture for a larger image)
Consolidation Across the Software Development Life Cycle
Figure 8 depicts Hyper-V streamlined processes across the software development life cycle and well into production operations environments.
Figure 8. Streamlining Processes with Hyper-V
Table 3 shows the benefits of consolidation on Hyper-V guests compared to using native instances of SQL Server.
Table 3. SQL Server Utility System Qualities – Hyper-V and Physical SQL Server Instances
|System Quality||Feature||Hyper-V||SQL Inst.|
|Manageability||Ability to build and provide canned environment||YES||NO|
|Manageability||End-to-End (development through production) use||YES||NO|
|Manageability||Simple migration to new host during server retire/replacement||YES||NO|
|Manageability||Simplicity for Instance scale up||YES||NO|
|Manageability||Simplicity for cloning a production environment (e.g. to Test)||YES||NO|
|Security||Transparent to accomplish same level of security as with a dedicated host?||YES||NO|
|Scalability||Dynamic sharing of processor resources||YES||YES|
|Scalability||Processors Supported per environment||4||>32|
|Availability||Clustering Option (Future ability for SQL Server on Hyper-V)||YES||YES|
|Business Continuity||Supports SQL Business Continuity features?||YES||YES|
|Supportability||SQL 2005 and 2008 CSS Support (not yet with SQL Server clustering)||YES||YES|
Effective resource utilization within the data center has a significant impact on the global efforts to protect our environment. Beginning with the server build, and on through transportation to the data center, rack space utilization, power, cooling, and finally, to the eventual end-of-life recycle, each server deployed or not deployed has an impact on efficient resource utilization and ultimately on the environment.
Executive sponsorship is crucial to enterprise-wide consolidation projects. It is not difficult to make a business case for consolidation, particularly when you consider data center power and rack space benefits. The socialization, evangelism, and budget controls that executive sponsorship provides are vital to deployment and adoption.
Today, finding opportunities to consolidate various IT layers and functions is easier than ever before. Windows Server 2008, Hyper-V, and SQL 2008 are a few of the products that are truly game changers when it comes to effective resource utilization and consolidation.
The Microsoft IT SQL Server Utility project is currently in progress and is expected to make a substantial impact on environmental sustainability while achieving many other virtualization and consolidation benefits.
Data Center Energy Forecast
Microsoft IT RightSizing - Identifying Server Candidates for Virtualization
Microsoft IT Showcase Virtualization White Paper
Report to Congress on Server and Data Center Energy Efficiency (Public Law 109-431)
Running SQL Server 2008 in a Hyper-V Environment – Best Practices and Performance Recommendations
SQL Server Magazine SQL Server Automation Scripts (September 2007)
Standard Performance Evaluation Corporation
About the Author
Mark Pohto is a senior systems engineer in Microsoft IT. Mark has worked in IT since 1987 and has a strong passion for leadership and database technologies. He joined Microsoft’s Redmond Platform Services team in 1999. His diverse experience and education includes language training at the DOD Defense Language Institute, several years in the intelligence community, U.S. Army Officer training, and MIS training at Our Lady of the Lake University in San Antonio, Texas. At Microsoft, he has worked as a senior DBA in the Database Operations Group and has managed the team responsible for application monitoring using Microsoft Operations Manager. He developed and published the automation tools used for database administration and has contributed to the books SQL Server 2000 High Availability and Pro SQL Server 2005 High Availability by Allan Hirt. As the group manager for the Microsoft SQL Server Center of Excellence, Mark managed the team that developed SQL Server solutions such as the Risk Assessment Program and Service Level Monitoring and the SQL Server Microsoft Certified Architect program. He has taught architecture in programs for Exchange Server, Windows Server and SQL Server. Mark recently returned to Microsoft IT to lead an IT-wide SQL Server consolidation project.
Jimmy May, Microsoft IT senior performance engineer, and Ward Pond, Microsoft Services technology architect, also contributed to this article.