1 / 56

Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies

Required Slide. SESSION CODE: DAT308. Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies. Prem Mehra Program Manager Microsoft Corporation. Sung Hsueh Program Manager Microsoft Corporation. Session Objectives and Takeaways. Session Objective(s):

kesia
Télécharger la présentation

Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies

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. Required Slide SESSION CODE: DAT308 Microsoft SQL Server Consolidation: Options, Considerations, and Case Studies PremMehra Program Manager Microsoft Corporation Sung Hsueh Program Manager Microsoft Corporation

  2. Session Objectives and Takeaways • Session Objective(s): • Provide an overview of consolidation options • Compare each consolidation option • Highlight consolidation case studies • Know when to apply which consolidation option • Be prepared with best practices and recommendations

  3. Outline • Consolidation Drivers and Options • Virtualization Technique • Instance & Database Technique • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement • Case Studies • Summary

  4. Consolidation Drivers Reduce Cap-Ex 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 Infrastructure Agility Load Balancing & Dynamic Provisioning Standardization of services Underutilized hardware Hardware computing capacity Number of database apps Overburdened Administrators Number of DBA’s 1990 2000 2010

  5. Consolidation Options • Currently a variety of consolidation approaches exist and are utilized. • Typically, as isolation goes up, density goes down and operation cost goes up. Higher Density, Lower Costs Higher Isolation, Higher Costs Virtual Machines IT Managed Environment Databases Instances Schemas Sales_1 Consolidate_1 Marketing_1 Online_Sales DB_1 ERP_10 DB_2 ERP_10 DB_3

  6. Comparing Consolidation Approaches Source : http://msdn.microsoft.com/en-us/library/ee819082.aspx

  7. SQL Server Consolidation feature set Hyper-V Live Migration Windows Server Resource Manager (WSRM) failover cluster in guest VMs New in SQL 2008 R2 ® System Center VM Manager Microsoft Assessment and Planning (MAP) Resource Governor >64 logical processors Sysprep SQL setup Data Tier App Utility Control Point and Managed Instances Higher Isolation Lower Density Higher Density Lower Isolation Virtual Machines Instances Databases

  8. Outline • Consolidation Drivers and Options • Virtualization Technique • Instance & Database Technique • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement • Case Studies • Summary

  9. Virtualization: Performance Perspective • Topics of interest • Scalability of virtual instance • Comparison of virtual vs. native instance • Virtualization overhead: IO and CPU • Throughput Metric • Relative Throughput = Batches/sec/%Processor • Test workload • OLTP workload – Stock trading • Focus on typical consolidation workload • Low resource utilization

  10. Dynamically Expanding Virtual Hard Disk Performance Test Configuration On disk storage space needed • Host • Dell PowerEdgeR900 Intel Xeon 2.4GHz • (16cores, 64GB RAM) • HP DL785 (32cores, 128GB and 48cores, 256GB) • Storage • Hitachi Data Systems AMS1000 • Hitachi Data Systems AMS2500 • Virtual Machines • 4 Virtual Processors, 14GB RAM, 12 GB for SQL Passthroughand Fixed-Size VHDs (2 Data, 1 Log) • Dynamic VHD not used, better performance in Win2008R2 • http://download.microsoft.com/download/0/7/7/0778C0BB-5281-4390-92CD-EC138A18F2F9/WS08_R2_VHD_Performance_WhitePaper.docx VHD Size Fixed Sized Virtual Hard Disk On disk storage space needed VHD Size

  11. 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)

  12. Transaction Response Time (with SLAT) • Transaction response time comparable • 32 Cores 128 GB Server. Guest: 4 Core 7 GB per VM, fixed size VHD • CPU over-commit • Sub seconds application response time • Test your own workload, may scale differently SLAT: Second Level Address Translation (Hardware Assist)

  13. Best Practices and Recommendations 1 • 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

  14. Best Practices and Recommendations 2 • Use synthetic devices for best performance • Synthetic devices utilize a VSC (virtual service client) and VSP (virtual service provider) to pass requests over a VMBus to the root partition • Everything occurs in kernel mode once initial request is passed from VM • Synthetic devices significantly perform better and use less CPU overhead than emulated devices • Emulated devices should be avoided when possible • May be needed during initial configuration of guest VM • Synthetic devices are installed with integration components • 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

  15. 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 Guest Cluster Guest Cluster 2 1 Live Migration 1 2 1 2 Shared Storage iSCSI Host cluster Redundant Paths to storage Shared Storage iSCSI, SAS, Fibre Guest Clustering Live Migration & Host Clustering

  16. SQL Live migration with Data Replication (SCVMM) (Domain Controller) (Command View) SQL Server DB SQL Client App Web server SAN HP Cluster Extension Hyper-V Cluster Virtual Machines Virtual Machines Live Migration DR Group 003 IP network EVA 4400 DR Group 002 DR Group 001 Replicate VHDs of all VMs LAN SAN SCVMM EVA 4400 Domain Controller CommandView SQL Client App OS Disk 30GB SQL Server DB G:\ OS Disk 30 GB K:\ DatabaseDisk 100 GB Web server OS Disk 30GB

  17. Creating and managing Virtual Machines Optimize Resources • Centralized virtual machine deployment and management for Hyper-V, Virtual Server, and VMware ESX servers • Fast and reliable P2V and V2V conversion • Intelligent placement of Virtual Machines • Comprehensive application and service-level monitoring with Operations Manager and Integrated Performance and Resource Optmization (PRO) of VMs Leverage Skills • Familiar interface, common foundation • Monitor physical and virtual machines from one console • Fully scriptable using Windows PowerShell 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 Reduce Cap-Ex Reduce Op-Ex Agile IT

  18. P2V Demo DEMO

  19. Outline • Consolidation Drivers and Options • Virtualization Technique • Instance & Database Technique • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement • Case Studies • Summary

  20. Managing Resources Relational Server - CPU • Windows System Resource Manager (process level) • CPU management across multiple instances or other processes on same OS • Affinity mask (sp_configure option) • Process partitioning across CPU’s exposed to OS • Resource Governor (SQL Server 2008) • CPU/Memory management within an instance • Software Non-Uniform Memory Access (Soft NUMA) • Application partitioning across CPU’s utilized by a SQL instance

  21. Managing Resources Relational Server - Memory • Grant the SQL Server service account “Lock pages in memory” privilege • Determine total RDBMS memory size for each instance (Msql) • Ensure Msql < Total Server Memory – ~1GB (per 8-16GB) for operating system – Max memory required by other apps or instances • Reserve enough room outside of buffer pool for • SQL Server Threads • Threads will consume 4 MB each on Itanium, 2 MB on X64, 1 MB on 32-Bit • (= 1GB for 256 Worker Threads on Itanium) • XPs, In-Proc OleDB drivers, CLR GC heap, etc. • NOTE: On 64-bit memory outside of Buffer Pool cannot be limited • Place a memory ceiling on each relational server instance • Optionally, to ensure a certain amount of memory resources set ‘min server memory’

  22. Managing Resources Relational Server – I/O • Shared storage is a likely reality • Dedicated physical resources (spindles can provide more predictable performance) • Size properly, remember capacity is only one dimension • IOPs & Bandwidth • Monitor response times proactively (Averages over time) • Increasing response times with same I/O level likely indicates issues with shared storage • 1 - 5 ms for log (ideally 1 ms or better) • 5 - 20 ms for data (OLTP) (ideally 10 ms or better) • <=25-30 ms for data (DSS) (partly determined by total bandwidth) • Follow deployment best practices • http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx • Storage Top 10 Best Practices • www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

  23. Temp DB Usage • Consolidating SQL Server databases with different collations • Can encounter problems since Temp DB will retain the default collation of the model database • For more information on consolidated databases with different collations see SQL Server Books Online topic “Mixed Collation Environments” • Becomes much more significant in a consolidation environment • Be sure to tune Temp DB for proper sizing as well as performance • Temp DB utilized for by many features of SQL Server

  24. Temp DB: Isolation and Configuration • Temp DB placement (dedicated versus shared physical disks) • Unless you understand Temp DB I/O characteristics, it may be better to allocate Temp DB on spindles for data and indexes to utilize more cumulative disks • Best Practice: Temp DB one data file of equal size per CPU (core) on host server • Pre-size data/log files—do no rely on AUTOGROW • Monitor and understand your own Temp DB usage • SqlServer:Transactions/FreeSpace in Temp DB (KB) • Related dynamic management views (DMVs) • sys.dm_db_session_space_usage, sys.dm_db_task_space_usage, sys.dm_exec_requests

  25. Outline • Consolidation Drivers and Options • Virtualization Technique • Instance & Database Technique • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement • Case Studies • Summary

  26. Multi-Server Management DO NOT ERASE!

  27. The SQL Server Utility • Central point of reasoning for multi-server management • Simple to setup and use - a key advantage Managed Instances SQL02 SQL01 SQL03 SQL04 DBA SQL05 UCP Management Studio

  28. DAC: What is a data-tier application? • New unit of deployment for T-SQL apps • An “MSI” for T-SQL apps • Facilitates interaction between application developer and DBA • Core parts of the DAC • DAC metadata • Schema definition • Deployment requirements • Set of associated services DAC DAC Metadata (Name, Version, …) Definition (Schema) Deploy Indexes, Partitions FileGroups … Develop Tables, Views, Constraints, SProcs, UDFs, Users, Logins DAC Deployment Profile Deployment Requirements Post-Deployment Scripts DAC = Data-tier Application Component => the “MSI” like package Data-tier app => the deployed DAC running on a managed instance

  29. Data-tier Application MANAGE DEVELOP DEPLOY SQL Server Management Studio FinApp Dev DB 1 Deploy / Upgrade DAC Reverse Engineer DAC 2 SQL Server Management Studio DBA 9 4 8 Visual Studio 2010 Manage, Register, Uninstall, Extract, Upgrade DAC Control Point Define Target Pre-Reqs DBA 3 7 HR Deploy / Upgrade DAC Developer Hand-offto DBA Compile + Build FinApp Prod DB 6 5 SALES .dacpac Managed Server Group

  30. Health State Visualization Dashboard List View Detail View

  31. Measure Data Visualization CPU Utilization Storage Utilization

  32. Utility Dimensions and Measures Measures Dimensions Computer Volume CPU Utilization DAC Server Disk Space Utilization Database Filegroup DataFile LogFile

  33. Limitations and Support • Number of managed instances • Enterprise Edition UCP is limited to 25 managed instances • Tested 200 managed instances per UCP • Version of managed instances • SQL Server 2008 R2 • SQL Server 2008 PCU2

  34. Capacity Planning * recommended to support maximum scale limits.

  35. Application and Multi-Server Management Application and Multi-Server Management • TARGET AUDIENCE DATABASE ADMINISTRATOR IT PROFESSIONAL TARGET SCENARIO End-to-End Service Management Data Platform Resource Optimization • Datacenter and cross-platform • Health monitoring of distributed applications • SQL Server RDBMS focused • Central evaluation of utilization policies TARGET DOMAIN APPs SQL Server OS Hardware SQL Server Health, Breadth/Composable Planning + Trend Analysis, Depth/Domain-specific

  36. Outline • Consolidation Drivers and Options • Virtualization Technique • Instance & Database Technique • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement • Case Studies • Summary

  37. Case Study 1: 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

  38. Case Study 1: MSIT SQL Consolidation • 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 • Simplicity and ease of deployment

  39. Case Study 1: Microsoft IT Consolidation Operational Results

  40. Case Study 1: SQL Utility Configuration - Guests • Fiber Channel Only • No CPU over-commitment • Fixed VHDs only • 4 SCSI controllers • Controller 1 Channel 0 – D:\ Binaries (SQL Install) • Controller 1 Channel 1 – H:\ DATA • Controller 1 Channel 2 – I:\ DATA • Controller 1 Channel 3 – J:\ DATA • Controller 1 Channel 4 – K:\ DATA • Controller 2 Channel 0 – O:\ Log • Controller 3 Channel 0 – T:\ Tempdb • Controller 4 Channel 0 – E:\ DB Backups • Controller 4 Channel 1 - F:\ Tran Log Backups

  41. Case Study 1: SQL Utility Configuration - Overview Guest Host Network Storage

  42. Case Study 2: Overview • A large US Manufacturer • About 4000 instances, 1900 managed by DBA, 4100 databases • Goal: Consolidate Tier-2 and 3 databases. • Motivation: TCO, Chargeback to user department • 4 to 5 months into the process(3rd Quarter 09)

  43. Case Study 2: Strategy & Observations • Strategy: • Database consolidation into fewer instances • Expectation: Fewer than 1000 instances • A Dev/Test Farm & a Production Farm • 4 Socket Quad Core (16 core) Servers • HA/DR: Synchronous Mirroring with Witness • Dev/Test Farm – all in one datacenter • Production – in two data centers • Observation: A data point (3rd Quarter 09) • Consolidated 250 to 300 databases on 4 to 5 servers • On average 60 databases per instance, 88 in one

  44. Case Study 2: Lessons Learned • Lessons Learned: • Engaging and getting attention and time of the application teams is a challenge. High level management support is essential • Technical solution is comparatively easier to design and manage • Kept technical solution simple and standardized • Resource Governor used to assign higher priority to specific connections • Well defined processes for maintenance management • Roles: Divided administrative responsibilities: • Systems Administration – Responsible for instance related tasks and got administrative privileges. Responsible for the node& instance oriented tasks. E.G., Restart of a server after node failure, Monitoring of the instance, Resource Governor, etc. • Database Administrators got DBO privileges for their databases. Responsible for DB Schema, Backup, Recovery, DBCC, Data Integrity, etc. • To minimize costs, installed on 16GB of Memory. As of the last observation point: was adequate for the Tier 2 and 3 applications

  45. Case Study 3: Overview • A large US enterprise from Finance/Insurance industry • About 3000 instances • Goal: Consolidate Tier-2 Databases • Motivation – TCO, and achieve approximately 60% CPU utilization • Status (3rd Quarter 2009): • About 50% instances consolidated • About 6.7 instances per server

  46. Case Study 3: Strategy & Observations • Strategy: • More instances per server • Evaluate Database level consolidation later • Migrate to SQL Server 2005 and then consolidate • Started the process in first quarter of 2008 (prior to SQL Server 2008 availability) • Certifying SQL Server 2008 for production • Failover Clustering for HA • Observation: • About 45% instances already consolidated (3rd Quarter 2009) • Standardized on HP BL685 Blades • 4-way dual core 32 GB – 4 Production or 8 Non-production instances. • 4-way quad core 96 GB – 10 Production or 20 Non-Production Instances. • Expect finishing by middle of 2010

  47. Case Study 3: Lessons Learned • To gain experience & build confidence initially consolidating infrastructure oriented databases • Subsequently, Line of business applications (LOB) • Infrastructure DB consolidation helped in achieving LOB teams cooperation • TCO helped in getting management support • Reboots associated with adding more instances & maintenance negatively impact HA

  48. Outline • Consolidation Drivers and Options • Virtualization Technique • Instance & Database Technique • SQL Server 2008 R2 Multi-Instance & Application Management Enhancement • Case Studies • Summary

  49. Summary • Running SQL Server workloads within Hyper-V guest VM’s can be a good option for some production work load • Database level consolidation provides benefit of higher density • SQL Server 2008 R2 Utility Control Point (UCP) provides dashboard and drill down utilization views of instances and applications

  50. ReferencesNeed more info? High Performance SQL Server Workloads on Hyper-Vhttp://download.microsoft.com/download/D/F/8/DF89D22D-39C8-4728-A990-3BD4467891B7/HighPerformanceSQLServerWorkloadsOnHyper-V_Final.docxRunning SQL Server 2008 in Hyper-V Best Practices http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspxSQL Server Consolidation Guidancehttp://msdn.microsoft.com/en-us/library/ee819082.aspx TechNet Webcast: Deep Dive: Microsoft Virtualization Best Practiceshttps://www106.livemeeting.com/cc/mseventsbmo/view?id=1032442977&role=attend&pw=726F0653 SQL Server 2008 R2 Manageabilityhttp://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/SQL2008R2Manageability.docx Data-tier Applications in SQL Server 2008 R2http://msdn.microsoft.com/en-us/library/ff381683.aspx “How do I …” videos Search for “UCP” and “DAC” athttp://technet.microsoft.com/en-us/sqlserver/dd353197.aspx

More Related