Fast Track Data Warehouse SQL Server 2012 OLTP – Tune your application (DW less control) Business Case - Budget Time to value.
Download ReportTranscript Fast Track Data Warehouse SQL Server 2012 OLTP – Tune your application (DW less control) Business Case - Budget Time to value.
Fast Track Data Warehouse SQL Server 2012
OLTP – Tune your application (DW less control) Business Case - Budget Time to value
Step 1: Buy new shiny SAN array Step 2: Buy big server Step 3: Install SQL Server 2012 Step 4: Bulk load 30TB DW with SSIS Step 5: Grant access
Infrastructure Cost Large scans of huge fact tables Audience Survey – SAN throughput
DATA WAREHOUSING OFFERINGS WHERE FAST TRACK FITS IN
Self Build Business Data Warehouse Fast Track Reference Architectures Parallel Data Warehouse Half Rack Parallel Data Warehouse Full Rack
Effort to Build Capacity Concurrency Query Complexity Architecture Form factor Variable Variable Variable Variable SMP Software only Very low 5TB Light Medium SMP Appliance Moderate 14TB – 95TB Light – high Medium – high SMP Reference Architecture Very low 80TB Very high Very high MPP Appliance Very low 500TB + Very high Very high MPP Appliance
• • • • • • •
Hardware System Design:
• • • Tight specifications for servers, storage and networking Resource Balanced & Validated Latest generation Servers & Storage
Still Windows Server & SQL Server for admins & mgmt Can select from a range of HW vendors Engineered by expert from HW vendor and Microsoft Pre-tested for predictable results Faster implementation Great performance/price ratio Low SQL Server license count
Sequential Reads Parallel reads across multiple disk sets Balanced architecture Difficult to achieve in large server / share SAN environment Database layout matches hardware layout Matching throughput for each component LUNs mapped to Storage Processors, network paths, CPUs
SQL Server 2012 Windows Server 2008 R2
CPU CPU
Server HBA HBA HBA HBA Storage Processor 3GB/sec Storage Processor 3GB/sec RAID10 RAID10 RAID10 RAID10 Storage Enclosure Storage Processor 3GB/sec Storage Processor 3GB/sec RAID10 RAID10 RAID10 RAID10 Storage Enclosure Storage Processor 3GB/sec Storage Processor 3GB/sec RAID10 RAID10 RAID10 RAID10 Storage Enclosure Storage Processor 3GB/sec Storage Processor 3GB/sec RAID10 RAID10 RAID10 RAID10 Storage Enclosure
Take DL380 FT 3.0 hardware – 20TB solution SQL Server 2008 R2 – 2.4GB/sec benchmark queries SQL Server 2012 – 5GB/sec with ColumnStore Indexes No hardware changes No Solid State Drives 10x – 100x query improvement with ColumnStore
Trace Flags DB file placement Tempdb: 8-12 files max Index light – optimise for sequential scan throughput Capacity is based on Page and ColumnStore compression Support for AlwaysOn Failover Clustering
Use SQLIO for validation Aim for consistent MB/sec above published #s Take care with DB file placement On 30TB+ configuration pay attention to SP-LUN mappings Simulate FT LUN layout in Dev environment Take advantage of ColumnStore for extra performance Set MAXDOP to number of physical cores Use Resource Governor to limit memory/query to 10-15%
Fast Track Data Warehouse SQL Server 2012
SQL Server Parallel Data Warehouse
What is Parallel Data Warehouse
• • Scalable Scales from 10’s to 100’s of TB of Data Implemented as Massively Parallel Processing (MPP) System • • Standards Based Leverages Commodity Hardware Speaks SQL Server Language (T-SQL) • • Flexible Offers Hardware of Choice (HP/Dell) Supports Multiple Hardware Architectures • Cost Effective Low Price/TB Ratio
• • • • • • • SQL Server Symmetric Multiprocessing (SMP) System ‘Shared Everything’ Architecture High Contention for System Resources (e.g. I/O, CPU) Extremely High Concurrency for Some Workloads Limited Scalability (up to 95TB) Requires Building High Availability Rich Functionality for OLTP and Data Warehousing • • • • • • • Parallel Data Warehouse Massively Parallel Processing (MPP) System ‘Shared Nothing’ Architecture Balanced Resource Utilization (e.g. I/O, CPU, Memory) Higher Concurrency for Very Complex Workloads Excellent Scalability (up to 1PB) Built-in High Availability and Redundancy Tuned for Data Warehousing
Client Drivers Support / Patching ETL Load Interface Corporate Backup Solution Control Rack Control Nodes Active / Passive Management Servers SQL Landing Zone Backup Node Compute Nodes Data Rack SQL SQL SQL Spare Compute Node SQL SQL SQL SQL SQL SQL SQL Storage Nodes
Client Drivers SQL
Client Connections Always Go Through The Control Node Contains No Persistent User Data • • PDW “Secret Sauce” • • • Processes SQL requests Prepares execution plan Orchestrates distributed execution Local SQL Server Final Query Plan Processing Result Aggregation • • • Drivers TDS Protocol (SQL Native 10) Wire Protocol (SeQuel Link) ODBC, OLE-DB, ADO.NET and JDBC Client Drivers for both 32-bit and 64-bit
Management Node
Support / Patching
Management Node
Runs the Windows Domain Controller (Active Directory) Used for deploying patches to all nodes in the appliance Holds images in case a node needs reimaging
ETL Load Interface
Landing Zone
Provides High-Capacity Storage for Data Files from ETL processes Integration Services available on the Landing Zone Connected to Internal Network Available as Sandbox for other Applications and Scripts that Run on Internal Network Source Landing Zone Files Data Loader Compute Nodes
DWLOADER or SSIS
Corp. Backup Solution
Coordinated Backup across Nodes Database-Level Backup • • Full or Differential Metadata Backup Can Restore to a Larger Appliance • • Optional Item – 1 Size per Configuration Up to 654TB of Capacity Orderable in Different Capacities
“PDW Node”
Compute Node Storage node
Each MPP Node is a Highly Tuned SMP Node with Standard Interfaces Dedicated Hardware, Database, and Storage Running SQL Server 2008 Enterprise Edition SQL as Primary Interface
Compute Node Hardware
Enterprise-Class DBMS Dual Multi-Core Processors TempDB Workspace
DUAL 4Gb FC CPU CPU RAM
Dual InfiniBand • • • Pre-Configured for Each SQL Server Instance on Each Compute Node.
• • Drives Configured as RAID 1 to Avoid Appliance Failover for a Single-Drive Failure Configured with 3 LUNs (i.e. 3 RAID 1 Pairs) TempDB Used for the Following Purposes: Sort-Work Area for Data Loading into Clustered Index Tables Spill Area for Hash Joins Not Fitting into Memory Temporary PDW Tables
Dual 8 GB Fiber Storage Processor Dual 8 GB Fiber Storage Processor
• • Dual Fiber Channel Controllers Active/Active Provides Fault Tolerance
Data/Log Drives (RAID 1) - LFF Hot Spare
LUN1
Small Form Factor Drives (SFF) (24) – 8 RAID 1 groups containing a single LUN Large Form Factor Drives (LFF) (11) – 4 RAID 1 groups containing 2 LUNs each
LUN2 LUN3 LUN4 LUN5 LUN6 LUN7 LUN8 LUN1 LUN2 LUN3 LUN4 LUN5 LUN6 LUN7 LUN8 LUN 9 LUN 9 LUN 9 Log LUN 9 LUN 9 LUN 9
SFF – 6 disk in a RAID 1 + 0 group LFF – 2 disk in a RAID 1 group SFF - 2 Hot Spares LFF – 1 Hot Spare
Hot Spare Hot Spare (SFF)
Storage Node – Physical File Layout
LUN1 FG UDB_Dist_A UDBDist.ndf
UDBRepl.ndf
LUN2 FG UDB_Dist_B UDBDist.ndf
UDBRepl.ndf
LUN3 FG UDB_Dist_C UDBDist.ndf
UDBRepl.ndf
LUN4 FG UDB_Dist_D UDBDist.ndf
LUN5 FG UDB_Dist_E UDBDist.ndf
UDB Replicated FG UDBRepl.ndf
UDBRepl.ndf
LUN6 FG UDB_Dist_F UDBDist.ndf
UDBRepl.ndf
LUN7 FG UDB_Dist_G UDBDist.ndf
UDBRepl.ndf
LUN8 FG UDB_Dist_H UDBDist.ndf
UDBRepl.ndf
LUN 9 LUN 9 LUN 9 Log LUN 9 LUN 9 LUN 9
8 Distribution Filegroups 1 Replicated Filegroup with 8 Files 1 Log Filegroup
PDW Benefits – Massive Parallel Processing
Control Rack Query 1 Control Nodes Active / Passive SQL Management Node Landing Zone Backup Node Data Rack Compute Nodes ?
?
?
?
?
?
?
?
?
?
SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL Spare Compute Node Storage Nodes
Query 1 is Submitted to SQL Server via the Control Node.
Query is Executed on All 10 Nodes.
Results are Sent Back to the Client.
• • • Data Volume 80 TB data warehouse analyzing data from exchanges Existing system based on SQL SMP farm 2 different clusters of 6 servers each • • • Requirement Linear scalability with additional hardware Support hourly loads with SSIS – 300GB/day BI Integration: SSRS, SSAS and PowerPivot • • • • AU3 Feedback SP and increased T-SQL support was great Migrating SMP SSRS to PDW was painless 142x for scan heavy queries & no summary tables Enabled queries that do not run on existing system ETL Operational DB’s PDW Portal Reports Dashboards Scorecards
• • Data Volume Currently 11 TB data warehouse analyzing data from transactional and clickstream sources (2 years data) Business need to expand to 7 year data window, i.e. ~39 TB. • • • Requirement Scalability - growing data volume does not affect performance Performance and ad-hoc analysis for interactive querying by users BI Integration with Microsoft BI stack - SSAS and SSRS • • AU3 Feedback SSAS cubes worked ‘out-of-box’ Performance an order of magnitude faster than existing system (~30x on an expanded data set) Click Stream Nielsen PDW SSAS OLTP system
Loading Data
Breaking the Marketing Numbers
10X Performance Best-in-class Manageability Low Entry Cost More BI Tools, Any Language
Breakthrough Performance 10X Faster Query Performance Drives Greater Productivity 6X Support More Users with High Concurrency Improvement in Data Movement in the appliance Faster Throughput for Mixed Workloads
Best-in-Class Manageability Reduced operational cost through unified management of appliances and other SQL instances Reduced cost of Upgrading from Scale Up SQL Server to MPP PDW Integration with Cost
Low Entry Cost New Lower Cost Half Rack Solution Order From Your Preferred Vendor HP DELL
More BI Tools, Any Language Greater Support for Non Microsoft BI & ETL Tools Use PDW on Data in Any Language SAS Hadoop Informatica Microstrategy SAP Business Objects International Data Support
Session Objectives • • • Scaling Microsoft SQL Server to manage the trend to analyse and report on increasing data volumes.
Key Takeaways The Fast Track Reference Architecture provides a cost-effective approach to accelerate your data warehouse strategy. Providing a guide to building a reliable, scalable solution that will help you reduce your risk and save time.
Parallel Data Warehouse is a preconfigured appliance based upon the proven SQL Server database technology. PDW is capable of scaling out as your data volumes grow, reducing the maintenance overhead to a minimum and provides the raw performance an Enterprise Data Warehouse requires.