Building SQL Server Private Clouds with Hyper-V and System Center Existing SQL Server Consolidation Strategies • Currently a variety of SQL Server.
Download ReportTranscript Building SQL Server Private Clouds with Hyper-V and System Center Existing SQL Server Consolidation Strategies • Currently a variety of SQL Server.
Building SQL Server Private Clouds with Hyper-V and System Center
Existing SQL Server Consolidation Strategies • • • Currently a variety of SQL Server consolidation strategies exist and have been utilized.
Typically, as isolation goes up, density goes down.
Isolation concerns include: Resources contention, name spaces, security domains, security effectiveness Dedicated Machines Virtual Machines Instances Sales_1 Marketing_1 Online_Sales ERP_10 ERP_10 Databases
Consolidate_1
DB_1 DB_2 DB_3 Schemas 6/7/2010 2
Building SQL Server Private Clouds with Hyper-V and System Center
What SQL Server Recommends for Consolidation Today
Virtual Machine Consolidation
• Strong isolation between applications • Near perfect application compatibility • Zero down time migration of execution loads • Out of the box high availability configuration • Online Physical to Virtual conversion • Simplified and accurate billing and chargeback mechanisms • Minimal and rapidly diminishing overhead cost Dedicated Machines Virtual Machines Schemas Instances Sales_1 Marketing_1 Online_Sales ERP_10 ERP_10 Databases
Consolidate_1
DB_1 DB_2 DB_3 6/7/2010 3
Building SQL Server Private Clouds with Hyper-V and System Center
High Level SQL Server Private Cloud Solution Architecture Management Studio Dynamic Infrastructure Toolkit Service Level Dashboard for SC Security Compliance Manager Others Compliance Management
Provisioning Engine
Database Reporting Components Database Reporting Components Database Reporting Database Reporting Components SQL server Reporting Services Reporting Services Operating System Operating System SSIS Integration Packages SQL server Integration Services Operating System Data Base Application Data Base Application Components SQL server Data Base Data Base Application Components SQL server SQL server SQL server Operating System Microsoft SharePoint Server 6/7/2010 4
Building SQL Server Private Clouds with Hyper-V and System Center
A SQL Server, Hyper-V and System Center Implemented Private Cloud Solution • Provides a consolidation platform for SQL Server products utilizing Windows Server, Hyper-V and System Center • Provides an extremely high level of application and environmental compatibility • Multi-server management capabilities provided through SQL Server Utility Explorer and System Center solution stack • Both homogeneous and heterogeneous hardware and software environments are enabled • Suggested guidance to provision a single data-tier application per child partition • Additional capabilities are provided by Microsoft Solution Accelerators • Infrastructure provisioning, SLA definitions & reporting, compliance verification, chargeback & billing • Detailed white papers, best practices and step by step guidance 6/7/2010 5
Building SQL Server Private Clouds with Hyper-V and System Center
A SQL Server, Hyper-V and System Center Implemented Private Cloud Solution (continued) • Fully on-line load migration provided through Live-Migration • Transparent, automatic load balancing provided by System Center Virtual Machine Manager Performance Resource Optimization (PRO) • Transparent high availability provided through Windows Failover Clustering • Existing SQL Server High Availability mechanisms supported, but less automation in setup • Consistent execution environment across SQL Server products • • SQL Server Relational Data Base Engine SQL Server Integration Services • • SQL Server Analysis Services SQL Server Reporting Services • Applicable to high percentage of existing applications • • Larger scale applications limited by current 4 cores per Virtual Machine limitation Economical consolidation ratio target of up to 24:1 • • No technical limitations exist Big price point jump from 4GB DIMMs to 8GB DIMMs 6/7/2010 6
Building SQL Server Private Clouds with Hyper-V and System Center
6/7/2010
An existing, running system is completely transferred into a virtual machine
7
Building SQL Server Private Clouds with Hyper-V and System Center
Overview of Hyper-V and Virtualization Management Studio Dynamic Infrastructure Toolkit Service Level Dashboard for SC Security Compliance Manager Others Compliance Management
Provisioning Engine
Database Reporting Components Components SQL server Database Reporting Reporting Services Reporting Services Operating System Database Reporting Database Reporting Components Operating System SSIS Integration Packages SQL server Integration Services Operating System Data Base Application Data Base Application Components Data Base Data Base Application Components SQL server SQL server SQL server SQL server Microsoft SharePoint Server 6/7/2010 8
Building SQL Server Private Clouds with Hyper-V and System Center
Virtualization Adoption Characteristics • By the end of 2010, between 18% & 25% of enterprise workloads will execute within virtual machines.* • By the end of 2012, that adoption rate will be between 45% and 55%.* • Industry wide, for Enterprise IT Servers, IDC predicts 8:1 virtualized consolidation ratios by 2012.
^ • Existing typical SQL Server consolidation rates range from 4:1 to 18:1.
+ SQL Server Eco-system memory characteristics • • • ~90% of SQL Server systems have 32GB RAM or less ~65% of SQL Server systems have 8GB RAM or less ~20% of SQL Server systems have 2GB RAM or less *From data acquired across a range of analysts, published customer quotes and current customer adoption trends.
^IDC Virtualization Study 2008 + From customer case studies and industry surveys.
6/7/2010 9
Building SQL Server Private Clouds with Hyper-V and System Center
Microsoft IT Consolidation Initiative Pre-Consolidation State • • • • • • • ~4797 SQL Server Instances on dedicated systems ~100,000 databases Average ~10% CPU utilization across hosts ~15% of systems drove maximum CPU peaks between 1% and 5% ~60% of systems drove maximum CPU peaks between 5% and 20% ~20% of systems drove maximum CPU peaks between 20% and 50% ~5% of systems drove maximum CPU peaks above 50% Consolidation results • • Chose virtualization over instance based consolidation Achieved ~6:1 consolidation ratios 6/7/2010 10
Building SQL Server Private Clouds with Hyper-V and System Center
6/7/2010 Virtual Machine Efficiency
i t o n U t z z i l i
40 30 20 10 0 SQL Server & Hyper-V Scales With Minimal Overhead • Requires Modern CPUs - SLAT (second level address translation) • 1% - 2% performance overhead per Virtual Machine
T o l t a Virtualized SQL Server Instance Scalability
80 70
C P U
60 50 1VM 2VM 3VM Batch req/sec 4VM 5VM %CPU 6VM 7VM 8VM Relative Throughput 3 500,00 3 000,00
B a t c h
2 500,00 2 000,00 1 500,00 1 000,00
R e q u e s t s
500,00
/
0,00
S e c 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 11
Building SQL Server Private Clouds with Hyper-V and System Center
Microsoft 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 6/7/2010 12
Building SQL Server Private Clouds with Hyper-V and System Center
Virtual Memory Operation • Modern operating systems allow multiple processes/tasks to exist and execute on a single CPU • Windows utilizes hardware memory management to allow processes to see a linear memory address space The Virtual / Process view SQL Server Process 4 Process 2 SQL Agent Process 7
Child Partition
Operating System 6/7/2010 13
Building SQL Server Private Clouds with Hyper-V and System Center
Virtual Memory Operation • • • Windows allows a process to see a linear virtual memory space In reality, pages are spread throughout physical locations Virtual to physical mapping is expensive and is accelerated with MMU hardware The Virtual / Process view SQL Server SQL Agent Process 7 Process 4 Process 2 The Physical / real view Physical Memory Pages Operating System 6/7/2010 14
Building SQL Server Private Clouds with Hyper-V and System Center
Virtual Memory & Second Level Translation • • With Virtualization an additional level of mapping is required Second Level Address Translation (SLAT) provides the extra translation into Virtual Machine address spaces • This provides a HUGE performance advantage over non-SLAT enabled CPUs The Virtual / Process view Virtual Machine 1 The Physical / real view Physical Memory Pages Virtual Machine 3 Virtual Machine 1 Hyper Visor Operating System 6/7/2010 15
Building SQL Server Private Clouds with Hyper-V and System Center
Overview of the Data Tier Application Component environment Management Studio Dynamic Infrastructure Toolkit Service Level Dashboard for SC Security Compliance Manager Others Compliance Management
Provisioning Engine
Database Reporting Components Database Reporting Components Database Reporting Database Reporting Components SQL server Reporting Services Reporting Services Operating System Operating System SSIS Integration Packages SQL server Integration Services Operating System Data Base Application Data Base Application Components SQL server Data Base Data Base Application Components SQL server SQL server SQL server Operating System Microsoft SharePoint Server 6/7/2010 16
Building SQL Server Private Clouds with Hyper-V and System Center
Data-Tier Application Execution Environment • Compelling advantages to provisioning one data-tier application per child partition • • • • Settings & configuration isolation Resource isolation Billing & charge back effectivenes s Resource utilization overhead is rapidly diminishing • Consistent execution environment across SQL Server products • • SQL Server Relational Data Base Engine SQL Server Integration Services • • SQL Server Analysis Services SQL Server Reporting Services • Applicable to high percentage of existing applications • • Larger scale applications limited by current 4 cores per virtual machine limitation Economical consolidation ratio target of up to 24:1 • No technical limitations • Big price point jump from 4GB DIMMs to 8GB DIMMs 6/7/2010 17
Building SQL Server Private Clouds with Hyper-V and System Center
Application Deployment Options Existing Application Migration • Data-tier application component extraction and deployment • Introduced in SQL Server 2008 R2 • SQL Server Management Studio provides DAC wizards Existing Application Environment Migration • Existing physical environments can be imported directly • System Center Virtual Machine Manager provides Physical to Virtual conversion • Existing VMWare based installations can be directly imported • System Center Virtual Machine Manager provides VMWare to Hyper-V conversion Self Service Deployment • System Center VMM provides self service portal for virtual machine provisioning • Traditional application install can be targeted at Virtual Machine • Dynamic Infrastructure Tool Kit supplies additional roles and workflows around virtual machine and infrastructure provisioning 6/7/2010 18
Building SQL Server Private Clouds with Hyper-V and System Center
High Availability In the SQL Server Private Cloud • For shared storage environments, high availability provided by Windows Failover Clustering.
• Virtual Machine high availability is transparently provisioned when VMs are created.
• Planned failovers can be initiated individually for each virtual machine. • Failover targets are maintained by System Center Virtual Machine Manager intelligent placement mechanisms.
• Existing SQL Server High Availability mechanisms fully supported within Virtual Machines.
• Database mirroring, log shipping, Trans Replication • SQL Server health based availability requires SQL Server image with clustering enabled.
• For Heterogeneous software environments: • SQL Server failover mechanisms are appropriate to the SQL Server version running in virtual machine 6/7/2010 19
Building SQL Server Private Clouds with Hyper-V and System Center
Hardware utilization and load balancing • Live Migration provides fully online load balancing • Connections are maintained • All SQL Server transactions proceed normally – no failures, no roll backs • Requires shared storage implemented through NAS, iSCSI or FC.
• System Center Virtual Machine Manager PRO implements load balancing decisions • Reserves can be specified around CPU, Memory, Disk IOPS, Disk Bandwidth and Network bandwidth • Best load balancing targets are continuously and transparently maintained • Exploits existing hardware as well as new commodity hardware • Intelligent placement and reserves are based on available capacity • Strong hardware enforced resource and environmental isolation • Provisioning a single Data Tier Application per virtual machine greatly simplifies billing and chargeback.
• Existing OS level statistics provide rich, reliable and trusted statistics 6/7/2010 20
Building SQL Server Private Clouds with Hyper-V and System Center
6/7/2010
Multiple virtual machines load balance across multiple physical nodes based on changing CPU & IO loads
21
Building SQL Server Private Clouds with Hyper-V and System Center
Overview of the Monitoring and Management Infrastructure Management Studio Dynamic Infrastructure Toolkit Service Level Dashboard for SC Security Compliance Manager Others Compliance Management
Provisioning Engine
Database Reporting Components Database Reporting Components Database Reporting Database Reporting Components SQL server Reporting Services Reporting Services Operating System Operating System SSIS Integration Packages SQL server Integration Services Operating System Data Base Application Data Base Application Components SQL server Data Base Data Base Application Components SQL server SQL server SQL server Operating System Microsoft SharePoint Server 6/7/2010 22
Building SQL Server Private Clouds with Hyper-V and System Center
Centralized utility Monitoring
System Center Operations Manager
• • • • Huge volume of MOM pack support across industry software and hardware products Cascading health models from hardware level up to specific databases Health models can be fully customized to specific needs Microsoft will be providing specific guidance and recommendations here
Computer SQL Server 1 SQL Server 2 Database 1 Database 2 Database 3
6/7/2010 23
Building SQL Server Private Clouds with Hyper-V and System Center
Centralized utility Monitoring
System Center Operations Manager
(continued) • Customizable monitoring views and reporting • Web based console for remote access 6/7/2010 24
Building SQL Server Private Clouds with Hyper-V and System Center
Configuration & Servicing System Center Configuration Manager •
Operating System and Application Deployment
• • • Integrates with Virtual Machine Manager Software Library Provides customizable automation and workflows Automates construction of slipstreamed, patched images •
Software Update management
• • Integrates and orchestrates WSUS updates and patching Provides for automated maintenance window execution and reporting •
Asset intelligence and license management
• Deployment and usage reporting across hardware, software assets •
Desired Configuration Management
• Broad range of Microsoft and 3 • rd party Configuration Packs Provide specific compliance reporting • Security Compliance Manager Solution Accelerator 6/7/2010 25
Building SQL Server Private Clouds with Hyper-V and System Center
Identifying blocked transactions
6/7/2010 26
Building SQL Server Private Clouds with Hyper-V and System Center
Overview of Microsoft Solution Accelerators Management Studio Database Reporting Components Database Reporting Components Database Reporting Database Reporting Components SQL server Reporting Services Reporting Services Operating System Operating System Dynamic Infrastructure Toolkit Service Level Dashboard for SC Security Compliance Manager Compliance Management Others SSIS Integration Packages SQL server Integration Services Operating System
Provisioning Engine
Data Base Application Data Base Application Components SQL server Data Base Data Base Application Components SQL server SQL server SQL server Operating System Microsoft SharePoint Server 6/7/2010 27
Building SQL Server Private Clouds with Hyper-V and System Center
Solution Accelerators • Provide additional capabilities to help integrate MS technologies • Free downloads available from Microsoft.com
• Dynamic Infrastructure Tool Kit for System Center • Defines enterprise wide Infrastructure provisioning and self service models • Servicing procedures, approval & deployment workflows • Self service infrastructure deployment portal • Service Level Dashboard for System Center Operations Manager • Service level definitions for individual and groups of applications • SLA compliance and tracking reports • Security Compliance Manager • Centralized security baseline/standards management • Portfolio of recommended baselines • Baseline export to GPO, DCM and SCAP standard formats • System Center Configuration Manger Extensions for SCAP • Provides compliance support for federal Security Content Automation Protocol • Maps System Center DCM compliance reports to SCAP format 6/7/2010 28
Building SQL Server Private Clouds with Hyper-V and System Center
Solution Accelerators Dynamic Infrastructure Toolkit for System Center • • • Provisioning , Administration & Self Service Portals Lightweight infrastructure integration and provisioning engine Extensibility authoring workflow & customized user interface
Portals Provisioning engine Management Infrastructure Resources
6/7/2010 29
Building SQL Server Private Clouds with Hyper-V and System Center
Solution Accelerators Service Level Dashboards for System Center Operations Manager • • • • Free solution accelerator download Provides near real-time, end-to-end reporting on application response times Simulates user interactions through synthetic transactions Provides reporting and status to both System Center Operations Manager console and Web based portals 6/7/2010 30
Building SQL Server Private Clouds with Hyper-V and System Center
High Level SQL Server Private Cloud Solution Architecture Management Studio Dynamic Infrastructure Toolkit Service Level Dashboard for SC Security Compliance Manager Others Compliance Management
Provisioning Engine
Database Reporting Components Database Reporting Components Database Reporting Database Reporting Components SQL server Reporting Services Reporting Services Operating System Operating System SSIS Integration Packages SQL server Integration Services Operating System Data Base Application Data Base Application Components SQL server Data Base Data Base Application Components SQL server SQL server SQL server Operating System Microsoft SharePoint Server 6/7/2010 31
Building SQL Server Private Clouds with Hyper-V and System Center
Microsoft SQL Server, Hyper-V & System Center Resources SQL Server Consolidation Guidance http://msdn.microsoft.com/en-us/library/ee819082(SQL.100).aspx
High Performance SQL Server Workloads on Hyper-V http://download.microsoft.com/download/D/F/8/DF89D22D-39C8-4728-A990-3BD4467891B7/HighPerformanceSQLServerWorkloadsOnHyper V_Final.docx
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
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
6/7/2010 32
Building SQL Server Private Clouds with Hyper-V and System Center
Microsoft Solution Accelerator Resources Dynamic Infrastructure Toolkit for System Center Home Page
http://www.microsoft.com/virtualization/en/us/private-cloud.aspx
Service Level Dashboard for System Center Operations Manager
http://go.microsoft.com/fwlink/?LinkId=110337
Security Compliance Manager download
http://go.microsoft.com/fwlink/?LinkId=182512
System Center Configuration Manager Extensions for SCAP
http://go.microsoft.com/fwlink/?LinkId=113938
6/7/2010 33
Building SQL Server Private Clouds with Hyper-V and System Center
Questions?
6/7/2010 34
www.microsoft.com/teched http://microsoft.com/technet www.microsoft.com/learning http://microsoft.com/msdn
Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31
st