1 / 44

Consolidation and Virtualization with SQL Server

Consolidation and Virtualization with SQL Server . Melbourne User Group September 10, 2009. Rob Reinauer Product Unit Manager SQL Server Engine. Agenda. SQL Server Consolidation Today Virtualization and Microsoft Hyper-V Architecture Capabilities with SQL Server

huy
Download Presentation

Consolidation and Virtualization with SQL Server

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Consolidation and Virtualizationwith SQL Server Melbourne User Group September 10, 2009 Rob Reinauer Product Unit Manager SQL Server Engine

  2. Agenda • SQL Server Consolidation Today • Virtualization and Microsoft Hyper-V • Architecture • Capabilities with SQL Server • Managing virtualized environments • Performance of SQL Server with Hyper-V • Scalability across multiple VMs on a system • IO Performance • Achieving Scalability with Virtualization • CPU, Memory, Network and Disk subsystems • Microsoft IT Case Study • Questions

  3. Forces Driving Consolidation • Hardware Utilization Efficiency • Datacenter deployment efficiency • Power Utilization • Hardware standardization initiatives • Management Efficiency • Fewer physical servers to manage and maintain • Centralized management of multiple/many servers • Infrastructure Agility • Load Balancing • Lowered cost and complexity for High Availability

  4. SQL Server Consolidation Today • Currently a variety of consolidation strategies exist and are utilized. • Typically, as isolation goes up, density goes down and cost goes up. Higher Density, Lower Costs Higher Isolation, Higher Costs IT Managed Environment Virtual Machines Databases Schemas Instances MyServer Sales_1 Consolidate_1 Marketing_1 Online_Sales DB_1 ERP_10 DB_2 ERP_10 DB_3 Microsoft Confidential

  5. SQL Server Consolidation Today • Schema level consolidation • Multiple databases are collapsed into a single database • Forces common security model on all applications • Typically requires changes to application and scripts • Many opportunities for subtle cross interactions • Namespace collisions and conflicts • Unexpected dependencies / performance contentions Higher Density, Lower Costs Higher Isolation, Higher Costs Microsoft Confidential

  6. SQL Server Consolidation Today • Database level consolidation • Multiple databases hosted on 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. Higher Density, Lower Costs Higher Isolation, Higher Costs Microsoft Confidential

  7. SQL Server Consolidation Today • 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 • System Memory and CPU are typical density limiters Higher Density, Lower Costs Higher Isolation, Higher Costs Microsoft Confidential

  8. SQL Server Consolidation Today • 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 Higher Density, Lower Costs Higher Isolation, Higher Costs Microsoft Confidential

  9. What is Virtualization? • Multiple operating systems images supporting separate independent applications running simultaneously on the same computer system. • Strong hardware enforced isolation between the VMs One Physical Server Child Partition Child Partition Child Partition Server Server Server Root Partition SP2 RC Hypervisor Devices Processors Memory Hardware

  10. Virtualization Scenario: Running one application on one OS • Windows Server 2008 is installed on the bare system, Hyper-V enabled One Physical Server Root Partition Hypervisor Devices Processors Memory Hardware

  11. Virtualization Scenario: Running one application on one OS • Windows Server 2008 and SQL Server 2008 are installed or imaged into a child partition. • Windows Server 2008 is installed on the bare system, Hyper-V enabled One Physical Server Child Partition Server Root Partition Hypervisor Devices Processors Memory Hardware

  12. Virtualization Scenario: Deploying Test Bed Environments • We would like to try out our application running against SQL Server 2008 running on Windows Server 2008 SP2 RC. • But… we need to keep our existing application online and running. • New OS and SQL Server installed or imaged into new child partition One Physical Server Child Partition Child Partition Server Server Root Partition SP2 RC Hypervisor Devices Processors Memory Hardware

  13. Virtualization Scenario: Consolidating Legacy Applications • We would like to retire old hardware servers • But… we don’t want to purchase new hardware or go through a re-verification on new operating system and SQL Server versions. • System Center VMM migrates a physical server image to a Hyper-V partition. One Physical Server Child Partition Child Partition Child Partition Server Server Server Root Partition SP2 RC Hypervisor Devices Processors Memory Hardware

  14. Hyper-V Architectural overview • The Hyper-V Hypervisor runs directly on the hardware • Provides virtualized execution environment for operating systems • Operating systems and applications run in partitions on top of the Hypervisor • Enlightenments provide fast paths to mitigate overhead

  15. Windows Server 08 R2 Hyper-V Improvements

  16. 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 • Shared Disk Environments - SANs, iSCSI, NAS, • Windows Fail-Over Clustering • Host based Clustering • Child partition based Clustering • Non-Shared Disk Environments • SQL Server Mirroring • Transactional and Peer-to-Peer replication • Log Shipping • Resource Isolation • Hyper-V Manager • SCVMM • SQL Server Resource Manager Microsoft Confidential

  17. High Availability In Virtualized Environments Windows Fail-Over Clustering - Host Level Clustering • One complete system fails over to another system • All VMs on the source system fail over • Each VM can target different physical systems for fail-over. • Each VM is re-started on fail-over target system • WFC migrates LUNs to target system(s) • Enables Live Migration in Windows 2008 R2

  18. SQL Server High Availability with Hyper-V Host Level Clustering - Partition movement, Drive routings Pre-Failover Windows Failover Clustering System 1 System 2 Child Partition 1 SQL 2008 Child Partition 2 SQL S005 Parent Partition No Child Partitions In Existence. E:\D1.VHD Lun1->E: D1.VHD->S: D2.VHD->S: E:\D2.VHD F:\D1.VHD Lun2->F: D1.VHD->T: D2.VHD->T: F:\D2.VHD Hypervisor LUN 2 LUN 1 SAN FiberChannel or iSCSI

  19. SQL Server High Availability with Hyper-V Host Level Clustering - Partition movement, Drive routings After-Failover Windows Failover Clustering System 1 System 2 Child Partition 1 SQL 2008 Child Partition 2 SQL S005 Parent Partition E:\D1.VHD Lun1->E: D1.VHD->S: D2.VHD->S: System Failure E:\D2.VHD F:\D1.VHD Lun2->F: D1.VHD->T: D2.VHD->T: F:\D2.VHD Hypervisor LUN 2 LUN 1 SAN FiberChannel or iSCSI

  20. High Availability In Virtualized Environments Windows Fail-Over Clustering - Guest Level Clustering • Clustering applied at the individual VM level • A single VM on the source system fails over to another VM • Each VM can target different physical systems for fail-over. • WFC does not migrate LUNs to target system(s) • iSCSI typically required

  21. SQL Server High Availability with Hyper-V Guest Partition Clustering - Partition movement, Drive routings Pre-Failover Windows Failover Clustering System 1 System 2 Child Partition 1 SQL 2008 Child Partition 2 SQL S005 Child Partition 1 Child Partition 2 Parent Partition Parent Partition D1.VHD->S: D2.VHD->S: D1.VHD->T: D2.VHD->T: Hypervisor Hypervisor LUN 2 LUN 1 SAN iSCSI

  22. SQL Server High Availability with Hyper-V Guest Partition Clustering - Partition movement, Drive routings After-Failover Windows Failover Clustering System 1 System 2 Child Partition 1 SQL 2008 Child Partition 2 SQL S005 Child Partition 1 SQL 2008 Child Partition 2 Parent Partition Parent Partition Partition Failure D1.VHD->S: D2.VHD->S: D1.VHD->S: D1.VHD->T: D2.VHD->T: D1.VHD->T: Hypervisor Hypervisor LUN 2 LUN 1 SAN iSCSI

  23. Hyper-V Live Migration • Delivered in Windows 2008 R2 • Migrates a running VM with essentially zero down time • Actually, several seconds of downtime, but connections are maintained • Allows essentially zero down time: • Hardware maintenance • Patching and servicing of host OS • Currently does not allow zero down time; • Patching and servicing of guest OS • Patching and servicing of applications within guest VMs

  24. Hyper-V Live Migration • How it works: step by step • Step 1: Snapshot VM memory • Copy partition memory from source VM to Destination Physical Server Physical Server Source Child Partition Destination Child Partition Root Partition Root Partition Changed Pages Storage Connections Network Connections Partition Memory Partition Memory Hypervisor Hypervisor Hardware Hardware Network Connections LUN 1 LUN 2 Shared Storage

  25. Hyper-V Live Migration • How it works: step by step • Step 2: Copy changed pages from source VM to destination Physical Server Physical Server Source Child Partition Destination Child Partition Root Partition Root Partition Changed Pages Changed Pages Storage Connections Network Connections Partition Memory Partition Memory Hypervisor Hypervisor Hardware Hardware Network Connections LUN 1 LUN 2 Shared Storage

  26. Hyper-V Live Migration • How it works: step by step • Step 3: Storage connections are migrated from the source VM to the destination VM. Physical Server Physical Server Source Child Partition Destination Child Partition Root Partition Root Partition Changed Pages Changed Pages Storage Connections Storage Connections Network Connections Partition Memory Partition Memory Hypervisor Hypervisor Hardware Hardware Network Connections LUN 1 LUN 2 Shared Storage

  27. Hyper-V Live Migration • How it works: step by step • Step 4: Network connections are migrated from source VM to destination VM Physical Server Physical Server Source Child Partition Destination Child Partition Root Partition Root Partition Changed Pages Changed Pages Storage Connections Storage Connections Network Connections Partition Memory Partition Memory Network Connections Hypervisor Hypervisor Hardware Hardware Network Connections LUN 1 LUN 2 Shared Storage

  28. Hyper-V Live Migration • How it works: step by step • Step 5: Destination VM is brought online, • Source VM is taken off line Physical Server Physical Server Destination Child Partition Source Child Partition Root Partition Root Partition Changed Pages Storage Connections Partition Memory Network Connections Hypervisor Hypervisor Hardware Hardware Network Connections LUN 1 LUN 2 Shared Storage

  29. Creating and managing Virtual Machines Hyper-V Role in Windows 2008 Server Manager • Centralized management of Virtual Machine creation, configuration and provisioning • Provides wizards for creation and provisioning of VMs and devices • Provides tools for creation and provisioning of Virtual Hard Drives (VHDs) • Limited import capabilities

  30. Creating and managing Virtual Machines System Center Virtual Machine Manager 2008 • Physical to Virtual and Virtual to Virtual image creation • In place physical systems can be provisioned to VMs • VMWare VMs can be translated to Hyper-V • Rapid Virtual Machine Provisioning – Virtual Machine Library • Multi-machine management across system and VMs • PRO - Performance and Resource Optimization and automation

  31. Creating and managing Virtual Machines System Center Virtual Machine Manager 2008 • System Center VMM Multi-Machine Monitoring and Management

  32. Evaluating Virtual Machine Efficiency If Consolidating Servers: 1. Is it more efficient to utilize multipleinstances (Scenario 1 below) or multiple virtual machines (Scenario 2) 2. How much overhead develops with larger numbers of instances and Virtual Machines? We wanted to address the following questions: Scenario 1 Scenario 2 SQL running in 8 Separate virtual machines running on a single system 8 Separate SQL Instances running on a single system R2 Hypervisor Hardware Hardware

  33. Evaluating Virtual Machine Efficiency • Hyper-V + Processor virtualization support = Huge Win! • SLAT (second level address translation) • 1% - 2% overhead per Virtual Machine • Measurement • Configuration • AMD Shanghai CPUs • SLAT support • No IOMMU • HP DL585 16 Cores • HP EVA8000 SAN • Windows 2008 R2 RC • VM Configurations • 4 Virtual Processors • 7GB RAM • Fixed VHDs • Approximately 10% CPU utilization / VM • Relative Throughput is normalized per VM B a t c h R e q u e s t s / S e c T o t a l C P U U t i l i z z t i o n

  34. Achieving Scalability With Virtualization Scalability requires balanced system capabilities CPU, Memory, Network and Disk subsystem bandwidth • CPU Utilization • The sum of the CPU utilizations of consolidated applications should not exceed the capacity of the target system • CPU cores can be explicitly allocated to Virtual Machines • CPU reserves, limits and relative priorities can be specified • CPU cores can be over committed • For example: • - On an 8 core system 4 VMs can each be allocated 4 virtual CPUs • System scheduling provides load balancing across the VMs • The administrator could specify limits and reserves across the VMs

  35. Achieving Scalability With Virtualization Scalability requires balanced system capabilities CPU, Memory, Network and Disk subsystem bandwidth • Memory Allocation and Utilization • Memory is allocated to virtual machines in fixed amounts • Assume ~1.5 GB consumed by Operating System and SQL Server instance code pages • Remaining memory utilized for data caching, mostly for SQL Server • Network subsystem bandwidth • Sufficient bandwidth must be provided for the sum total of the consolidated applications • Task Manager and Perfmon can provide statistics on application network utilization

  36. Achieving Scalability With Virtualization Scalability requires balanced system capabilities CPU, Memory, Network and Disk subsystem bandwidth • Disk subsystem bandwidth • I/O Bandwidth is typically gated on number of random I/Os not MB/Sec. throughput • Throughput typically scales with number of drives not with the speed of the attach bus • Throughput essentially never scales with the space of the storage subsystem • Disk attach bus bandwidth may become significant when: • The work loads generate significant percentages of sequential I/O • Multiple I/O intensive applications generate large numbers of I/Os • Solid State storage is being utilized • Perfmon can provide statistics on number of I/Os, latency and bandwidth utilization.

  37. Evaluating Virtual Machine Efficiency • Windows Server 2008 with fiber channel SAN achieves more than 200,000 I/O operations per second. • On a range of tests, Hyper-V based I/O workloads were between 88% and 99% of native performance Qlogic Fiber Channel SAN Performance Benchmark Hyper-V I/O operations per second compared to Native OS 90% IOPS 88% 97% 99% Non-IOMMU Enabled CPUs

  38. 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

  39. 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

  40. Case Study - Microsoft IT Consolidation Operational Results

  41. Summary • Hyper-V based virtualization is an effective server consolidation platform for SQL Server • Modern CPUs easily achieve efficient SQL Server scalability out beyond 8 virtual machines. • The Microsoft Hyper-V platform and SQL Server work together to implement robust high availability environments. • Windows 2008 R2 Live Migration offers effectively zero down time load migration between two systems. • Microsoft IT has documented a successful server consolidation strategy utilizing Hyper-V. • The System Center Virtual Machine Manager provides powerful tools to manage Virtual Machines • Migrating existing native system images to Hyper-V images • Administration and monitoring for multiple Virtual Machines • PRO - Performance and load balancing automation

  42. SQL Server Community Resources The Professional Association for SQL Server (PASS) is an independent, not-for-profit association, dedicated to supporting, educating, and promoting the Microsoft SQL Server community. • Become a FREE PASS Member: www.sqlpass.org/RegisterforSQLPASS.aspx • Learn more about the PASS organization www.sqlpass.org/ • Additional Community Resources • SQL Server Community Center www.microsoft.com/sqlserver/2008/en/us/community-center.aspx • TechNet Community for IT Professionals http://technet.microsoft.com/en-us/sqlserver/bb671048.aspx • Developer Center http://msdn.microsoft.com/en-us/sqlserver/bb671064.aspx • SQL Server 2008 Learning Portalhttp://www.microsoft.com/learning/sql/2008/default.mspx

  43. References • Running SQL 2008 in Hyper-V Environment • http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspx • Microsoft IT virtualization case study • http://technet.microsoft.com/en-us/library/dd557540.aspx • Green IT in Practices: SQL Server Consolidation in Microsoft IT • http://msdn.microsoft.com/en-us/library/dd393309.aspx • Qlogic Hyper-V IO performance benchmarks • http://www.qlogic.com/promos/products/hyper-v.aspx • Windows Server Hyper-V site • http://www.microsoft.com/windowsserver2008/en/us/virtualization-consolidation.aspx • System Center Virtual Machine Manager 2008 R2 site • http://www.microsoft.com/systemcenter/virtualmachinemanager/en/us/default.aspx • Hyper-V Technet center • http://technet2.microsoft.com/windowsserver2008/en/servermanager/virtualization.mspx • SQL Server 2008 Business Value Calculator: • http://www.microsoft.com/sqlserver/2008/en/us/value-calc.aspx

  44. Questions?

More Related