SQL Server Consolidation at Microsoft

Technical White Paper

Published: March 2009

Contents

Executive Summary

Introduction

Considerations for Consolidation and Virtualization

Implementation of the SQL Utility

Attributes of System Quality

Conclusion

For More Information

Executive Summary

Many Microsoft® SQL Server® instances deployed in Microsoft data centers operate on dedicated server hardware. However, SQL Server instances frequently underutilize the hardware resources. Each server requires management, physical space within the data center, power, and cooling—all of which are finite resources.

Microsoft Information Technology (Microsoft IT) recognized that these finite resources were being overused and sometimes depleted. For example, Microsoft data centers experienced a shortage of physical space. The power needs of the servers themselves and the power required to provide cooling to the data centers also present an expense to Microsoft.

One way to solve the problem of ever-increasing demands on finite computing resources is through consolidation and virtualization. Microsoft IT successfully deployed similar projects for other areas with the release of the Compute Utility to manage memory and processor resources, and the Storage Utility to manage disk resources. These utilities changed the way Microsoft IT presents resources to business users. Instead of purchasing server hardware, the business user purchases compute units (CUs) or storage units (SUs) based on the needs of the application. Microsoft IT obtained CUs from Standard Performance Evaluation Corporation for various processor chips. CUs provide a realistic way of comparing the processing power of servers that have different processors.

Microsoft IT identified that the number of SQL Server computers in the organization was growing as new applications went online, and that about 20 percent of the SQL Server computers were reaching their scheduled end-of-life (EOL) date and would need to be replaced each year. This presented an opportunity for Microsoft IT to create a new service called the SQL Utility. The SQL Utility provides virtualized SQL Server instances based on actual resource requirements.

The SQL Utility effort provides several business benefits:

• Reduces operating and capital expenses. The hardware being deployed for the SQL Utility uses less power and requires less cooling. Additionally, hardware components such as the processor are more powerful per dollar spent, requiring fewer resources for the same level of performance. A SQL Server instance is projected to cost 44 percent less to operate per year when managed through the SQL Utility. Microsoft IT therefore expects the SQL Utility to reduce operating costs by $11 million per year.
• Provides business continuity, scalability, and availability. The requirement to provide a stable, reliable, and resilient environment exists regardless of what technologies and approaches Microsoft IT selects. Therefore, this requirement is as true for dedicated SQL Server instances as it is for the SQL Utility. The SQL Utility service enhances and adds to these capabilities wherever possible.
• Enhances environmental sustainability. By using less power and requiring less cooling, SQL Server computers deployed through the SQL Utility contribute directly to environmental sustainability efforts. The utility also reduces space requirements in data centers, because the consolidated hosts use far less rack space.
• Creates a standardized build for SQL Server instances. Building an application environment with SQL Server typically requires several manual steps. However, technologies such as Hyper-V™ technology provide new opportunities to create standardized builds that can be deployed quickly and consistently.

Microsoft IT examined several options for creating the SQL Utility, including choosing the most appropriate method for providing SQL Server instances while keeping in mind factors such as availability, security, and manageability. Microsoft IT examined the available technologies to choose the most appropriate method for virtualization. The result is that the SQL Utility facilitates SQL Server consolidation to decrease the demand for finite data center resources.

By using technologies such as Hyper-V and the Windows Server® 2008 operating system, the SQL Utility creates a highly available and easily managed environment. The 2009 goal for the SQL Utility is to reduce dedicated SQL Server single-instance hosts by 10 percent, in addition to laying the foundation for future consolidation efforts.

This white paper examines the deployment of the SQL Utility within Microsoft IT, including the decisions that Microsoft IT made, implementation details, and aspects of system quality. It assumes that readers are technical decision makers and are somewhat familiar with Windows Server and SQL Server technologies. Many of the principles and techniques described in this paper can apply to other organizations. However, this paper is based on the experience and recommendations of Microsoft IT as an early adopter. It is not intended to serve as a procedural guide. Each enterprise environment has unique circumstances; therefore, each organization should adapt the plans and lessons learned described in this paper to meet its specific needs.

Introduction

The current Microsoft IT application portfolio consists of about 1,300 applications. Today, more than 4,700 SQL Server instances with about 100,000 databases on dedicated hosts support the Microsoft application portfolio.

Business units within Microsoft typically purchase dedicated hosts for their data-related application needs. However, data gathered from the RightSizing initiative indicates that the mean processor utilization across these hosts is below 10 percent. The underutilization of hardware taxes data-center resources such as space, power consumption, and cooling needs.

The cost of providing manageability and availability for single databases or instances is higher than that of a standardized and virtualized approach. Such an approach offers significant opportunity to provide a highly manageable and available solution at scale for minimal expense. In addition to providing virtualization, one such opportunity is to establish a standard library of server builds and an approach for replacing the current manual build process of development, test, and production environments. Microsoft IT can use this pattern for SQL Server, as well as for other types of servers and applications, to further reduce operating expenses.

Figure 1 shows the effects of a 6-to-1 consolidation ratio as they pertain to resources. This ratio is an average that represents the savings that Microsoft IT found by consolidating resources. For example, where servers would have previously taken 6.8 rack units (RU), consolidation can provide the same computing and database resources in 0.62 RU.

Figure 1. Positive effects of consolidation on resource needs and related costs

Considerations for Consolidation and Virtualization

The SQL Utility and consolidation effort builds on the foundations that the Storage Utility and Compute Utility strategies provide. These strategies focus on consolidation and virtualization of resources.

The Storage Utility provides storage area network (SAN) storage and simplifies capacity management and planning. The Compute Utility provides computing resources to the business based on pre-determined computing power needs. Microsoft IT determines whether a virtual or physical server is needed based on application requirements and on RightSizing data. Both the Storage Utility and Compute Utility services effectively abstract the provisioning and use of computing resources from the viewpoint of the business user and application. Figure 2 depicts the relationship of the SQL Utility to virtualization, the Storage Utility, and the Compute Utility.

Figure 2. The SQL Utility as it relates to other utilities in Microsoft IT

Identification of Underutilized Servers

Effective consolidation starts with the basic problem-solving steps of understanding the problem and the environment, identifying alternatives, evaluating alternatives, and finally implementing and refining the selected alternative.

Microsoft IT has databases that it uses to track all the applications and all the hardware resources. Over the years, RightSizing efforts have targeted those resources to measure actual utilization. Monitoring revealed that resources were frequently underutilized. Microsoft IT measured the processor utilization of SQL Server instances and rated them in terms of a figurative "temperature." Table 1 defines this rating system.

Table 1. Server Ratings Based on CPU Usage

Server temperature	Mean CPU percentage	Maximum CPU percentage
Permafrost	≤1	≤5
Cold	≤5	≤20
Warm	≤20	≤50
Hot	>20	>50

Microsoft IT classified only about 5 percent of the servers as "hot," and more than half of the servers as either "cold" or "permafrost." This data confirmed that significant opportunity existed for consolidation.

Consolidation Approaches

There are three primary approaches to consolidation of SQL Server instances:

• Host consolidation. Refers to placing additional SQL Server instances on each physical host. Historically, host consolidation for SQL Server has been accomplished by installing multiple instances of SQL Server on a single operating system and using SQL Server and sometimes Windows® System Resource Manager (WSRM) to manage CPU and memory. Recently, virtualization has matured to the point that it provides another viable alternative for managing these resources on a single host.
• Instance consolidation. Refers to hosting more databases on each SQL Server instance, resulting in fewer overall SQL Server instances. This approach has many risks and challenges, because numerous configuration items and resources are now common to more databases and applications. TempDB, memory, and service accounts are just a few areas for concern. Also, the operating system is shared in this model, which presents a challenge in regard to patches, scheduled downtime, upgrade schedules, and similar activities.
• Database consolidation. Refers to hosting more applications on each database. This consolidation can be achieved by using schemas, but doing so is not a cost-effective approach for consolidating existing databases and applications.

Whenever previously separate hosts and applications are placed in a shared environment, the risk exists that one or more of those applications will consume an extraordinary amount of the shared resources, thereby depriving the other applications of their share of computing resources. A significant risk of service level agreement (SLA) conflicts also exists across those applications that now must agree to scheduled maintenance.

Microsoft IT decided to use host consolidation as its approach for consolidation with the SQL Utility. Host consolidation either reduces or eliminates many of the typical risks associated with other consolidation types. Host consolidation greatly reduces risks such as SLA conflicts and shared resources, but depending on how computing resources are partitioned or managed, there are still risks to address. Microsoft IT's next steps were therefore to assess and select an approach to resource management.

Virtualization Platform: WSRM vs. Hyper-V

An important decision in host consolidation is how to partition or manage computing resources. For the SQL Utility project, Microsoft IT evaluated using named instances with WSRM and using Hyper-V technology.

WSRM and Hyper-V offered various capabilities to meet the requirements of the SQL Utility. WSRM enables administrators to:

• Set policies for consumption of application resources for processor and memory:
  • Select processes to be managed
  • Set targets or limits for resource consumption
• Manage computer resources according to policies:
  • Processor utilization (percentage of CPU)
  • Assignment of processes to dedicated processors (CPU affinities)
  • Process working set size (physical resident pages)
  • Committed memory (page table and page file usage)
• Generate, store, view, and export accounting records.
• Apply policies based on a date/time schedule.
• Generate e-mail notification for events.

WSRM grants resources to processes, meaning that resources can be allocated to a process on demand. If the process does not require the allocated resources, other processes can use those resources.

For example, if six WSRM-managed SQL Server instances exist, each instance can be granted 16 percent of the total processor. Each instance can use its entire share of the processor, but if five instances are using less than their allocated processor resource, the remaining instance can use more processor resources. If demand for processor resources then increases on one of the other instances, WSRM returns the resources as needed. Figure 3 depicts this process.

Figure 3. Resource allocation by WSRM

Hyper-V has the following capabilities:

• Up to 16 host cores
• Unlimited random access memory (RAM) for hosts
• Unlimited virtual machines (VMs) per host
• Up to eight VM processor cores per host core and up to four VM processors per VM
• Use of the System Preparation Tool (Sysprep) on the image and the ability to add the image to a library for reuse
• Ability to take a snapshot of the VM during testing
• Ability to build a VM from a network installation

Hyper-V also offers several capabilities relevant to the SQL Utility:

• Standard guest templates can be used for initial builds.
• Guests can be moved to newer hosts as needed for isolation, load balancing, or EOL server replacements.
• Guests can be reconfigured down or up to the next standard environment or guest.
• Small, medium, and large standard guests can coexist on a single host.
• Microsoft System Center Virtual Machine Manager 2007 can be used to watch and guide capacity management.
• Hyper-V provides a consolidation platform that is consistent with other consolidation efforts (for example, Web servers).

Figure 4 illustrates the Hyper-V architecture.

Figure 4. The architecture of Hyper-V

Early in the consolidation project, Microsoft IT planned to use WSRM; but after closely evaluating both approaches, it selected Hyper-V technology for the SQL Utility. Microsoft IT based this decision on the manageability, scalability, and standardization benefits that Hyper-V provides as they related directly to the SQL Utility project. Microsoft IT uses Hyper-V to manage both processor and memory use within and across both host and guest partitions.

Implementation of the SQL Utility

Microsoft IT is implementing the SQL Utility in phases to reduce risk. The first phase of the project focuses on consolidation of SQL Server instances that are reaching EOL.

The approximately 20 percent of SQL Server hosts that are scheduled for EOL replacement each year would typically need to be replaced with new hardware dedicated to a single SQL Server instance. Microsoft IT targeted these retire/replace servers that perform OLTP workloads as candidates for taking advantage of the SQL Utility service.

The SQL Utility service is consumption based. CUs and memory are the measures by which Microsoft IT allocates resources to existing hosts when those hosts are migrated to the SQL Utility service. Microsoft IT provisions additional resources on an as-needed basis.

Microsoft IT examined several approaches to providing SQL Server environments and decided on an implementation that provided always-on high availability with flexible business continuity and disaster recovery. Hardware resources from the host are divided or partitioned through Hyper-V technology. This option currently provides up to four logical processors per guest.

Figure 5 depicts the SQL Utility architecture.

Figure 5. The SQL Utility architecture

Service Offerings

Within the implementation that Microsoft IT chose, two service offerings of the SQL Utility service exist:

• Hyper-V Guest with SQL Server
• Hyper-V Guests with SQL Server Clustering (future offering)

Each of the two service offerings is available in small, medium, or large configurations. The type of configuration chosen is based on RightSizing data and baselines of established performance patterns. Table 2 breaks down the small, medium, and large configurations.

Table 2. Configurations Available with the SQL Utility

Offering	RAM (SQL Server + operating system)	Processors
Virtual instance small	2 or 4	1
Virtual instance medium	4 or 8	2
Virtual instance large	16	4

For the initial rollout of the SQL Utility service, Microsoft IT targeted SQL Server instances that required less than 1 terabyte of storage, 16 MB or less memory, and four logical processors or fewer.

Figure 6 depicts the SQL Utility service offerings and how client need determines the type of instance to be deployed.

Figure 6. Determining the configuration for a SQL Server instance

The use of Hyper-V enables benefits for manageability, performance, scalability, and flexibility around business continuity. Hyper-V enhances manageability by enabling rolling upgrades through clustering in addition to the standard build templates, which makes deployment much easier. Hyper-V enhances performance and scalability because of the possibility of isolating poorly performing instances and the ability to allocate additional resources to instances as needed.

Users have a choice of business continuity/disaster recovery features and work with the SQL Server Operations team to choose the appropriate business continuity/disaster recovery for the instance. The use of Hyper-V enables the SQL Utility to work within the software development life cycle (SDLC), as shown in Figure 7.

Figure 7. Hyper-V and the SDLC

Identification of Candidates for Migration

Microsoft IT uses the following criteria to qualify candidate SQL Server instances for migration:

• Permafrost or cold SQL Server instances running on a physical host
• EOL servers that need replacement
• End-of-warranty services that can be migrated
• Net new servers (for example, new applications that require a database server)
• Proactive migrations to take advantage of Hyper-V benefits and reduce costs

Microsoft IT will provision servers according to RightSizing data and capacity management guidance. Instances of EOL hardware will be migrated via the standard processes of the SQL Server Operations team. Guests will be isolated initially by demand and SLA. In general, workloads from multiple business units will not share hosts.

Microsoft IT monitors capacity and availability to ensure that a newly onboarded SQL Server instance does not adversely affect performance. It will use existing performance baselines.

Microsoft IT will deem phase 1 a success based on the host count and the reduction in rack space and power consumption. System quality attributes such as availability and performance will also be reported. Later phases will focus on improvements to service offerings such as clustering for high availability and database backup automation via Microsoft System Center Data Protection Manager 2007.

Attributes of System Quality

As part of the SQL Utility project, Microsoft IT identified several key attributes of system performance and quality. These attributes existed for hosts in a non-consolidated environment, and they continue for SQL Utility in the dedicated service environment. These attributes include:

• Availability and business continuity
• Environmental sustainability
• Manageability
• Provisioning
• Performance
• Predictability and repeatability
• Reliability
• Scalability
• Security
• Monitoring
• Supportability

Table 3 shows features of both Hyper-V and a normal SQL Server instance as they relate to specific attributes of system quality.

Table 3. A Comparison of Features Related to System Quality

Attribute	Feature	Hyper-V	Dedicated SQL Server instance
Manageability	Ability to build and provide standardized environment	Yes	No
Manageability	Deployment/rollback ability	Yes	No
Manageability	End-to-end life-cycle use	Yes	No
Manageability	Simple migration to new hosts as needed	Yes	No
Manageability	Simple resource management (scale-up)	Yes	No
Manageability	Simple process for creating cloned instances	Yes	No
Security	Same process for securing a dedicated host	Yes	No
Scalability	Dynamic processor resource sharing	Yes	Yes
Scalability	Processors supported per environment	4	>32
Performance	Acceptable performance	Yes	Yes
Availability	Clustering available (future ability for SQL Server on Hyper-V)	Yes	Yes
Business continuity	Support of SQL Server business continuity features	Yes	Yes
Supportability	Microsoft Customer Support Services (CSS) support for Microsoft SQL Server 2005 and SQL Server 2008 (not yet with SQL Server clustering)	Yes	Yes

The following sections examine each of the attributes as they relate to the SQL Utility service.

Availability and Business Continuity

Availability refers to the timely accessibility of an application or service based on a defined service agreement and from a customer perspective. At this time, Hyper-V does not support SQL Server clustering. Therefore, the initial phase of the SQL Utility deployment will focus on non-clustered SQL Server instances until support is available for clustered instances. Database mirroring and log shipping on Hyper-V are already supported.

Microsoft IT has identified several requirements and recommendations related to meeting the availability and business continuity attributes that pertain to the SQL Utility. These include:

• Uptime requirements:
  • Provide 99.9 percent uptime.
  • Reduce downtime for host upgrades and updates.
  • Provide contingency for instances and applications that need to be isolated or given additional resources to enable applications to meet their SLAs.
• General clustering requirements (requirements may change as clustering becomes a supported scenario for SQL Server guests and as best practices are developed):
  • Connect nodes through two independent networks.
  • Use the same communication setting for all network adapters on the same network.
  • Put each network on a unique subnet.
  • Configure two of the networks for internal cluster communication.
• Network adapter and network recommendations:
  • Set the correct network binding or connection order on each cluster node.
  • Set the default name for each network connection in a way that clearly identifies the use of each network.
  • Use a switch capable of creating virtual local area network (VLAN) segments to create isolated networks.
  • Manually set the speed and duplex settings on each network adapter and make them identical across all nodes in the cluster.
  • Enable Domain Name System (DNS) and/or Windows Internet Naming Service (WINS) on the public network.
  • Configure a default gateway on the public network adapters if cluster nodes use those adapters to communicate with clients or services on remote subnets.
  • Use static Internet Protocol (IP) addressing for public networks and private networks.
  • Use Internet Engineering Task Force (IETF) Request for Comments (RFC) 1918 addressing for private network numbering.
  • Do not use WINS, DNS, or a default gateway on the private network adapter.
  • Set the private network role to Internal Cluster Communications Only.
• Cluster service:
  • Private network: internal cluster communications.
  • Public network: all communications.
  • Private network should be the highest priority for internal cluster communications.
• Quorum: Provide a dedicated disk for quorum.
• Best practices:
  • Configure at least two of the cluster networks for internal cluster communication to eliminate a single point of failure.
  • Design each cluster network so that it fails independently of the other cluster networks.
  • Do not connect multiple adapters on one node to the same network when not using network adapter teaming.
  • Ensure that two or more independent networks connect the nodes of a cluster to avoid a single point of failure.
  • Use identical network adapters—model, driver version, firmware.

Of the requirements, the 99.9 percent uptime provides for about 43 minutes of downtime per month. Table 4 shows some common availability metrics.

Table 4. Availability in Terms of Time

Target	Allowed downtime
	Per year		Per month		Per day
	Minutes	Hours	Minutes	Hours	Minutes	Hours
99.000%	5,256.00	87.60	438.00	7.30	14.40	0.24
99.900%	525.60	8.80	43.80	0.73	1.44	0.02
99.990%	52.60	0.90	4.40	0.07	0.14	0.00
99.999%	5.30	0.10	0.40	0.01	0.01	0.00

Availability will be depicted on a Microsoft Service Level Management (SLM) scorecard based on data collected by Microsoft System Center Operations Manager 2007. Database availability is primarily used by database operations teams to ensure that they are meeting their agreed-to availability levels. Application availability is actually measured and consumed separately because it measures availability through all levels of the application stack rather than focusing on the data tier. Figure 8 shows an example of the SLM scorecard.

Figure 8. An example SLM scorecard

Environmental Sustainability

As SQL Server instances are retired and migrated to the SQL Utility service, power consumption decreases. On average, EOL servers use 369 volt amps, whereas new servers use 313 volt amps. The new servers can operate at higher temperatures, meaning that data-center cooling needs may decrease as new servers are deployed.

Servers deployed by the Compute Utility service occupy less than 1 RU, whereas a typical EOL SQL Server instance occupies 6.8 RU, on average. Because a typical new server provides around 188 CUs compared to an older server that provides 20–30 CUs, new servers can host several instances of SQL Server, which greatly reduces the footprint of the data center. Because Microsoft IT will purchase fewer physical servers in the new consolidated environment, there will be less hardware to recycle as EOL is reached.

Manageability

Manageability refers to Microsoft IT's ability to perform administration-related tasks on the platforms involved. Manageability is improved with existing IT utilities, the consolidated environment, and new and forthcoming technologies such as System Center Virtual Machine Manager, System Center Data Protection Manager, and System Center Operations Manager.

Several Microsoft IT requirements exist for manageability:

• Take advantage of current database administration and automation tools during phase 1.
• Identify and develop operational processes for onboarding to the SQL Utility service as well as for upgrades and failover.
• Support the previous, current, and future SQL Server versions.
• Support the previous, current, and future operating system IT Service Pack (IPAK).
• Support Microsoft CSS–supported environments.

Provisioning

Microsoft IT is undertaking an effort to reduce the number of manual steps involved in deployment of new SQL Server instances. These steps currently include:

1. Provisioning the operating system.
2. Installing software such as antivirus applications.
3. Deploying the SQL Server instance.
4. Configuring additional SQL Server–related items such as base administration jobs.
5. Ensuring that a test environment is configured to match the production deployment.

As the SQL Utility offering matures, the number of steps will decrease dramatically. Microsoft IT will have created standardized SQL Server guest templates ready for deployment, which means that deployment of a new server will simply be a matter of choosing an appropriate image from the VM library. Quantitatively, the time to deploy a new SQL Server guest will decrease by weeks compared to ordering and building a dedicated physical server.

The System Center Virtual Machine Manager library provides a standard build library that will enable IT developers, testers, and production support teams to provide a consistent experience throughout the SDLC. If a SQL Server instance needs to be relocated, it can simply be moved as a whole rather than requiring Microsoft IT to build a new physical server, reinstall all components, and restore the databases. The snapshot feature of Hyper-V also enables rollback of host changes. As part of the deploy/rollback planning process, taking a snapshot prior to a host change greatly reduces risks if the change needs to be rolled back to a previous state.

Performance

The primary performance requirement for the SQL Utility service is providing equal or greater CUs, memory, and disk performance as the SQL Server instance used on its previous host. Other requirements include:

• Database and applications must conform to Microsoft IT's host configuration policy.
• Average disk/second read and write operations:
  • Log: 1 millisecond (msec) or better (write operations are the most important)
  • Data: 8 msec or better for OLTP, 25 msec or better for Decentralized Software Services (DSS)
• Memory:
  • SQL Server Memory Grants Pending = 0
  • Paging File % Usage and % Usage Peak <70
  • Pages/sec <5
• Processor:
  • The sqlservr process % Processor Time <80
  • Processor queue length <4 per processor

The SQL Utility project will also establish performance baselines and provide a method to isolate instances or databases that are causing or having issues.

Microsoft IT uses the following performance-related guidelines for consolidation:

• Begin with small workloads, validate the deployment, and make appropriate changes to the deployment plan.
• Use Hyper-V pass-through disks or fixed virtual hard disks (VHDs) for storage, as shown in Figure 9. The use of fixed VHDs enables guests to be moved between hosts, though they do have slightly lower performance than pass-through. Dynamic VHDs are not recommended.

Figure 9. A Hyper-V SQL Server instance example

• Do not over-commit processors for guests. Use a one-to-one ratio of guest processor to logical processor as a starting point, and then verify the performance prior to making changes.
• Avoid emulated devices with Hyper-V in favor of synthetic/enlightened devices. Enlightened devices provide better performance and use fewer processor resources.
• Establish an operating level agreement (OLA) with performance requirements for the related providers. For example, the OLA with the Storage Utility would call for 1 msec average disk/second read and write for log and 8 msec for OLTP data.

Microsoft IT collected the following performance numbers by using the SQLIO Disk Subsystem Benchmark Tool. The chart on the left of Figure 10 depicts performance for an EOL server with four processors, 4 gigabytes (GB) of memory, and redundant array of independent disks (RAID)-10 Direct Attached Storage (DAS). The chart on the right of Figure 10 was for a Hyper-V server with two logical processors, 4 GB of memory, and a single VHD (SQL Server binaries, data, log, TempDB) in the SQL Utility environment shown in Figure 9. As Figure 10 shows, input/outputs (I/Os) per second were much higher and more consistent on the SQL Utility platform. Microsoft IT achieved similar tests and results by using various SQLIO parameters, including threads and I/O sizes.

Figure 10. Comparison of I/Os per second

As Figure 11 shows, latencies were much lower on the SQL Utility platform.

Figure 11. Latency comparison

Throughput was initially less on the SQL Utility platform. However, throughput slightly exceeded that of the older system after Microsoft IT modified the standard Hyper-V guest configuration to use multiple fixed VHD files with dedicated virtual controllers and channels. The engineering standard for Microsoft IT configuration of SQL Utility Hyper-V guest drives is as follows:

• Controller 1 Channel 0: D:\ Binaries (SQL Server Install)
• Controller 1 Channel 1: H:\ DATA
• Controller 1 Channel 2: I:\ DATA
• Controller 1 Channel 3: J:\ DATA
• Controller 1 Channel 4: K:\ DATA
• Controller 2 Channel 0: O:\ Log Drive
• Controller 3 Channel 0: T:\ TempDB Drive
• Controller 4 Channel 0: E:\ DB Backup Drive
• Controller 4 Channel 1: F:\ Tran Log Backup Drive

Microsoft IT gathered the performance numbers shown in Table 5 by simulating a stock trading system workload in the same single-VHD SQL Utility environment.

Table 5. Performance for a Stock Trading System Workload in the SQL Utility Environment

Counter	Average	Maximum
User connections	641	1,136
Transactions/second	3,063	3,673
Batch requests/second	1,470	2,058
Average disk sec/write	0.005	0.019
Average disk sec/read	0.003	0.010

Figure 12 shows the performance summary for various guest configurations.

Figure 12. Performance and configuration rationale

Predictability and Repeatability

Today's application deployment and SDLCs frequently introduce risk through the deployment strategy, because each server must be built separately and manually throughout the initial development, testing, and production environments and phases. Doing so creates room for inconsistencies between environments, which can result in unpredictable and unanticipated behavior between the environments. What works in development and test does not work in production because of a slight difference during an installation. By creating standard builds, the SQL Utility service will reduce or eliminate these inconsistencies at all points of the SDLC.

Reliability

Reliability was a key concern of SQL Server users when Microsoft IT was considering a consolidated environment. The concern was related to possible overconsumption of shared resources among the guests in the consolidated environment. Additionally, a concern existed that changes made to one or more guests would affect other guests within the consolidated environment. These concerns were alleviated by the use of Hyper-V technology, which provides operating system isolation.

The primary reliability metric used for the SQL Utility service is the mean time between failures (MTBF). The requirement is an MTBF of 182 days; however, because each layer of the SQL Utility service is fault tolerant, service interruptions will be minimized.

Scalability

The scalability requirements of the SQL Utility include:

• SQL Utility hosts must provide adequate CUs, memory, network bandwidth, and I/O throughput to ensure that consolidated instances meet customer-service-level expectations.
• The Storage Utility must meet or exceed existing storage performance.
• Flexibility must exist to move an instance to a new host if host capacity has been reached or exceeded.
• Performance and security requirements may justify adjustments to resource allocation at the Storage Utility layer. These changes are considered on an exception basis.

Scalability through virtualization technology such as Hyper-V is much easier than with dedicated physical server hardware. Scaling up or down in resources is a matter of allocating or de-allocating the resources between the host hardware and the guest. Guests can be moved between hosts as needed for capacity management or to compensate for unforeseen performance issues.

Security

The traditional security model within Microsoft IT enabled production support teams to have administration rights on host operating systems. The teams then assumed responsibility and accountability for any changes made to those hosts. The Hyper-V method of partitioning enables this model to continue because of the separation of guest operating system from host operating system. Production support teams will continue to have the same abilities.

The requirements for security are based largely on the policies laid out for Microsoft IT deployments, including the Microsoft Application Consulting and Engineering (ACE) policies such as the Line-of-Business (LOB) Application Policy and Host Configuration Policy. Many security policies and standards drive network, server, operating system, database, and application configurations that are outside the scope of this paper.

Monitoring

The SQL Utility must meet seven primary requirements for service monitoring and control:

• Measure and depict database availability.
• Measure and depict database reliability.
• Monitor SQL Server services and health.
• Monitor agent jobs.
• Measure and depict database performance.
• Monitor SQL Server configuration for security.
• Measure and depict database capacity.

The monitoring requirements remain primarily the same as in a non-consolidated environment. However, an additional amount of monitoring is required for the virtualization layer.

The monitoring services that Microsoft IT provides through System Center Operations Manager for the non-consolidated environment will continue to be used for the SQL Utility service. Additionally, Desired Configuration Management detection in Microsoft System Center Configuration Manager 2007 will enable Microsoft IT to find configurations that deviate from configuration standards.

Supportability

Microsoft CSS support is important to application owners. The SQL Utility will not deploy unsupported configurations of SQL Server. However, Microsoft IT will anticipate future supported configuration scenarios and refine the service accordingly.

Conclusion

Microsoft IT identified the opportunity to reduce operating and capital expenses by implementing a SQL Utility service. The SQL Utility service relies on the Compute Utility and Storage Utility and provides a layer of abstraction for SQL Server instances at Microsoft. By using the SQL Utility, Microsoft IT has begun to consolidate SQL Server hosts, thereby reducing the number of servers in the data center.

The SQL Utility service takes advantage of technologies such as Windows Server 2008 and Hyper-V to provide a highly available platform that improves manageability over previous implementations of SQL Server on dedicated physical servers. The standardization effort of the SQL Utility means that new SQL Server instances can be deployed faster and more reliably than before.

The SQL Utility provides and enhances business continuity, availability, and scalability while reducing the footprint of SQL Server instances in the data center. By consolidating IT layers, Microsoft IT is helping to maintain a sustainable environment.

The SQL Utility project is still in progress. However, it has met its early goals, and it will continue to provide increasing value to business users while reducing expenses.

For More Information

• "Running SQL Server 2008 in a Hyper-V Environment—Best Practices and Performance Recommendations" at http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspx
• SQLIO Disk Subsystem Benchmark Tool at http://www.microsoft.com/downloads/details.aspx?familyid=9A8B005B-84E4-4F24-8D65-CB53442D9E19&displaylang=en
• "Green IT in Practice: SQL Server Consolidation in Microsoft IT" (The Architect Journal, issue 18) at http://msdn.microsoft.com/en-us/architecture/dd393309.aspx
• "SQL Server Automation Scripts" (SQL Server Magazine, September 2007) at http://www.sqlmag.com/Article/ArticleID/96463/SQL_Server_Automation_Scripts.html
• "Virtualization Strategy Provides Tools, Processes, and Compliance Capabilities to Enhance Business Support and Drive Adoption" at http://technet.microsoft.com/en-us/library/cc713312.aspx
• "Identifying Server Candidates for Virtualization" at http://technet.microsoft.com/en-us/library/cc700692.aspx
• Virtualization case studies at http://www.microsoft.com/virtualization/case-studies.mspx
• Standard Performance Evaluation Corporation at http://spec.org

For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada information Centre at (800) 563-9048. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information through the World Wide Web, go to:

http://www.microsoft.com

http://www.microsoft.com/technet/itshowcase

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 2009 Microsoft Corporation. All rights reserved.

Microsoft, Hyper-V, SQL Server, Windows, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

All other trademarks are property of their respective owners.