Parallel Data Warehouse Overview

Introduction

SQL Server 2008 R2 Parallel Data Warehouse (PDW) is a highly scalable appliance for enterprise data warehousing. The PDW works by using Massively Parallel Processing Software and Appliance hardware to control several physical servers each running their own instances of SQL Server 2008. This control process allows the PDW to scale out query processing and objects across multiple nodes of CPU, storage etc.. This provides a significant boost of performance to even the widest queries for the widest tables. These instances all still appear as one instance to the end user even though a single PDW table may be distributed across several servers.

The MPP functionality comes from an acquisition of Datallegro by Microsoft in summer 2008. The engineering teams at Microsoft have been working on rewriting the software to work with SQL Server and have now accomplished their goal of releasing V1 of the PDW offering. As V1’s go it is very strong and already competing directly with the Teradata’s and Neteeza’s of the world. Currently the PDW offering is scaling up into the hundreds of terabytes without breaking a sweat.

The benefit to the MPP architecture and Microsoft’s implementation is that from a cost per TB or query perspective, Microsoft comes out way ahead. This is because the PDW is built on and expanded by commodity hardware. Not to say these are netbooks chained together, but we are talking midgrade to enterprise quality HP and Dell servers, not $800K Teradata racks.

This is a new direction for Microsoft since they have, until now, operated in the Symmetric Multi-processing (SMP) world, where the only option is larger more powerful single servers. In the SMP world, all the processing takes place on a single node or physical instance of the database. Limits are created by the CPU, memory and storage capacities. In PDW large tables are partitioned across multiple physical nodes, where each node has its own CPU, memory, and storage. This is called a “shared nothing“ architecture.

 

PDW Architecture

In PDW those multiple nodes controlled by the software and appliance are referred to as “Control Nodes”. Remember there are no proprietary hardware components in a PDW system all of it is commodity hardware, networking and storage. As the environment grows over time more control nodes can be added or upgraded individually with faster CPU, memory or network components. As new hardware comes out those servers can be repurposed for other roles in the enterprise that don’t require the same level of performance such as file servers. I have included some diagrams from Microsoft’s official documentation to help highlight the architecture. The first one is below.

 

For those of you who aren’t familiar with what the term appliance means I’m definitely not talking about your refrigerator. I am hungry though, not to self, no more in depth writing without packing a lunch. In computing an appliance is a special purpose application whose software and hardware work together in a single unit for a specific purpose. In PDW the software and server running it work together to accomplish scaling the queries and data across multiple nodes and tracking metadata, query response etc... The PDW appliance has a set of nodes that look like the diagram below from Microsoft.

 

  

The architecture of a typical PDW system would look something like the next diagram.

  

 

Some important things to note about the design of this architecture:

- The components are balanced with each other to avoid bottlenecks and all components are mirrored for enterprise quality redundancy.

- The control node sends the queries to the proper compute nodes, then organizes and returns the results.

- Queries are much fast then on an SMP system since the data is spread evenly over the different nodes and their storage.

- Scaling the architecture requires simply adding capacity to the appliance, eliminating the need for a replace or forklift style upgrade.

Server Roles in the PDW

There are several different server roles in the PDW architecture so let’s explore those.

Control Node

Clients connect to the control node since the PDW MPP engine runs here. These nodes contain no permanent user data, they only collect and display the results from queries. The control node is the central point for all hardware monitoring and contains the metadata about the PDW configuration, databases and objects. Its instance of SQL Server is there primarily for metadata and to make use of TempDB for some query types.

Compute Node

The compute nodes store the user data and perform most of the query execution. They are not typically accessible to the outside world since the Control node is doing all the connecting, collecting and correlating.

Landing Zone

We are not going into much detail on loading data in this article but there are some very specific things we will cover later in the series including the new adapters for SSIS and DWLoader utilities. The landing node is where the data can be “landed” prior to final loading into the compute nodes. This server CAN be access by windows users and can also run 3rd party hardware and software to help with data loading. Think of this as your staging or final load sandbox on the PDW.

Backup Node

This node will store your appliance backup files, and can also be access via windows and loaded with 3rd party hardware or software.

Management Node

If the control node is the slave driver, then the management node is the safety net. This node runs the domain controller for the PDW. You didn’t think Microsoft was going to trust this bad boy to your rusty old domain controller? No way, a shiny new one comes right in the rack. IT also is used for patch deployment and holds images of the different nodes in case a reimage is required.

 

Hub and Spoke Architecture

Enterprise Data Warehouses typically fit into one or several of the following areas:

1. Centralized data warehouse with the “single version of the truth” – These can be expensive and difficult to maintain depending on your companies staff and capabilities.

2. Decentralized data marts or departmental analysis with no central repository or EDW. – These can be more responsive to the business but are not generally the best tools for company analysis since they contain different versions of the truth and are very difficult to keep aligned across the organization. In addition they are often designed and built using whatever toolset the business unit has at hand so the technologies involved are often at odds as well.

3. Hub and spoke architectures with a central EDW and department data marts, fed from the EDW. Hub and spoke architectures are typically the best solutions for the largest enterprises. Those enterprises that would be looking at a PDW solution. It offers the combination of a strong centralized EDW which provides company wide data and single sources of truth for important information, while allowing for synchronization to smaller data marts for more customized departmental analysis. See the diagram below for an example of a Hub and spoke architecture.

 

The parallel copy capabilities from PDW provide a lot of improved flexibility here to move large amounts of data to the data marts. This parallel copying functionality is in theV1 release of PDW.

There are two types of tables in a PDW implementation. Replicated tables are tables where the table is stored in its entirety on each compute node. Distributed tables are split evenly over each compute node using a hashing algorithm. As you can imagine typically your replicated tables would be your dimension tables and those giant fact tables would be distributed across the compute nodes.

  

Connecting to PDW

PDW ships with a client tool to connect to the system called Nexus. Nexus looks and acts similarly to SSMS or SQL Server Management Studio but it is not Management Studio, it only runs PDW implementations. See an example of connecting to a PDW with Nexus below.

 

Creating Objects in PDW

There are several new object types in PDW but overall creating them is still essentially the same, with a twist. For instance:

   

Create Database

Creates a new database and the files used to store the database.

Syntax

CREATE DATABASE database_name    

 [ WITH ( <database_option> [ ,…n ] ) ][;]

<database_option> ::= AUTOGROW = ON | OFF   

    | REPLICATED_SIZE = replicated_size [ GB ]  

    | DISTRIBUTED_SIZE = distributed_size [ GB ]   

    | LOG_SIZE = log_size

Example:

CREATE DATABASE bidnPDW  

WITH   

   (REPLICATED_SIZE = 200 GB,
DISTRIBUTED_SIZE = 800 GB,
LOG_SIZE = 300 GB);

You’ll notice a trend beginning here. We are specifying options for replicated table areas and distributed table areas. These apply for the database as a whole and will be stored individually or spread evenly based on the type of table we create.

Creating Tables

A. Creating a replicated table

The following example creates a replicated table with three columns and with no partitions. The id column includes a NOT NULL constraint. Notice there is no “distributed” flag engaged here.

CREATE TABLE bidnTable1
( id integer NOT NULL,
lastName varchar(20),
zipCode varchar(6)
);

B. Creating a distributed table

The following example creates the same table as the previous example; however, in this example the table is distributed (on the id column) instead of replicated.

CREATE TABLE bidnTable2
( id integer NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)

WITH ( DISTRIBUTION = HASH (id) );

C. Creating a partitioned table

The following example creates the same table as in example A with the addition of RANGE LEFT partitioning on the zipCode column.

CREATE TABLE bidnTablePart

( id integer NOT NULL,
lastName varchar(20),
zipCode integer
)

WITH ( PARTITION

( id RANGE LEFT FOR VALUES (10, 20, 30, 40, 50, 60, 70, 80, 90)));

In this example, data would be sorted into the following partitions:

 

 

Partition

1

2

3

4

5

6

7

8

9

10

Value

col <= 10

10 < col <= 20

20 < col <= 30

30 < col <= 40

40 < col <= 50

50 < col <= 60

60 < col <= 70

70 < col <= 80

80 < col <= 90

90 < col

 

 If we created the table with RANGE RIGHT instead of RANGE LEFT (default), the data would be sorted into the following partitions (this is the same as in your normal SQL Server partitioning:

 

Partition

1

2

3

4

5

6

7

8

9

10

Value

col < 10

10 <= col < 20

20 <= col < 30

30 <= col < 40

40 <= col < 50

50 <= col < 60

60 <= col < 70

70 <= col < 80

80 <= col < 90

90 <= col

 

E. Creating a table with date partitioning

The following example creates a new table, bidnTableDatePT, with partitioning on a date column and distributing the data over the compute nodes on the OrderKey. This will provide the scalability and increased seek speed gained from partitioning. Notice we have to specify the distribution key since it’s a distributed table and then our partitioning information second.

CREATE TABLE bidnTableDatePT (

    l_orderkey bigint,
l_partkey bigint,
l_suppkey bigint,
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(25),
l_shipmode char(10),
l_comment varchar(44))

WITH

  ( DISTRIBUTION = HASH (l_orderkey),
PARTITION ( l_shipdate
RANGE RIGHT FOR VALUES

      (

      '1992-01-01','1992-02-01','1992-03-01','1992-04-01','1992-05-01','1992-06-01','1992-07-01','1992-08-01','1992-09-01','1992-10-01','1992-11-01','1992-12-01',

      '1993-01-01','1993-02-01','1993-03-01','1993-04-01','1993-05-01','1993-06-01','1993-07-01','1993-08-01','1993-09-01','1993-10-01','1993-11-01','1993-12-01',

      '1994-01-01','1994-02-01','1994-03-01','1994-04-01','1994-05-01','1994-06-01','1994-07-01','1994-08-01','1994-09-01','1994-10-01','1994-11-01','1994-12-01',

      '1995-01-01','1995-02-01','1995-03-01','1995-04-01','1995-05-01','1995-06-01','1995-07-01','1995-08-01','1995-09-01','1995-10-01','1995-11-01','1995-12-01',

      '1996-01-01','1996-02-01','1996-03-01','1996-04-01','1996-05-01','1996-06-01','1996-07-01','1996-08-01','1996-09-01','1996-10-01','1996-11-01','1996-12-01',

      '1997-01-01','1997-02-01','1997-03-01','1997-04-01','1997-05-01','1997-06-01','1997-07-01','1997-08-01','1997-09-01','1997-10-01','1997-11-01','1997-12-01',

      '1998-01-01','1998-02-01','1998-03-01','1998-04-01','1998-05-01','1998-06-01','1998-07-01','1998-08-01','1998-09-01','1998-10-01','1998-11-01','1998-12-01'

      )));

We can also do some old favorites in PDW such as Create Table as Select and regular joins between distributed tables and replicated tables. The two examples below are simple versions of these queries.

Create Tables as Select

CREATE TABLE FactResellerSalesNew
WITH (

DISTRIBUTION = HASH(ProductKey),
PARTITION ( OrderDateKey
RANGE RIGHT FOR VALUES
(20010101, 20020101, 20030101,20040101, 20050101))
,CLUSTERED INDEX(OrderDateKey))

AS
SELECT * FROM dbo.FactResellerSales

Query Tables with Join

SELECT b.ProductKey, b.OrderQuantity

FROM dbo.DimPromotion a /* Replicated Table*/

JOIN dbo.FactInternetSales b /* Distributed Table*/ ON a.PromotionKey = b.PromotionKey

WHERE a.PromotionKey = 13;

 

In this article I covered the basics of the PDW architecture, how it fits together and what the major components are. We also discussed why this is an exciting new offering and how it compares with other solutions you or your company may be considering. These units are already up at Microsoft training centers around the country and available for testing with your data if you’re interested. Please let me know or contact your Microsoft TSP in your area to get you setup for a proof of concept.

In the next article we’ll talk about working with additional objects in the PDW environment, how to create and manage them as well as some of the new flags and options for those objects. It’s important to understand that while not all companies will jump on this PDW appliance it provides an industry leading, cost effective and easily scalable solution and options over the competition. Next week I will shoot you some resources on the new PDW system from Microsoft, but for now I will leave you with some terms to remember.

Terms to Remember

1. MPP – Massively Parallel Processing – Scaling processing across multiple nodes or servers

2. SMP – Symmetric Multi Processing – Processing occurs within ones instance of SQL Server and its CPU, memory and storage.

3. EDW – Enterprise Data Warehouse

4. Hub and Spoke – Central EDW with departmental or business function data marts for specialized analysis.

5. Distributed Tables – Tables that are spread across the compute nodes in a PDW environment. These are typically fact tables and are evenly spread using a hashing algorithm.

6. Replicated Tables – These tables are all located in their entirely on the compute nodes and are typically dimension or lookup tables that do not require the distribution capabilities of PDW to manage them.