1 / 59

Infrastructure Solutions for Microsoft SQL Server

Infrastructure Solutions for Microsoft SQL Server. Information Infrastructure Solutions. EMC Proven Solutions. What to expect. Full-stack testing Not just interop Shared deployment risk with EMC Decreased deployment/testing cycles Assured performance

aoife
Download Presentation

Infrastructure Solutions for Microsoft 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. Infrastructure Solutions for Microsoft SQL Server Information Infrastructure Solutions

  2. EMC Proven Solutions What to expect • Full-stack testing • Not just interop • Shared deployment risk with EMC • Decreased deployment/testing cycles • Assured performance • Verified building blocks for scalability

  3. Cork, Ireland Shanghai, China Singapore Hopkinton, MA Santa Clara, CA Vienna, Austria Proven Solutions approach 1 2 3 4 Requirements

  4. Tiered/ Unified Storage Replication, Backup and Recovery Business Continuity Security Solutions Overview for Microsoft Applications Virtualization and Private Cloud • Symmetrix VMAX • VNX • VNXe • IOmega • VMware vSphere • Microsoft Hyper-V • VBlock • VPLEX • Replication Manager • NetWorker • Avamar • Data Domain • EMC Disk Library (EDL) • Cluster Enabler • RecoverPoint • vCenter SRM • SRDF • MirrorView • Celerra Replicator • RSA Data Loss Prevention Suite • RSA SecureView • RSA enVision • RSA SecurID • RSA Adaptive Authentication Proven Solutions, White Papers and Best Practices

  5. SQL Server Always On - I/O Reliability Program A Microsoft validated program for storage solution that complies with a set of core technical criteria to ensure the highest level of availability for mission critical SQL applications • All EMC storage arrays adhere to and can enforce write ordering consistency • Adherence to SQL Server Write Ahead Logging (WAL) Protocols with EMC’s Consistency Technology (transactional integrity) • Onboard protected caching to optimize I/O operations • EMC storage platforms will not transition asynchronous I/O operations from a host into synchronous ones • Microsoft SQL Server Database Engine Input/OutputRequirements • http://www.microsoft.com/sqlserver/2008/en/us/high-availability.aspx • http://www.emc.com/solutions/samples/microsoft/ms-sql-server-always-on-technologies-program.htm

  6. Enterprise Flash Drives and SQL Server What to expect • Decreased response time • More throughput • Smaller footprint, less power • Enable the use of nl-SAS with FAST

  7. Enterprise Flash Drives with SQL Server Decrease response time and improve scaling with assurance • Response time can be as low as 1ms (x10 faster than 15k FC disks) • Single Flash drive can deliver up to x30 IOPS than FC disk • Smaller footprint and reduced energy requirements by ~38% • Read intensive workloads with low cache read-hit rate • Random I/O patterns • Small I/O requests (up to 16KB) • Extremely low latency, high transaction throughput

  8. Enterprise Flash Drives with SQL Server Decrease response time with assurance • Selected Tables • Implementing table partitioning for read intensive tables • Significant performance improvement • TempDB • Typically generates large sequential I/O , but in some instances I/O can be very random • Moderate performance improvement • Index • Moderate performance improvement • Transaction Logs • Testing has shown no performance benefit over FC+Array write cache

  9. Reference Architecture: Tiered Storage Tiered storage design with: • CLARiiON CX4-960 • Two-node active/passive failover cluster • Storage connectivity - 8 Gb/s FC • Network connectivity - 1 GbE Workload • OLTP with TPC-E like standard • Number of customers: 75,000 • User data: 789 GB • Expected throughput: 10,000 IOPs

  10. Reference ArchitectureLayout – FAST (Flash+FC) Reference ArchitectureLayout – (FC ONLY)

  11. Layout with Flash & FC Drives Maintain performance with less acquisition cost

  12. Layout with Flash & FC Drives Maintain performance with smaller footprint

  13. Layout with Flash & FC Drives The table below highlights the results identified between our baseline configuration of 90 FC drives compared to 30 FC drives with 4 Flash

  14. FAST VP for Virtualized SQL ServersReference Architecture Storage • Two Engine VMAX • 1 GigE Ethernet network • VMFS Datastore VMware • Four SQL VMs (Hot/Warm) • 8 vCPU, 16GB RAM

  15. Improving Performanceand Efficiency Performance and Capacity -60% -32% -37% CAPEX and OPEX • Note: The tested 2 tiers approach is a viable option and not necessarily a best practice

  16. Performance Use FAST Cache to substantially improve OLTP throughput  • Improve performance without complex data migration • No downtime • No application impact • No schema changes • Improvement is dependent on several factors • Locality of Data • Ratio of Data to Cache Over 3x Transaction Throughput

  17. Efficiency Use FAST Cache to increase utilization while maintaining performance Equivalent Performance Improve Capacity Utilization by 4x

  18. Ease of Use FAST can eliminate complex data architecture projects Traditionally Databases have hot and cold areas • Common Approach • Manually partition database for TODAY’s workload • Problem: Complicated, downtime required, costly and only solves it for the present. • FAST Approach • No Manual steps required • Adapts dynamically to changing access patterns • Grow storage tiers as needed without any application level impact

  19. FAST Cache and SQL Server • Improve storage efficiency • Eliminate short-stroking • Reduces power and cooling requirements

  20. Improvement might not be immediate FAST Cache requires multiple accesses to promote data Lab testing shows few hours between Cache available, and fully used. • FAST requires time to monitor the system and move data around. • Typically happens on a daily schedule • Does not adapt to mid-day changes in workload TPS Improvement within 5 hours

  21. vfCache What to expect • Massively decreased read response time • Massively increased scale of performance • Reduced workload on existing SAN fabric and arrays • Licensing cost savings through increased consolidation

  22. EMC VFCache Improves Server Performance • Server Flash caching solution that uses intelligent caching software and hardware technology to reduce latency and increase throughput • “Hottest” data accessedthrough VFCache in the server providing increased performance • VFCache benefits SQL Server 2012: Lower database I/O latency by 60% 50% more I/O serviced within 1 ms 4X more transactions per SQL Server database Perfect fit for OLTP workloads Read Latency (Sec) Transactions Per Minute (TPM)

  23. Total Protection Write-through caching to the array • Data persists down to EMC Symmetrix VMAX and EMC VNX networked storage to ensure high availability, end-to-end data integrity, data reliability, and disaster recovery • Sharable and scalable • No stranded storage

  24. Read Hit Example • Read request from application to an accelerated array LUN • VFCache Driver determines a hit occurred and accesses data from Flash device • Data returned from the Flash device is forwarded to the application Application 1 3 VFCache Driver 2 SANHBA PCIeFlash SAN storage

  25. Read Miss Example • Read request from application to an accelerated array LUN • VFCache Driver determines a miss occurred and accesses data from array LUN • Data is read from the array and returned to the application • Read miss data is written to Flash device asynchronously Application 1 VFCache Driver 2 4 SANHBA PCIeFlash 3 SAN storage

  26. Write Example • Write request from application to an accelerated array LUN • VFCache Driver writes data to array LUN • Application write acknowledged upon array completion • Write data is asynchronously written to Flash device Application 1 VFCache Driver 2 4 SANHBA PCIeFlash 3 SAN storage

  27. The VFCache Effect More transactions, less waiting The VFCache Effect • Response time 50% • Throughput 210% Measured workload example: TPCC-like workloads on Oracle and DB2 (1.2 TB database)

  28. VFCache for SQL Server - Architecture

  29. VFCache for SQL Server - Impact

  30. Tiered/ Unified Storage Replication, Backup and Recovery Virtualization and Private Cloud Business Continuity Security Solutions Overview for Microsoft Applications • Symmetrix V-Max • VNX • VNXe • IOmega • VMware vSphere • Microsoft Hyper-V • VBlock • VPLEX • Replication Manager • NetWorker • Avamar • Data Domain • EMC Disk Library (EDL) • Cluster Enabler • RecoverPoint • vCenter SRM • SRDF • MirrorView • Celerra Replicator • RSA Data Loss Prevention Suite • RSA SecureView • RSA enVision • RSA SecurID • RSA Adaptive Authentication Proven Solutions, White Papers and Best Practices

  31. Virtualizing SQL What to expect • Reduced Licensing Cost • Smaller footprint • More power efficiency • Assured performance • Simplified scaling • Increased operational flexibility • Simplified disaster recovery • Simplified test/dev provisioning

  32. ~5-8x reduction in SQL licensing costs 16 Virtualized SQL Servers • Enterprise Edition • Two quad-core procs • 2 vCPU per core* • Cost: • $54,992 - limited mobility (no SA) • $96,236 - unlimited mobility (with SA) • * MS recommends up to 8 vCPUs/core with Hyper-V 16 Physical SQL Servers • Enterprise Edition • 64 core licenses* • Cost: • $439,936 (minimum) • * MS licenses a minimum of 4 cores per server. Frequently it’s more.

  33. Key Benefits – Server Virtualization • Consolidation - Achieve 2-10x consolidation ratio, especially for larger deployments • Lower TCO - Significant power, coolingand data center space • Availability- Using a VM based protection for SharePoint provides homogeneous high availability (VMware HA, WFC) • Business Continuity - Simplified disaster recovery management (vCenter Site Recovery Manager, Cluster Enabler) • Maintenance - Live migration of virtual machines (VMware vMotion, Hyper-V Live Migration) • Load Balancing - Maximized overall performance with balanced HW utilization across the farm (VMware DRS, SCVMM PRO) • Rapid Provisioning and Scaling – Using VM templates for fast provisioning for easier scale-out

  34. Approaches to SQL Server Consolidation Single instance - Databases consolidation • Requires common configuration • Preferably similar workloads • Resource contention (Memory, TempDB..) • Downtime/Maintenance impact • Limited performance management Multiple instances - Instance consolidation • Per-Instance resource allocation • Workload isolation Multiple VMs - Hypervisor consolidation • Better isolation • Dynamic resource management • Faster deployment • VI benefits (CPU/Memory over-commitment)

  35. SQL_2 SQL_4 SQL_6 SQL_8 SQL_1 SQL_3 SQL_5 SQL_7 OS OS SQL Server Scaling In Virtual Deployments Scale-Up Approach • Multiple databases or SQL instances per VM • Fewer ESX Servers • Single point of failure • Larger VM SMP overheads • OS bottleneck, especially for 32-bit environments Scale-Out Approach • Single instance/database per VM • Better SQL Instance and workload isolation DSS vs. OLTP separation • More granular change management • DRS/VMotion more effective with smaller VMs VM1 VM2 Virtual Machines Virtual Machines SQL_3 SQL_1 SQL_2 SQL_4 SQL_7 SQL_5 SQL_6 SQL_8 OS OS OS OS OS OS OS OS ESX Server ESX Server ESX Server

  36. SQL Server Scale up performancePhysical vs. Virtual • At 1-2 vCPUs, ESX achieves 92% of native throughput • 4 vCPUs can reach 88% while 8 vCPUs 86% of native throughput • At 1, 2 and 4 vCPUs on the 8pCPU server, ESX is able to effectively offload certain tasks to idle cores.

  37. Virtualized SQL Server - Connectivity Options • SQL 2008 on Windows 2008 performed similarly under virtual and physical machines • The physical machine and virtual machine (MSI) saturate at 9,000 users • VMFS, and RDM saturate at 8,000 users. • VMFS performance drops rapidly once user saturation is reached VMware ESX - Performance/Connectivity options (iSCSI Connectivity, Avg., User response time <2.0 sec)

  38. Tiered/ Unified Storage Replication, Backup and Recovery Virtualization and Private Cloud Business Continuity Security Solutions Overview for Microsoft Applications • Symmetrix V-Max • Symmetrix DMX • CLARiiON CX4 • CLARiiON AX4 • Celerra Unified Storage • IOmega • VMware vSphere • Microsoft Hyper-V • VBlock • VPLEX • Replication Manager • Avamar • NetWorker • Data Domain • EMC Disk Library (EDL) • RecoverPoint • SRDF • MirrorView • Cluster Enabler • vCenter SRM • RSA Data Loss Prevention Suite • RSA SecureView • RSA enVision • RSA SecurID • RSA Adaptive Authentication Proven Solutions, White Papers and Best Practices

  39. Local Replication with EMC What to expect • Rapid data restores and backups regardless of data size • Offloaded backups to increase potential operating/maintenance time • Single management point for all your apps and platforms • Automated repurposing for test/dev

  40. Daily Backup: Recovery point every 24 hours Recovery Gap Snapshots/Clones: Recovery point every 2-6 hours Recovery Gap Time-based CDP: Time indexed, but no SQL aware recovery points Time Based Recovery Points (T) Time CDP and/or CRR with SQL VDI Bookmarks:Application optimized recovery points Unlimited Recovery Points with SQL Server Aware VDI Bookmarks Checkpoint Patch Post-Patch Cache Flush Hot Backup Eng. Version Release Checkpoint VDI Snapshot (T) Time SQL Operational Recovery - Know Your RPO&RTO Recovery Gap Recovery Gap Recovery Gap Recovery Gap Recovery Gap Recovery Gap Recovery Gap

  41. Common Interface for Multiple Recovery Scenarios • File group Restore • Allows you to restore a subset of the database at file group granularity • Full Restore • Restores the entire user database/s. This includes the data, log files, and, for SQLServer 2005/8, all full-text catalogs • Replace Restore • Rapid VDI based restore that • skips all the checks • (log backup, duplicate DB, • duplicate filename) • Advanced Recovery • Recovery (PiT) • No Recovery (For T-log replay) • Standby (Read-Only) • File System (Manually attach) • On-demand mount allows single interface for all types of replicas • RecoverPoint CDP bookmark, CRR bookmark, Crash Consistent Point-in-Time Copy • Time slider for crash consistent point-in-time mount • User-friendly name for ‘Any Point-in-Time’ event

  42. Array and SAN replication for SQL What to expect • Database and application layer replication integration • Decreased bandwidth consumption with RecoverPoint • Simplified Failover • Push-button failover with VMware SRM • Automated failover with Cluster Enabler • Transparent failover with VPLEX • Possibility of zero data loss RPO with synchronous replication • Extremely rapid RTO with CE/VPLEX

  43. SQL Server Availability Zero-Seconds Minutes Hours Failover Cluster (Local) DB Mirroring (Sync) Cluster Enabler (Sync) Cluster Enabler (Async) Transactional Replication Hours Minutes Seconds DB Mirroring (Async) Log Shipping VDI/VSS Backup/Restore Recovery Time (RTO) SAN Replication (Sync) SAN Replication (Async) Native Backup/Restore Recovery Point (RPO)

  44. SQL Server Replication Stretched Failover Clustering • Automated restart solution, based on Windows server Failover Clustering • Provides high-availability on the instance level • Active/Passive or multiple active instances/nodes • Modules available for SRDF, MirrorView and RecoverPoint (Sync and Async) • Can be fully automated • Supports all cluster modes (MNS, FSW etc) • Multiple subnets support coming in “Denali” Windows Failover Cluster Site A Site B SRDF MirrorView RecoverPoint

  45. VMotion over Distance Microsoft-Oracle-SAPEnabled By VPLEX Metro • Site A • VPLEX Metro • Symmetrix VMAX • CLARiiON CX4-480 • Site B • VPLEX Metro • Vblock 1 (CX4) • 100Km Distance

  46. BOOKMARK BOOKMARK Virtual Machine Virtual Machine SAN SAN WAN F: D: G: E: RDM RDM VMFS VMFS ESX1 Automated SQL Consistent Replicas at Both Sites With Physical or Virtual Hosts Replication Manager Server VMware ESX Cluster VMware ESX Server Farm SAN RecoverPoint RecoverPoint LUN LUN Local CDP Journal Remote CRR Journal Production LUN Remote CRR Copy Local CDP Copy 1. Replication Manager freezes SQL databases (VDI) 2. Replication Manager server requests bookmark to be created 3. Replication Manager thaws SQL databases 4. Images are now mountable/recoverable

  47. Solution Architecture 4* SQL Server Virtual Machines • 2* Online Transaction Processing Databases (75,000/25,000 users) • 2* Data Warehouse / Analytics Databases (2TB/1TB) Storage Configuration • OS: RAID5 FC Pool • OLTP DB: RAID 1/0 FC Pool • DW DB: RAID 6 SATA Pool • DB Logs: RAID 1/0 FC RG • RP Journals: RAID 1/0 FC RG J

  48. Optimizing WAN with RecoverPoint 43mbits 3.6mbits 23.3mbits 151mbits 177.9mbits Operating System + PageFile WAN traffic site write rate SQL System DBs + SQL TempDB Average compression ratio achieved 4:1 User Databases J

  49. Tiered/ Unified Storage Replication, Backup and Recovery Business Continuity Security Solutions Overview for Microsoft Applications Virtualization and Private Cloud • Symmetrix VMAX • VNX • VNXe • IOmega • VMware vSphere • Microsoft Hyper-V • VBlock • VPLEX • Replication Manager • NetWorker • Avamar • Data Domain • EMC Disk Library (EDL) • Cluster Enabler • RecoverPoint • vCenter SRM • SRDF • MirrorView • Celerra Replicator • RSA Data Loss Prevention Suite • RSA SecureView • RSA enVision • RSA SecurID • RSA Adaptive Authentication Proven Solutions, White Papers and Best Practices

More Related