Fast Track Data Warehouse SQL Server 2012 OLTP – Tune your application (DW less control) Business Case - Budget Time to value.

Download Report

Transcript 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.