The SQL Server Database Consolidation Appliance Reference Architecture

2. SESSION CODE: DAT409 Rob Reinauer Product Unit Manager, SQL Systems Engineering Microsoft The SQL Server Database Consolidation Appliance Reference Architecture (c) 2011 Microsoft. All rights reserved.

3. SQL Server Private Clouds & The SQL Database Consolidation Solution Agenda • Private Cloud Solutions Overview • SQL Server Appliance Engineering • The SQL Server Database Consolidation Solution • The SQL Server DBC Solution Hardware Architecture • The SQL Server DBC Solution Software Architecture • Consolidation, Virtualization & SQL Server • Application Deployment & Compatibility • Performance and Capacity Planning

4. SQL Server Private Clouds & The SQL Database Consolidation Solution Private Cloud Value • Radically lower the cost for managing and maintaining compute infrastructures Mechanisms • Integrated management and monitoring • Elastic capacity provisioning • Automated load balancing and servicing • Self Service deployment and monitoring • Transparent, automated high availability • Metering and chargeback capabilities

5. Introducing: The SQL ServerDatabase Consolidation Solution A pre-built, optimized, SQL Server Private Cloud Solution

6. SQL Server Private Clouds & The SQL Database Consolidation Solution The SQL Server Database Consolidation Solution • Provides a complete, pre-built, integrated hardware & software solution for SQL Server database consolidation • Pre-installed and configured software stack utilizing Windows Server, Hyper-V, SQL Server and System Center • Pre-built, balanced hardware components based on HP blade servers and iSCSI storage solutions

7. SQL Server Private Clouds & The SQL Database Consolidation Solution The SQL Server Database Consolidation Solution • Provides an extremely high level of application and environmental compatibility • Each SQL Instance provisioned in a unique virtual machine • Physical to virtual DB server migration imports full execution environment • Balanced HW & SW design allows exceptional consolidation density • Between 20:1 and 50:1 should be typical • Integrated monitoring & management • Cascading health models from HW through SW stack • Automated servicing and patching

8. SQL Server Private Clouds & The SQL Database Consolidation Solution The SQL Server Database Consolidation Solution • Transparent automatic load balancing • Powered by Live Migration • Self service portal and Infrastructure provisioning workflows • Pre-provisioned HA for SQL instances • Chargeback & billing measurement and reports • SQL Instance discovery and consolidation characterization

9. SQL Server Private Clouds & The SQL Database Consolidation Solution The SQL Systems Engineering approach to appliance design Workload behavior drives architecture, hardware and software component specification. • Workload • Architecture • Hardware • Software

10. How do appliances supply value? Accelerate time to valueDesign, Build & Deploy in weeks rather than months Minimize Optimize Simplify Accelerate Custom-built solution Integrated & Optimized Appliance Assess and understand workload Design Design Choose appliance for workload 1 Define architecture 1 Acquire appliance Build Evaluate alternatives Install appliance Deploy Design specific implementation 2 Weeks Extract & load data 2 AcquireHW & SW components Build Stand-up in production Build solution 3 Use Monitor & Manage Months Load data Extract and manipulate data 3 Proof Of Concept & Validation Generate reports 4 Tune & Balance HW & SW Make decisions Deploy Integrate in environment 4 Burn in & Stability Monitor and troubleshoot Use Extract and manipulate data 5 Generate reports Make decisions

11. How do appliances supply value? Minimize riskDeploy with confidence Accelerate Minimize Optimize Simplify • Proven infrastructure built on best practices of leading experts • Secure, compliant infrastructure for fewer vulnerabilities • Single, trusted source of support for simpler resolution Workload requirements Reference architecture Application software Server, storage, networking Integrated and optimized by HP and MS experts Tested Tuned Validated Pre-configured and ready to run Single source for support

12. How do appliances supply value? Simplify operations Accelerate Minimize Optimize Enhance operations and lower TCO Simplify • Tight integration across applications and infrastructure • End-to-end, integrated management and virtualization consoles • Standards-based technology adapts easily for growth Integrated Management & Servicing Provision Manage Allocate physical and virtual resources within appliance Control and update devices from anywhere Monitor Optimize Get real-time insight to resource usage Control performance, consumption, and availability

13. How do appliances supply value? Optimized performance Accelerate Minimize Optimize System efficiency through balanced systems Simplify • Optimal mix of tuned hardware and software • High-performance, cost-effective infrastructure • Components jointly tested and optimized by HP & Microsoft • Validated in customer environments

14. The SQL Server Database Consolidation Solution: Hardware Architecture The SQL DBC Solution Hardware Architecture: Overview Full Rack Configuration Half Rack Configuration ProCurve 10Gbps switches ProCurve 1Gpbs switches HP C3000 Blade Enclosure 4x BL 465c G7 Blades (2P) • 10Gb Flex10 Enet HP C3000 Blade Enclosure 8x BL 465c G7 Blades (2P) • 10Gb Flex10 Enet • Storage Blocks: • P2000 iSCSI Disk Array • D2700 Disk Racks

15. The SQL Server Database Consolidation Solution: Hardware Architecture SQL Server Database Consolidation SolutionConnectivity Architecture Blade Blade Blade Enclosure Blade Blade Blade Blade Blade Blade User Workloads VC Ports VC Ports User Workloads User Workloads 2 2 2 10GbE Switch 10GbE Switch 2 KEY: P2000 MSA P2000 MSA Base Appliance D2700 JBOD D2700 JBOD 3 3 Full Rack Appliance P2000 MSA P2000 MSA 10 Gbit Enet 6 Gbit SAS D2700 JBOD D2700 JBOD 3 3

16. The SQL Server Database Consolidation Solution: Software Architecture The SQL Server DBC Solution Software Architecture

17. The SQL Server Database Consolidation Solution: Software Architecture SQL Server DBC Monitoring and Management Infrastructure • Solution Accelerators • Monitoring and Management Dynamic Infrastructure Toolkit Service Level Dashboard for SC Security Compliance Manager Management Studio • Portal • … • Compliance • Management • Service Level • Management • Self • Service • Infra • Admin Provisioning Engine • Data Tier Application Components SSIS Integration Packages Data Base Application Components Data Base Application Components Data Base Application Components Data Base Application Components Database Reporting Components Database Reporting Components Database Reporting Components Database Reporting Components SQL server Integration Services SQL server Reporting Services SQL server Reporting Services SQL server Reporting Services SQL server Reporting Services SQL server SQL server SQL server SQL server Operating System Operating System Operating System Operating System Operating System Operating System Operating System Operating System Operating System • Infrastructure • MOM • Packs • Power • Shell • Windows Server Update Services • Patch Mgmt. Microsoft SharePoint Server • MOM • Packs • MOM • Packs • MOM • Packs • Active Directory • MOM • Packs • Compute • Storage • Network • Load Balancing

18. The SQL Server Database Consolidation Solution: Software Architecture 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 • Custom MOM packs with DBC awareness

19. The SQL Server Database Consolidation Solution: Software Architecture Centralized utility Monitoring • System Center Operations Manager(continued) • Customizable monitoring views and reporting • Web based console for remote access

20. The SQL Server Database Consolidation Solution: Software Architecture 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

21. The SQL Server Database Consolidation Solution: Software Architecture Self Service Provisioning System Center VMM Self Service Portal Datacenter IT Admin • Provisioning , Administration & Self Service Portals • Lightweight infrastructure integration and provisioning engine • Extensibility authoring workflow & customized user interface Business Unit IT Customer 1 Business Unit IT Customer 1 • Admin • Self Service Portals Provisioning engine On Boarding Registration 1. Org. details 2.Justification Infrastructure Request 1. Compute 2. Network 3. Storage 1. Validate resource availability 2. Configure resources 3. Allocate resources 1. Create/start Virtual Machines 2. Stop/delete Virtual Machines 3. Access reports Management • System • Center • Compute Provider • Network Provider • SAN Provider Infrastructure Resources Compute Network Storage

22. The SQL Server Database Consolidation Solution: Software Architecture Solution Accelerators • 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 SCAP Extensions • Provides compliance support for federal Security Content Automation Protocol • Maps System Center DCM compliance reports to SCAP format

23. The SQL Server Database Consolidation Solution: Software Architecture Service Level Dashboards for System Center Operations manager • 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

24. The SQL Server Database Consolidation Solution: Software Architecture Transparent, Automated, load balancing • Hyper-V Live Migration provides the mechanisms • All connections are continuously maintained • All SQL Server transactions proceed normally – no failures, no roll backs • Reserves can be specified around CPU, Memory, Disk IOPS, Disk Bandwidth and Network bandwidth • Best load balancing targets are continuously and transparently maintained

25. The SQL Server Database Consolidation Solution: Consolidation & Virtualization SQL Server Consolidation • Historically a variety of SQL Server consolidation strategies have been utilized. • Typically, as isolation goes up, density goes down. • Isolation concerns include: Instance mobility, resources contention, name spaces, security domains, security effectiveness

26. The SQL Server Database Consolidation Solution: Consolidation & Virtualization What SQL Server Recommends for Consolidation Today Virtual Machine Based Consolidation • Strong isolation between applications • Near perfect application compatibility • Zero down time migration of execution loads • Online Physical to Virtual conversions • Simplified and accurate billing and chargeback mechanisms • Minimal and rapidly diminishing overhead cost

27. The SQL Server Database Consolidation Solution: Consolidation & Virtualization Virtual Machine Efficiency with SQL Server • SQL Server & Hyper-V Scales With Minimal Overhead • Requires Modern CPUs - SLAT (second level address translation) • 1% - 2% performance overhead per Virtual Machine • Measurement • Configuration • CPU Types • AMD Shanghai • SLAT support • No IOMMU • System Information • HP DL585 16 Cores • HP EVA8000 SAN • Windows 2008 R2 RC • VM Configurations • 4 Virtual Processors • 7GB RAM • Fixed VHDs • System Loading • 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

28. The SQL Server Database Consolidation Solution: Consolidation & Virtualization The Database Application Execution environment

29. The SQL Server Database Consolidation Solution: Consolidation & Virtualization The Database Application Execution Environment • Compelling advantages to provisioning one SQL Instance per virtual machine • Settings & configuration isolation • Resource isolation • Billing & charge back effectiveness • Resource utilization overhead is rapidly diminishing • Consistent execution environment across SQL Server products • SQL Server Relational Engine • SQL Server Integration Services • SQL Server Analysis Services • SQL Server Reporting Services

30. The SQL Server Database Consolidation Solution: Consolidation & Virtualization Microsoft Assessment and Planning Toolkit (MAP) • Sophisticated, Automated discovery of SQL Server Instances • Automated toolkit with readiness assessment for planningserver migrations • Agent-less inventory of clients, servers, applications, devices andhardware and device Compatibility • Runs over defined periods and categorizes • SQL instances on the basis of CPU, • I/O and memory usage

31. The SQL Server Database Consolidation Solution: Application Deployment Application Deployment Options • Existing Application Migration • Physical to virtual migration • The complete existing physical execution environment of a system is imported into a virtual machine. • Data-tier application component extraction and deployment • SQL Server Management Studio provides DAC wizards • Existing VMWare based installations can be directly imported • Self Service Deployment • System Center VMM Self Service Portal • Supplies additional roles and workflows around virtual machine and infrastructure provisioning

32. The SQL Server Database Consolidation Solution: Application Deployment Workload Characterization Design Point Assumptions Initial Research Results Evolving Consensus Results

33. The SQL Server Database Consolidation Solution The SQL ServerDatabase Consolidation Solution

34. Enrol in Microsoft Virtual Academy Today Why Enroll, other than it being free? The MVA helps improve your IT skill set and advance your career with a free, easy to access training portal that allows you to learn at your own pace, focusing on Microsoft technologies. • What Do I get for enrolment? • Free training to make you become the Cloud-Hero in my Organization • Help mastering your Training Path and get the recognition • Connect with other IT Pros and discuss The Cloud Where do I Enrol? www.microsoftvirtualacademy.com Then tell us what you think. TellTheDean@microsoft.com

35. Resources • www.msteched.com/Australia • Sessions On-Demand & Community • www.microsoft.com/australia/learning • Microsoft Certification & Training Resources • http:// technet.microsoft.com/en-au • Resources for IT Professionals • http://msdn.microsoft.com/en-au • Resources for Developers (c) 2011 Microsoft. All rights reserved.

36. © 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. (c) 2011 Microsoft. All rights reserved.