Transcript Concepts and Principles SMP ** Workload requirements usually drive the architecture decision MPP with PDW.

Concepts and Principles
SMP
** Workload requirements usually drive the architecture decision
MPP with PDW
What is PDW?
Control Nodes
Compute Nodes
Active / Passive
SQL
Dual
Infiniband
Reporting Services
Analysis Services
Integration Services
SQL
Data Center
Access
SQL
SQL
SQL
SQL
SQL
ETL Load Interface
SQL
SQL
Corporate Backup
Solution
Corporate Network
Dual Fiber Channel
R2
Client Drivers
Passive Compute Node
Private Network
Enterprise Class
DBMS
Dual Multi-Core
Processors
TempDB
Workspace
HP
DL360 G6
1U
Intel Nehalem
8 Cores
Hyper threaded
72 GB
6 – 300GB 10K SAS
DELL
R610
1U
Intel Nehalem
8 Cores
Hyper threaded
96 GB
4 – 300GB 10K SAS
Models listed as of SQL Server 2008 R2 PDW MTP2 release
** Server models could change before RTM**
DUAL
4Gb FC
Stg Processor
Data & Log Drives
(RAID 10)
Hot
Spare
DUAL
4Gb FC
Stg Processor
450 GB
15K
SAS
36 TB
450 GB
15K
SAS
45 TB
1 TB
7.2K
SATA
80 TB
1 TB
7.2K
SAS
100 TB
Models listed as of SQL Server 2008 R2 PDW MTP2 release
** Storage models and drives could change before RTM**
What is it doing under the hood?
Time Dim
Product Dim
Date Dim ID
Calendar Year
Calendar Qtr
Calendar Mo
Calendar Day
Prod Dim ID
Prod Category
Prod Sub Cat
Prod Desc
SQL
Sales Facts
Date Dim ID
Store Dim ID
Prod Dim ID
Mktg Camp Id
Qty Sold
Dollars Sold
Store Dim
Store Dim ID
Store Name
Store Mgr
Store Size
SQL
SQL
Mktg
Campaign Dim
SQL
Mktg Camp ID
Camp Name
Camp Mgr
Camp Start
Camp End
15
Time Dim
Product Dim
Date Dim ID
Calendar Year
Calendar Qtr
Calendar Mo
Calendar Day
Prod Dim ID
Prod Category
Prod Sub Cat
Prod Desc
Store Dim
Store Dim ID
Store Name
Store Mgr
Store Size
SQL
SQL
Sales Facts
Date Dim ID
Store Dim ID
Prod Dim ID
Mktg Camp Id
Qty Sold
Dollars Sold
Smaller Dimension Tables are
Replicated on Every Compute
Node
SQL
Mktg
Campaign Dim
SQL
Mktg Camp ID
Camp Name
Camp Mgr
Camp Start
Camp End
16
Larger Fact Table is Hash
Distributed Across All
Compute Nodes
Time Dim
Product Dim
Date Dim ID
Calendar Year
Calendar Qtr
Calendar Mo
Calendar Day
Prod Dim ID
Prod Category
Prod Sub Cat
Prod Desc
SQL
Sales Facts
Date Dim ID
Store Dim ID
Prod Dim ID
Mktg Camp Id
Qty Sold
Dollars Sold
Store Dim
Store Dim ID
Store Name
Store Mgr
Store Size
SQL
SQL
Mktg
Campaign Dim
SQL
Mktg Camp ID
Camp Name
Camp Mgr
Camp Start
Camp End
17
Distributed Table
Compute Node
User Database
TempDB
Distributed table hashed within
node into physical tables
SQL Server 2008 SP2
Storage Node
Storage Processor
Data LUNs
Storage Processor
Data LUNs
Tx Logs
Hot
Spare
Each distribution lands on a specific LUN
Replicated tables striped across LUNs
Query Tool
Control Node
Parse SQL
Compute Node
Compute Nodes
Compute Nodes
Data Movement Service (DMS)
Validate & Authorize
Build MPP Plan
Data
Movement
Service
(DMS)
Execute Plan
TempDB
User Data
SQL Server
Return Data to Client
PDW Engine
DW Authentication
DW Configuration
DW
Schema
TempDB
SQL Server
Control Rack
Data Rack
Control Node
Compute Nodes
Load Manager
Creates Staging
Tables
DMSEngine
Ser er
PDW
Load
Manager
SQL
Server
DMS
Manager
DMS Reads
Load Data and
buffers records
to Send to
Compute Nodes
round-robin
DMS
Infiniband
DWLoader
invoked/
SSIS
Storage Nodes
Each row is converted
for bulk insert and hash
the distribution column
DMS
Converter
Sender
Receiver
Writer
Hashed row is sent to
appropriate node
receiver for loading
Landing Zone
Load
File/SSIS
Load
Client
SSIS
API
DMS
DMS
Converter
Sender
Receiver
Writer
Received
row is
pushed onto
writer thread
Row is bulk
inserted into
staging
table
20
Node1
Dist A
Node1
Dist B
Node1
Dist H
Insert-Select
Load
Data
Bulk Insert
Sort each
BATCH
in memory or
TempDB
Bulk Insert
Sort each
BATCH
in memory or
TempDB
Bulk Insert
Sort each
BATCH
in memory or
TempDB
Sort each
partition
In memory or
TempDB
Partitioned
Staging
Table
(CIDX)
Partitioned
Final
Table
(CIDX)
Insert-Select
Load
Data
Sort each
partition
In memory or
TempDB
Partitioned
Staging
Table
(CIDX)
Partitioned
Final
Table
(CIDX)
Insert-Select
Load
Data
Partitioned
Staging
Table
(CIDX)
Sort each
partition
In memory or
TempDB
Partitioned
Final
Table
(CIDX)
Concepts and Principles
MTP Feedback
Credit card processing
EDW Workload
Yellow
http://www.microsoft.com/sqlserver/2008/en/us/parallel-data-warehouse.aspx
Concepts and Principles
MTP Feedback
Questions?
www.microsoft.com/teched
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn
Sign up for Tech·Ed 2011 and save $500
starting June 8 – June 31st
http://northamerica.msteched.com/registration
You can also register at the
North America 2011 kiosk located at registration
Join us in Atlanta next year