Designing a Private Cloud Infrastructure for Microsoft SQL Server: Financial Services Case Study

1. DBI318 Designing a Private Cloud Infrastructure for Microsoft SQL Server: Financial Services Case Study • Manjnath Ajjampur • Lead Datacenter Strategist, Northern California • Microsoft Corporation Ross Mistry Principal Enterprise Architect Microsoft Corporation: MTC – Silicon Valley

2. http://RossMistry.com @RossMistry Ross Mistry Principal Enterprise Architect, Author and former SQL Server MVP Microsoft Technology Centers – Silicon Valley

3. @InADatacenter Manjnath Ajjampur Lead Datacenter Strategist, Northern California Microsoft Corporation

4. Agenda • Introduction • Private Cloud Overview • Planning • Design • Optimization

5. Part 1 Private Cloud Overview

6. Microsoft Private Cloud Defined “A private cloud is a new model for IT delivery. It turns a datacenter’s infrastructure resources into a single compute “cloud” and enables the key benefits of cloud computing: Pooled Resources Self-Service Usage-based Elastic

7. Cloud Drivers Innovation Agility Economics

8. Part 2 Private Cloud Customer Planning

9. Existing Environment • Hundreds of Instances of SQL Server running on physical servers • OS memory range: 4GB-48GB • Approximately 2000 Databases • Majority of the servers were running Windows Server 2008 or Windows Server 2008 R2 • Leveraging some System Center within other groups • 2U rack mounted servers – underutilized • SAN based storage • Separation of duties - DBA and Server Platform Engineering

10. Customer Goals and Requirements • Business driven • Virtualize Tier 2 and 3 data platform environments. • Increase manageability by standardizing on single data platform and OS. • Reduce TCO and increase hardware utilization. • Implement self service capabilities . • Dynamically support projected growth of 30% per year. • Allow DBA and Server teams to be proactive and focus on strategy. • Adhere to green initiatives. • Technology driven • Pool key resources (compute, storage and networking) into logical units. • Dynamically provision and scale database applications. • Provide high availability and disaster recovery for mission critical databases.

11. Planning – Resource Pooling • Used the MAP Toolkit to identify SQL Server sprawl. • Leveraged the data from MAP for Private Cloud capacity planning • Used the Upgrade Advisor to analyze SQL Server instances and identify upgrade blockers • Captured the following performance metrics • Processor • Memory • Disk Space and I/O • TempDB usage MAP Toolkit Upgrade advisor

12. Key Findings from MAP & Metrics Collected • Systems were running with <10% CPU utilization on 4 to 8 core machines • Memory Usage per instance was approximately 2 to 64 GB • Total storage required was approximately 5 TB • IOPs ranged from 30 -10,000

13. Reviewing a Sample MAP Report

14. Part 3 Private Cloud Customer Design Decisions

15. Design Decisions - Overall • Virtualize Tier 2 and 3 databases - 100 Instances on Hyper-V • Standardize on SQL Server 2008 R2 on Windows Server 2008 R2 SP1 • Private Could based on Windows Server 2008 R2 (Server Core) and Systems Center 2012 • Database Migration for existing workloads • 6 Nodes in Hyper-V Cluster • HA at a VM level, not instance level • 1 SQL Server instance per VM • Management infrastructure not part of this cluster • Segregation between Hyper-V platform owners and DBAs

16. Design Decisions - Processor • Total cores required –300 – this will be based on how many instances we go after • Current utilization – <10% of the CPU on an 8 core machine. • Rationalization • 2 socket servers with 8 cores per socket • Conservative 8:1 overcommit ratio of vCPU:CPU provided by Hyper-V • 100 instances with 1 core dedicated to each will require 100 cores • Best Practices • Selecting the maximum number of cores per processor available and choosing the fastest clock speed available.

17. Design Decisions - Memory • Assuming a 4GB per VM average, and a no oversubscription, that is ~130GB per node. Each node has 384 GB RAM • Dynamic Memory Utilized for all workloads • Best Practices • recommended to purchase the maximum amount of RAM • Respect NUMA architecture

18. Design Decisions - Storage • Use Pass-through disks for maximum performance • Use Storage Classification to adhere to business SLA • OS will boot from SAN • 8GB Fibre Channel • Best Practices • Each storage OEM has their own design recommendations for Hyper-V optimization • Respect SQL server LUN best practice

19. Design Decisions - Network • Follow Hyper-V best practice • 1 network for Management traffic • 1 network for Live Migration • 1 network for Cluster interconnect • 1-5 networks for SQL Server VM traffic • SQL Server VM traffic is over highly available and redundant NICs (802.3 ad) • Hardware based QOS

20. Part 4 Optimizing SQL Server in a Private Cloud

21. High Availability and Disaster Recovery • 1 SQL Server instance per VM • HA at the VM level using Live Migration • DR via SAN replication

22. Alternatives SQL Server High Availability

23. SQL Server HA Alternatives in Private CloudLive Migration • No Loss of Service • Availability with Lower Complexity • Easier Management with VMM • Manage Loads on VMs Across Physical Machines VM Live Migration 2 1 Host cluster Shared Storage iSCSI, SAS, Fiber

24. SQL Server HA Alternatives in Private CloudGuest Clustering • Create failover cluster in Hyper-V environment • Cluster service runs inside Hyper-V guest • Application Mobility: Enable patching of guest OS without downtime • Support mixed clustering (host and guest) Guest Cluster Guest Cluster 2 1 Shared Storage iSCSI Redundant Paths to storage

25. SQL Server HA Alternatives in Private CloudAvailability Groups • Guest Clustering not Required • Achieve HA and DR • No 3rd Party Solutions Required VMs 2 2 1

26. Self-Service • 3 x SQL Server Service Templates for Provisioning • Small – 1 vCPU, 4GB RAM, 100 GB, >1000 IOPS • Medium – 2 VPs, 8 GB RAM, 200 GB, >1000 IOPS • Large – 4 VPs, 16 GB RAM, 400 GB, >5000 IOPS

27. SQL Server Service Templates

28. Track Resources Hands-On Labs @sqlserver @ms_teched SQL Server 2012 Eval Copy Get Certified! mva Microsoft Virtual Academy

29. Resources Learning TechNet • Connect. Share. Discuss. • Microsoft Certification & Training Resources http://northamerica.msteched.com www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet http://microsoft.com/msdn

30. Required Slide Complete an evaluation on CommNet and enter to win!

31. MS Tag Scan the Tag to evaluate this session now on myTechEd Mobile

32. © 2012 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.