microsoft sql server consolidation options considerations and case studies n.
Skip this Video
Loading SlideShow in 5 Seconds..
Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies PowerPoint Presentation
Download Presentation
Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies

Loading in 2 Seconds...

play fullscreen
1 / 56

Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies - PowerPoint PPT Presentation

  • Uploaded on

Required Slide. SESSION CODE: DAT308. Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies. Prem Mehra Program Manager Microsoft Corporation. Sung Hsueh Program Manager Microsoft Corporation. Session Objectives and Takeaways. Session Objective(s):

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
microsoft sql server consolidation options considerations and case studies

Required Slide


Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies


Program Manager

Microsoft Corporation

Sung Hsueh

Program Manager

Microsoft Corporation

session objectives and takeaways
Session Objectives and Takeaways
  • Session Objective(s):
    • Provide an overview of consolidation options
    • Compare each consolidation option
    • Highlight consolidation case studies
  • Know when to apply which consolidation option
  • Be prepared with best practices and recommendations
  • Consolidation Drivers and Options
  • Virtualization Technique
  • Instance & Database Technique
  • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement
  • Case Studies
  • Summary
consolidation drivers
Consolidation Drivers

Reduce Cap-Ex

Upgrade & standardize to fewer hardware

Reduce space, power and thermal needs

Reduce Op-Ex

Improve Hardware Utilization Efficiency

Improve Management Efficiency

Lowered cost and complexity for High Availability

Infrastructure Agility

Load Balancing & Dynamic Provisioning

Standardization of services

Underutilized hardware

Hardware computing capacity

Number of database apps

Overburdened Administrators

Number of DBA’s




consolidation options
Consolidation Options
  • Currently a variety of consolidation approaches exist and are utilized.
  • Typically, as isolation goes up, density goes down and operation cost goes up.

Higher Density, Lower Costs

Higher Isolation, Higher Costs



IT Managed














comparing consolidation approaches
Comparing Consolidation Approaches

Source :

sql server consolidation feature set
SQL Server Consolidation feature set


Live Migration

Windows Server Resource Manager (WSRM)

failover cluster

in guest VMs

New in

SQL 2008 R2 ®

System Center VM Manager

Microsoft Assessment and Planning (MAP)

Resource Governor

>64 logical processors

Sysprep SQL setup

Data Tier App

Utility Control Point and Managed Instances

Higher Isolation

Lower Density

Higher Density

Lower Isolation





  • Consolidation Drivers and Options
  • Virtualization Technique
  • Instance & Database Technique
  • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement
  • Case Studies
  • Summary
virtualization performance perspective
Virtualization: Performance Perspective
  • Topics of interest
    • Scalability of virtual instance
    • Comparison of virtual vs. native instance
    • Virtualization overhead: IO and CPU
  • Throughput Metric
    • Relative Throughput = Batches/sec/%Processor
  • Test workload
    • OLTP workload – Stock trading
    • Focus on typical consolidation workload
      • Low resource utilization
performance test configuration

Dynamically Expanding Virtual Hard Disk

Performance Test Configuration

On disk storage space needed

  • Host
    • Dell PowerEdgeR900 Intel Xeon 2.4GHz
      • (16cores, 64GB RAM)
    • HP DL785 (32cores, 128GB and 48cores, 256GB)
  • Storage
    • Hitachi Data Systems AMS1000
    • Hitachi Data Systems AMS2500
  • Virtual Machines
    • 4 Virtual Processors, 14GB RAM, 12 GB for SQL

Passthroughand Fixed-Size VHDs (2 Data, 1 Log)

      • Dynamic VHD not used, better performance in Win2008R2

VHD Size

Fixed Sized Virtual Hard Disk

On disk storage space needed

VHD Size

passthrough vs fixed size vhd
Passthrough vs. Fixed Size VHD
  • VHD’s on Shared Storage vs. Dedicated Spindles using Passthrough Disks
  • Measuring average reads per second vs. latency
  • VHDs on shared disks has slight latency overhead and less throughput
    • Graph bars = Reads/sec
    • Lines = Avg. Disk/sec Read (.001 = 1 ms)
transaction response time with slat
Transaction Response Time (with SLAT)
  • Transaction response time comparable
    • 32 Cores 128 GB Server. Guest: 4 Core 7 GB per VM, fixed size VHD
    • CPU over-commit
    • Sub seconds application response time
    • Test your own workload, may scale differently SLAT: Second Level Address Translation (Hardware Assist)
best practices and recommendations 1
Best Practices and Recommendations 1
  • Running SQL Server workloads within Hyper-V guest VM’s is a good option for production environment
    • When compared against native the same throughput can be achieved within a guest VM at a cost of slightly increased CPU utilization
    • Hyper-V benefits from newer generation of processor architecture (SLAT, Snoop filter)
    • Assuming limitations of Guest VM meet requirements of the workload
      • Proper hardware sizing is critical to SQL Server performance
      • Test/Monitor your workloads
  • CPU Resources Over Commit on case by case basis for Higher Density
    • May introduce noticeable performance overhead when all workloads are busy

Best Practices and Recommendations 2

  • Use synthetic devices for best performance
    • Synthetic devices utilize a VSC (virtual service client) and VSP (virtual service provider) to pass requests over a VMBus to the root partition
      • Everything occurs in kernel mode once initial request is passed from VM
    • Synthetic devices significantly perform better and use less CPU overhead than emulated devices
    • Emulated devices should be avoided when possible
      • May be needed during initial configuration of guest VM
    • Synthetic devices are installed with integration components
  • Passthrough and Fixed Size VHD for Better I/O performance
    • IO Performance Impact is minimal
    • SQL IO performance and sizing recommendations apply
    • Dynamic VHD not recommended for SQL Server deployments
  • Proper sizing of memory capacity.
    • Memory is allocated for VMs in a static fashion and can only be modified when a guest is offline
  • CPU Affinity Not Supported
  • Network intensive workload might experience more overhead
high availability for sql server in virtualized environments
High Availability for SQL ServerIn Virtualized Environments
  • Consolidation Increases the importance of High Availability Features
    • Consolidation serves to increase cost for a single system failure
    • Increasing focus on planned outages vs. unplanned outages

Guest Cluster

Guest Cluster









Shared Storage


Host cluster


Paths to storage

Shared Storage

iSCSI, SAS, Fibre

Guest Clustering

Live Migration & Host Clustering

sql live migration with data replication
SQL Live migration with Data Replication


(Domain Controller)

(Command View)

SQL Server DB

SQL Client App

Web server


HP Cluster Extension

Hyper-V Cluster

Virtual Machines

Virtual Machines

Live Migration

DR Group 003

IP network

EVA 4400

DR Group 002

DR Group 001


VHDs of all VMs




EVA 4400

Domain Controller


SQL Client App

OS Disk 30GB

SQL Server DB

G:\ OS Disk 30 GB

K:\ DatabaseDisk 100 GB

Web server

OS Disk 30GB

creating and managing virtual machines
Creating and managing Virtual Machines

Optimize Resources

  • Centralized virtual machine deployment and management for Hyper-V, Virtual Server, and VMware ESX servers
  • Fast and reliable P2V and V2V conversion
  • Intelligent placement of Virtual Machines
  • Comprehensive application and service-level monitoring with Operations Manager and Integrated Performance and Resource Optmization (PRO) of VMs

Leverage Skills

  • Familiar interface, common foundation
  • Monitor physical and virtual machines from one console
  • Fully scriptable using Windows PowerShell

Increase Agility

  • Rapid provisioning of new virtual machines with templates
  • Centralized library of infrastructure components
  • Leverage and extend existing storage infrastructure and clusters
  • Allow for delegated management and access of VMs

Reduce Cap-Ex

Reduce Op-Ex

Agile IT

  • Consolidation Drivers and Options
  • Virtualization Technique
  • Instance & Database Technique
  • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement
  • Case Studies
  • Summary
managing resources relational server cpu
Managing Resources Relational Server - CPU
  • Windows System Resource Manager (process level)
    • CPU management across multiple instances or other processes on same OS
  • Affinity mask (sp_configure option)
    • Process partitioning across CPU’s exposed to OS
  • Resource Governor (SQL Server 2008)
    • CPU/Memory management within an instance
  • Software Non-Uniform Memory Access (Soft NUMA)
    • Application partitioning across CPU’s utilized by a SQL instance
managing resources relational server memory
Managing Resources Relational Server - Memory
  • Grant the SQL Server service account “Lock pages in memory” privilege
  • Determine total RDBMS memory size for each instance (Msql)
    • Ensure Msql < Total Server Memory – ~1GB (per 8-16GB) for operating system – Max memory required by other apps or instances
  • Reserve enough room outside of buffer pool for
    • SQL Server Threads
      • Threads will consume 4 MB each on Itanium, 2 MB on X64, 1 MB on 32-Bit
      • (= 1GB for 256 Worker Threads on Itanium)
    • XPs, In-Proc OleDB drivers, CLR GC heap, etc.
    • NOTE: On 64-bit memory outside of Buffer Pool cannot be limited
  • Place a memory ceiling on each relational server instance
    • Optionally, to ensure a certain amount of memory resources set ‘min server memory’
managing resources relational server i o
Managing Resources Relational Server – I/O
  • Shared storage is a likely reality
    • Dedicated physical resources (spindles can provide more predictable performance)
  • Size properly, remember capacity is only one dimension
    • IOPs & Bandwidth
  • Monitor response times proactively (Averages over time)
    • Increasing response times with same I/O level likely indicates issues with shared storage
    • 1 - 5 ms for log (ideally 1 ms or better)
    • 5 - 20 ms for data (OLTP) (ideally 10 ms or better)
    • <=25-30 ms for data (DSS) (partly determined by total bandwidth)
  • Follow deployment best practices
  • Storage Top 10 Best Practices
temp db usage
Temp DB Usage
  • Consolidating SQL Server databases with different collations
    • Can encounter problems since Temp DB will retain the default collation of the model database
    • For more information on consolidated databases with different collations see SQL Server Books Online topic “Mixed Collation Environments”
  • Becomes much more significant in a consolidation environment
  • Be sure to tune Temp DB for proper sizing as well as performance
  • Temp DB utilized for by many features of SQL Server
temp db isolation and configuration
Temp DB: Isolation and Configuration
  • Temp DB placement (dedicated versus shared physical disks)
    • Unless you understand Temp DB I/O characteristics, it may be better to allocate Temp DB on spindles for data and indexes to utilize more cumulative disks
  • Best Practice: Temp DB one data file of equal size per CPU (core) on host server
    • Pre-size data/log files—do no rely on AUTOGROW
  • Monitor and understand your own Temp DB usage
    • SqlServer:Transactions/FreeSpace in Temp DB (KB)
    • Related dynamic management views (DMVs)
      • sys.dm_db_session_space_usage, sys.dm_db_task_space_usage, sys.dm_exec_requests
  • Consolidation Drivers and Options
  • Virtualization Technique
  • Instance & Database Technique
  • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement
  • Case Studies
  • Summary
the sql server utility
The SQL Server Utility
  • Central point of reasoning for multi-server management
  • Simple to setup and use - a key advantage

Managed Instances








Management Studio

dac what is a data tier application
DAC: What is a data-tier application?
  • New unit of deployment for T-SQL apps
    • An “MSI” for T-SQL apps
  • Facilitates interaction between application developer and DBA
  • Core parts of the DAC
    • DAC metadata
    • Schema definition
    • Deployment requirements
  • Set of associated services


DAC Metadata

(Name, Version, …)

Definition (Schema)


Indexes, Partitions



Tables, Views, Constraints, SProcs, UDFs, Users, Logins

DAC Deployment Profile

Deployment Requirements

Post-Deployment Scripts

DAC = Data-tier Application Component => the “MSI” like package

Data-tier app => the deployed DAC running on a managed instance


Data-tier Application




SQL Server Management Studio


Dev DB


Deploy /

Upgrade DAC

Reverse Engineer DAC


SQL Server Management Studio





Visual Studio 2010

Manage, Register, Uninstall, Extract, Upgrade DAC



Define Target Pre-Reqs





Deploy /

Upgrade DAC


Hand-offto DBA


+ Build


Prod DB





Managed Server Group

health state visualization
Health State Visualization


List View

Detail View

measure data visualization
Measure Data Visualization

CPU Utilization

Storage Utilization

utility dimensions and measures
Utility Dimensions and Measures





CPU Utilization



Disk Space Utilization





limitations and support
Limitations and Support
  • Number of managed instances
    • Enterprise Edition UCP is limited to 25 managed instances
    • Tested 200 managed instances per UCP
  • Version of managed instances
    • SQL Server 2008 R2
    • SQL Server 2008 PCU2
capacity planning
Capacity Planning

* recommended to support maximum scale limits.


Application and Multi-Server Management

Application and Multi-Server Management





End-to-End Service Management

Data Platform Resource Optimization

  • Datacenter and cross-platform
  • Health monitoring of distributed applications
  • SQL Server RDBMS focused
  • Central evaluation of utilization policies



SQL Server



SQL Server

Health, Breadth/Composable

Planning + Trend Analysis, Depth/Domain-specific

  • Consolidation Drivers and Options
  • Virtualization Technique
  • Instance & Database Technique
  • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement
  • Case Studies
  • Summary
case study 1 msit sql consolidation
Case Study 1: MSIT SQL Consolidation

Microsoft IT Infrastructure Overview

  • Pre-Consolidation State
    • ~2,700 Applications in MSIT Portfolio
    • ~4797 SQL Server Instances
    • ~100,000 databases
    • ~20% end-of-life hosts/year
    • ~10% CPU utilization across hosts
  • Consolidation Approach
    • Microsoft IT evaluated database, instance and host based consolidation
  • Resource Management Approach
    • Resource Management effectiveness considered critical issue
case study 1 msit sql consolidation1
Case Study 1: MSIT SQL Consolidation
  • Consolidation Strategy
    • Host Consolidation utilizing Hyper-V
    • Target of 6 to 1 consolidation ratio
    • Fixed Virtual Drives (VHDs) over Dynamic and Pass Through
  • Consolidation Approach
    • Decision starting point was instance based consolidation
    • Evaluation led to decision change: Hyper-V based host consolidation
      • Simplicity and ease of deployment
case study 1 sql utility configuration guests
Case Study 1: SQL Utility Configuration - Guests
  • Fiber Channel Only
  • No CPU over-commitment
  • Fixed VHDs only
  • 4 SCSI controllers
    • Controller 1 Channel 0 – D:\ Binaries (SQL 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
    • Controller 3 Channel 0 – T:\ Tempdb
    • Controller 4 Channel 0 – E:\ DB Backups
    • Controller 4 Channel 1 - F:\ Tran Log Backups
case study 2 overview
Case Study 2: Overview
  • A large US Manufacturer
  • About 4000 instances, 1900 managed by DBA, 4100 databases
  • Goal: Consolidate Tier-2 and 3 databases.
  • Motivation: TCO, Chargeback to user department
  • 4 to 5 months into the process(3rd Quarter 09)
case study 2 strategy observations
Case Study 2: Strategy & Observations
  • Strategy:
    • Database consolidation into fewer instances
    • Expectation: Fewer than 1000 instances
    • A Dev/Test Farm & a Production Farm
    • 4 Socket Quad Core (16 core) Servers
    • HA/DR: Synchronous Mirroring with Witness
      • Dev/Test Farm – all in one datacenter
      • Production – in two data centers
  • Observation: A data point (3rd Quarter 09)
    • Consolidated 250 to 300 databases on 4 to 5 servers
    • On average 60 databases per instance, 88 in one
case study 2 lessons learned
Case Study 2: Lessons Learned
  • Lessons Learned:
    • Engaging and getting attention and time of the application teams is a challenge. High level management support is essential
    • Technical solution is comparatively easier to design and manage
      • Kept technical solution simple and standardized
      • Resource Governor used to assign higher priority to specific connections
      • Well defined processes for maintenance management
    • Roles: Divided administrative responsibilities:
      • Systems Administration – Responsible for instance related tasks and got administrative privileges. Responsible for the node& instance oriented tasks. E.G., Restart of a server after node failure, Monitoring of the instance, Resource Governor, etc.
      • Database Administrators got DBO privileges for their databases. Responsible for DB Schema, Backup, Recovery, DBCC, Data Integrity, etc.
    • To minimize costs, installed on 16GB of Memory. As of the last observation point: was adequate for the Tier 2 and 3 applications
case study 3 overview
Case Study 3: Overview
  • A large US enterprise from Finance/Insurance industry
  • About 3000 instances
  • Goal: Consolidate Tier-2 Databases
  • Motivation – TCO, and achieve approximately 60% CPU utilization
  • Status (3rd Quarter 2009):
    • About 50% instances consolidated
    • About 6.7 instances per server
case study 3 strategy observations
Case Study 3: Strategy & Observations
  • Strategy:
    • More instances per server
    • Evaluate Database level consolidation later
    • Migrate to SQL Server 2005 and then consolidate
      • Started the process in first quarter of 2008 (prior to SQL Server 2008 availability)
      • Certifying SQL Server 2008 for production
    • Failover Clustering for HA
  • Observation:
    • About 45% instances already consolidated (3rd Quarter 2009)
    • Standardized on HP BL685 Blades
      • 4-way dual core 32 GB – 4 Production or 8 Non-production instances.
      • 4-way quad core 96 GB – 10 Production or 20 Non-Production Instances.
    • Expect finishing by middle of 2010
case study 3 lessons learned
Case Study 3: Lessons Learned
  • To gain experience & build confidence initially consolidating infrastructure oriented databases
    • Subsequently, Line of business applications (LOB)
    • Infrastructure DB consolidation helped in achieving LOB teams cooperation
    • TCO helped in getting management support
  • Reboots associated with adding more instances & maintenance negatively impact HA
  • Consolidation Drivers and Options
  • Virtualization Technique
  • Instance & Database Technique
  • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement
  • Case Studies
  • Summary
  • Running SQL Server workloads within Hyper-V guest VM’s can be a good option for some production work load
  • Database level consolidation provides benefit of higher density
  • SQL Server 2008 R2 Utility Control Point (UCP) provides dashboard and drill down utilization views of instances and applications
references need more info
ReferencesNeed more info?

High Performance SQL Server Workloads on Hyper-V SQL Server 2008 in Hyper-V Best Practices Server Consolidation Guidance TechNet Webcast: Deep Dive: Microsoft Virtualization Best Practices

SQL Server 2008 R2 Manageability

Data-tier Applications in SQL Server 2008 R2

“How do I …” videos Search for “UCP” and “DAC” at

dat track scratch 2 win

Required Slide

Track PMs will supply the content for this slide, which will be inserted during the final scrub.

DAT Track Scratch 2 Win
  • Find the DAT Track Surface Table in the Yellow Section of the TLC
  • Try your luck to win a Zune HD
  • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win

Required Slide



  • Sessions On-Demand & Community
  • Microsoft Certification & Training Resources

  • Resources for IT Professionals
  • Resources for Developers


Required Slide

Complete an evaluation on CommNet and enter to win!


Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st

You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year


© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. 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 provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.