Gopal Ashok Program Manager Microsoft Corp Agenda SQL Server Consolidation Virtualization & Microsoft Hyper-V Architecture Consolidation Performance High Availability & Manageability offerings Achieving scalability with Virtualization Best Practices and.
Download ReportTranscript Gopal Ashok Program Manager Microsoft Corp Agenda SQL Server Consolidation Virtualization & Microsoft Hyper-V Architecture Consolidation Performance High Availability & Manageability offerings Achieving scalability with Virtualization Best Practices and.
Gopal Ashok Program Manager Microsoft Corp Agenda SQL Server Consolidation Virtualization & Microsoft Hyper-V Architecture Consolidation Performance High Availability & Manageability offerings Achieving scalability with Virtualization Best Practices and Recommendations SQL Relational Workload A Case Study Key Takeaways Forces Driving Consolidation Reduce Cap-Ex Underutilized hardware Overburdened Administrators 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 1990 2000 2010 Infrastructure Agility Load Balancing & Dynamic Provisioning Standardization of services Currently a variety of consolidation approaches exist and are utilized. Typically, as isolation goes up, density goes down and operation cost goes up. IT Managed Environment Virtual Machines Instances Sales_1 Databases Consolidate_1 Marketing_1 Online_Sales ERP_10 ERP_10 DB_1 DB_2 DB_3 Schemas Higher Density, Lower Costs Higher Isolation, Higher Costs SQL Server Consolidation SQL Server Consolidation Options Database level consolidation Multiple databases are collapsed into a single instance Common security, manageability and compatibility models required Might require changes to existing applications and scripts Lower manageability costs Better resource isolation and allocation capabilities through Resource Governor Virtual Machines Instances Sales_1 Databases Consolidate_1 Marketing_1 Online_Sales ERP_10 ERP_10 DB_1 DB_2 DB_3 Higher Density, Lower Costs Higher Isolation, Higher Costs SQL Server Consolidation Options Instance level consolidation Multiple Instances running on one system Full schema and security isolation Partial system resource & management isolation Potential conflicts in namespace, resources and system roles IO, System Memory and CPU are typical density limiters Virtual Machines Instances Sales_1 Databases Consolidate_1 Marketing_1 Online_Sales ERP_10 ERP_10 DB_1 DB_2 DB_3 Higher Density, Lower Costs Higher Isolation, Higher Costs SQL Server Consolidation Options Virtual Machine consolidation Strong isolation between applications Ease of capturing and moving execution loads Out of the box High Availability configuration Flexible storage management Fewer systems, but just as many OS images to manage Increased resource usage Virtual Machines Instances Sales_1 Databases Consolidate_1 Marketing_1 Online_Sales ERP_10 ERP_10 DB_1 DB_2 DB_3 Higher Density, Lower Costs Higher Isolation, Higher Costs Choosing the Right Consolidation Approach Evaluate key parameters for your environment Isolation between apps Security isolation Predictable Performance : Resource isolation HA : Failure isolation Density of apps Performance : Resource utilization efficiency Manageability Impact HA : mitigating single point of failure Time to Market How long does it take to consolidate? Can my solution Scale? Agenda SQL Server Consolidation Virtualization & Microsoft Hyper-V Architecture Consolidation Performance High Availability & Manageability offerings Achieving scalability with Virtualization Best Practices and Recommendations SQL Relational Workload A Case Study Key Takeaways Hyper-V Architectural Overview Root Partition Child Partition Child Partition Enlightened OS Non-Enlightened OS Server Applications Child Partition Server Windows Server 2008 R2 I/O Stacks Virtual Service Providers Windows Server 2008 I/O Stacks I/O Stacks TCP/IP , File systems TCP/IP , File systems Fast Path Device Emulation Device Emulation Drivers Drivers Virtual Service Clients Drivers VMBus VMBus Processors Hardware Kernel Mode Hypervisor Mode Hypervisor Devices User Mode Memory Agenda SQL Server Consolidation Virtualization & Microsoft Hyper-V Architecture Consolidation Performance High Availability & Manageability offerings Achieving scalability with Virtualization Best Practices and Recommendations SQL Relational Workload Analysis Services Workload A Case Study Key Takeaways Monitoring Performance: CPU Terminology Logical Processor: One logical computing engine in the OS, application and driver view Virtual Processor: Virtual logical processor (upto 1:8 over commit of LP) % Processor Time % Processor Time counters in either guest or root may not be accurate Hyper-V Processor Counters The best way to get true measure of CPU utilization Hyper-V Logical Processor: Total CPU time across entire server Hyper-V Virtual Processor: CPU time for each guest virtual processor Monitoring Performance: Storage Terminology Configuration Considerations Passthrough Disk: Disk Guest VM w/ Offline at Root Partition Use Physical disk counters within root partition Passthrough Disks to monitor I/O of passthrough disks Guest VM w/ VHD Use Logical or physical disk counters within guest VHD (Virtual Hard Disk) Fixed Size VHD : Space allocated statically Dynamic VHD : Expand on demand VM to monitor IO rates of a VHD Disk counters at the root partition provide aggregate IO of all VHDs hosted on the underlying partition/volume Either Very little difference in the values reported by the configuration counters from the root partition with those within guest VM Slightly higher latency values (Avg. Disk/sec Read and Write) observed within the guest VM SQL Server Consolidation Performance: Native vs. Virtual Configuration Workload : Stock trading application Hardware: PowerEdge R900 Intel Xeon 2.4GHz, 4 cores x 4 CPU = Total 16 cores Hitachi Data Systems AMS1000 Virtual Machines: 4 Virtual Processors, 14GB RAM, 12 GB for SQL Passthrough and Fixed-Size VHDs (2 Data, 1 Log) Results Relative Throughput = Batches/sec/%Processor Same throughput attainable There is more CPU overhead with hyper-v enabled or when running within a VM Passthrough Performance ~5% Better than fixed size VHDs Native Instances and Virtual Instances achieves the same level of scalability Performance and Scalability Comparison between Native and Virtual Instances: 16core 64GB RAM 12GB Per SQL Instance 60 2500 55 Batches/sec_Native 50 2000 Batches/sec_VM 45 Batches/sec/Inst_Native 40 1500 35 30 Batches/sec/Inst_VM % Processor_Native 25 1000 % Processor_VM 500 Relative Throughput_Native Relative Throughput_VM 20 15 10 5 0 0 1Instances 2Instances 3Instances 4Instances SQL Server Consolidation Performance: Native vs. Virtual (overcommit) Configuration Results Workload : Stock trading application Hardware: Relative Throughput = Batches/sec/%Processor PowerEdge R900 Intel Xeon 2.4GHz, 4 cores x 2 CPU = Total 8 cores Hitachi Data Systems AMS1000 Virtual Machines: 4 Virtual Processors, 14GB RAM, 12 GB for SQL Passthrough and Fixed-Size VHDs (2 Data, 1 Log) Same throughput attainable with CPU overcommit Additional overhead with over subscribe logical processors Taking into consideration of the additional overhead for capacity planning Native Instances and Virtual Instances achieves the same level of scalability Performance and Scalability Comparison between Native and Virtual Instances: 8core 64GB RAM 12GB Per SQL Instance 110 2000 100 1800 90 1600 80 70 60 50 1400 Batches/sec_VM 1200 Batches/sec/Inst_Native 1000 Batches/sec/Inst_VM 800 40 600 30 20 400 10 200 0 0 1 Inst 2 Inst 3 Inst 4 Inst Batches/sec_Native % Processor_Native % Processor_VM Relative Throughput_Native 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) Total Read IO’s per Second Dedicated Pass-through vs. VHDs on Shared Disks Total IO/s per Second and Disk Latency 8,000 0.01 6,000 0.008 0.006 4,000 0.004 2,000 0.002 0 0 Low OLTP Workload Med OLTP Workload High OLTP Workload Total Reads/sec (Dedicated LUNs) Transaction Response Time Configuration: Results: • OS: Microsoft® Windows Server® 2008 R2 Hyper-V™ • Hardware: • HP DL785 (32 core) Hitachi Data Systems AMS2500 Storage • Virtual Machines: 4 virtual processors and 7 GB RAM per virtual machine; Fixed size VHD Transaction response time comparable • sub seconds application response time • When system under stress, VM appears to have better scalability • Test your own workload, may scale differently 0.07 90 80 70 0.05 60 0.04 50 0.03 40 30 0.02 20 0.01 10 0 0 Inst2 Inst4 Response Time_VM Inst6 Inst8 Response Time_Native Inst10 Inst12 %Processor_VM Inst 14 Inst 16 %Processor_Native % Processor time Response Time (sec) 0.06 Agenda SQL Server Consolidation Virtualization & Microsoft Hyper-V Architecture Consolidation Performance High Availability & Manageability offerings Achieving scalability with Virtualization Best Practices and Recommendations SQL Relational Workload A Case Study Key Takeaways High Availability with Guest Clustering Using iSCSI Scenario Description: • Improve high availability with a combination of guest clustering and host clustering • Only iSCSI is supported for guest clustering • iSCSI Initiator runs within the VM allowing storage to be fully visible to the VM • Storage is fully visible to the guest cluster and enables high-availability of services and applications in the virtual layer • Support the use of multiple redundant paths using Microsoft Multipath IO (MPIO) or MCS (multiple connections per session) from within the VM VM Guest Cluster Guest Cluster • Provide fault tolerance both at application and host level • All applications can run in the context of the VM • Management efficiency based on SQL Server® and System Center management tools • Backup applications have full visibility to data within the application context • Storage providers such as MPIO, VDS, VSS run within the VM 2 1 Virtualization Benefits: Shared Storage Redundant Paths to storage iSCSI High Availability with Live Migration Scenario Description: • Manage high availability with multipathing and live migration for planned downtime situations, such as hardware and software maintenance • Migrating individual virtual machines (VMs) to other hosts within a cluster by using Cluster Shared Volume (in Windows Server® 2008 R2) • Use Microsoft ® System Center Virtual Machine Manager for migrations. System Center VMM can perform host compatibility checks before migrations and manage multiple Live Migrations with queues. • Nodes in cluster can be active-active • Ensure there is enough CPU capacity for the failover nodes in cluster Virtualization Benefits: • No loss of service during migration with live migration. • Improve availability with less complexity • Load balancing VMs across physical machines as needed • Better server utilization due to consolidation • Easier management through System Center VMM Consolidation increases the importance of HA, since there is a high cost to single system failure VM 11 Live Migration Host cluster Shared Storage iSCSI, SAS, Fibre 2 Creating and managing Virtual Machines • Maximize Resources – Centralized virtual machine deployment and management for Hyper-V, Virtual Server, and VMware ESX servers – Intelligent placement of Virtual Machines – Fast and reliable P2V and V2V conversion – Comprehensive application and service-level monitoring with Operations Manager – Integrated Performance and Resource Optmization (PRO) of VMs • 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 • Leverage Skills – Familiar interface, common foundation , powershell scripting Agenda SQL Server Consolidation Virtualization & Microsoft Hyper-V Architecture Consolidation Performance High Availability & Manageability offerings Achieving scalability with Virtualization Best Practices and Recommendations SQL Relational Workload A Case Study Key Takeaways Consolidation Hardware – NUMA & 64bit Memory SMP Front side bus contention increases w/ higher #CPUs CPU 0 CPU 1 CPU 2 CPU 3 NUMA NODE 0 NUMA CPU 5 CPU 6 CPU 7 NUMA NODE 1 CPU 0 CPU 2 CPU 1 CPU 3 Local Memory Access CPU 4 Memory Foreign Memory Access 4x local NUMA Node isolation presents inherent & significant advantages to software that is designed to take advantage of locality Microsoft Hyper-V localizes VM resource utilization to a NUMA boundary 64bit allows significantly more addressable space than 32bit Consolidation Hardware – SLAT With Virtualization an additional level of mapping is required in address translation Second Level Address Translation (SLAT) - 2nd generation virtualization technology in Intel VT-x with EPT and AMD –V with NPT chips accelerate VM performance to be almost on par with bare metal for common workloads Continuing innovations in hardware assists – Device and IO virtualization The Virtual Machine view Guest Physical view Guest Physical Memory Pages Guest Physical view Guest Physical Memory Pages Virtual Machine 1 Guest Physical view Guest Physical Memory Pages Virtual Machine 3 Virtual Machine 1 Host Physical / real view Physical Memory Pages Hyper Visor Operating System SQL Server Performance: SLAT Impact Configuration: Results: • OS: Microsoft® Windows Server® 2008 R2 Hyper-V™ • Hardware: • Increased throughput with consolidation • Near linear scale in throughput with no CPU over- HP DL585 (16 core) with SLAT HP EVA 8000 storage • Virtual Machines: 4 virtual processors and 7 GB RAM per virtual machine; Fixed size VHD commit • Improved performance with Windows Server 2008 R2 and SLAT processor architecture Throughput (Batch requests/sec) 3500 Virtual Instances Scalability % CPU 80 Almost Linear Scale No CPU over-commit 70 CPU over-commit 3000 60 2500 50 Heavy Load 2000 40 ) 1500 30 Moderate Load 1000 20 500 10 0 0 1VM 2VM Batch req/sec 3VM 4VM %CPU 5VM 6VM Relative Throughput 7VM 8VM Relative Throughput for Windows Server 2008 Low Load Consolidation Hardware – Snoop Filtering NUMA NODE 0 C0 C1 C2 C3 MCH0 C4 C5 C6 C7 MCH1 Memory Fetch addr (first time) 1. Issue Fetch to Memory Controller Hub (MCH) 2. Snoop local caches 3. Caches respond 4. Snoop foreign caches 5. Caches respond 6. Get data from memory controller NUMA NODE 1 Consolidation Hardware – Snoop Filtering NUMA NODE 0 Snoop Filter reduces internode traffic C1 C2 C3 MCH0 C4 C5 C6 C7 MCH1 Memory Fetch addr (first time) 1. Issue Fetch to Memory Controller Hub (MCH) 2. Check local snoop filter 3. Check foreign snoop filter 4. Get data from memory controller C0 NUMA NODE 1 SQL Server Performance : Snoop Filter Impact Configuration: • OS: Microsoft® Windows Server® 2008 R2 Hyper-V™ • Hardware: HP DL785 with and without snoop filter support (Shanghai vs. Istanbul) Hitachi Data Systems AMS2500 Storage Results: • Drop-in compatibility of Istanbul processors with existing infrastructure • AMD HyperTransport Assist (Intel snoop filter) feature huge benefit to SQL workload • • Virtual Machines: 4 virtual processors and 7 GB RAM per virtual machine; Fixed size VHD • keep cache coherency traffic between the two sockets from appearing on the external bus ~50% performance improvement Comparing Istanbul vs. Shanghai 100 90 94.05 82.12 80 70 60 90.28 93.12 4500 91.70 55% 4000 42% 57.84 57.84 5000 59.79 60.67 3500 Batches/sec_Shanghai 3000 %Processor Time_Istanbul %Processor Time_Shanghai Relative Throughput_Istanbul Relative Throughput_Shanghai 60.15 50 2500 40 2000 30 1500 20 1000 10 500 0 0 VM1 VM2 VM4 VM6 VM8 Batches/sec_Istanbul Agenda SQL Server Consolidation Virtualization & Microsoft Hyper-V Architecture Consolidation Performance High Availability & Manageability offerings Achieving scalability with Virtualization Best Practices and Recommendations SQL Relational Workload A Case Study Key Takeaways Best Practices and Recommendations 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 Integrated Component : “Enlightenments” Better IO performance Reduce memory access overhead 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 Case Study – 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 Instance based would utilize WSRM Hyper-V allows explicit allocation of CPU and IO channels Case Study – MSIT SQL Consolidation Microsoft IT Consolidation Conclusions • 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 • WSRM vs. Hyper-V ● Microsoft IT evaluated WSRM vs. Hyper-V & ultimately chose Hyper-V Case Study - Microsoft IT Consolidation Annual Operating Cost at 6:1 Consolidation $18,000,000 $16,000,000 $14,000,000 $12,000,000 $10,000,000 $8,000,000 $6,000,000 $4,000,000 $2,000,000 $0 Legacy Consolidated Annual Recycle Cost at 6:1 Consolidation Power and Cooling at 6:1 Consolidation $60,000 4,000,000 $40,000 3,000,000 2,000,000 $20,000 1,000,000 $0 Legacy Consolidated 0 Legacy Consolidated Key Takeaways No One-size Solution for Consolidation Hyper-V is a solid platform for SQL Server, both for scalability and performance Understand performance and functional needs of workloads being consolidated If possible, avoid consolidation of heterogeneous workloads on the same Windows Server I/O design is very significant Especially tempdb and log files Monitor I/O latency and ensure healthy I/O performance Avoid spindle sharing with other servers/applications when possible Follow Best Practices for storage Ensure No/minimal Contention for Memory Resources Determine and set upper memory limits for SQL Server and SSAS (both single and multiple instances) Take into account memory needed for OS, other applications Understand and test the impact of SSIS packages being run locally Ensure enough memory for CLR procedures Key Takeaways Resource Management Hyper-V CPU management tools to manage CPU resources at VM Level WSRM to manage multiple instances CPU resource Resource Governor to manage within an instance SQL Server Analysis Services Determine and set upper memory limits for SQL Server and SSAS (both single and multiple instances) SSAS consolidation enables better utilization of hardware (new or existing) SSAS consolidation requires planning to resolve resource contention and availability / single point of failure issues Taking advantage of New Processor Architecture SLAT Snoop Filter Share your Lessons Learned! SQL Solution Accelerators Infrastructure Planning and Design Guide Outline SQL Server 2008 infrastructure design decisions and ensure that business and technical requirements are met Offer easy-to-follow steps to design architecture including decision flows Download at www.microsoft.com/IPD Microsoft Assessment and Planning Toolkit Identify SQL instances automatically Assess Hyper-V virtualization candidates for server consolidation Download at www.microsoft.com/MAP Resources www.microsoft.com/teched www.microsoft.com/learning Sessions On-Demand & Community Microsoft Certification & Training Resources http://microsoft.com/technet http://microsoft.com/msdn Resources for IT Professionals Resources for Developers References Running SQL 2008 in Hyper-V Environment http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-venvironment-best-practices-and-performance-recommendations.aspx Green IT in Practices: SQL Server Consolidation in Microsoft IT http://www.msarchitecturejournal.com/pdf/Journal18.pdfhttp://msdn.microsoft.com/en-us/architecture/dd393309.aspx Support Policies of SQL Server in virtualized environments. http://support.microsoft.com/?id=956893 http://blogs.msdn.com/psssql/archive/2008/10/08/sql-server-support-in-a-hardwarevirtualization-environment.aspx Windows Virtualization Validation Program http://windowsservercatalog.com/svvp.aspx?svvppage=svvp.htm Windows Server Hyper-V site http://www.microsoft.com/windowsserver2008/en/us/virtualization-consolidation.aspx Hyper-V Technet center http://technet2.microsoft.com/windowsserver2008/en/servermanager/virtualization.mspx SQL Server 2008 Business Value Calculator: www.moresqlserver.com Complete an evaluation on CommNet and enter to win an Xbox 360 Elite! © 2009 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.