Microsoft SQL Server Parallel Data Warehouse (PDW) Explained

Microsoft SQL Server Parallel Data Warehouse (PDW), formally called by its code name “Project Madison”, is an edition of Microsoft’s SQL Server 2008 R2 that was released in December 2010. PDW is Microsoft’s reworking of the DatAllegro Inc. massive parallel processing (MPP) product that Microsoft acquired in July 2008. It only works with certain hardware (two so far), the first of which is HP Enterprise Data Warehouse Appliance (Dell is the other, with a couple more to come in the near future: IBM and Bull). This edition of SQL Server can’t be bought as an independent piece of software, it has to be bought along with the hardware.

So what is MPP? Until now, SQL Server has been a Symmetric Multiprocessing (SMP) solution, which essentially means it uses one server. MPP provides scalability and query performance by running independent servers in parallel. That is the quick definition. For more details, read What MPP means to SQL Server Parallel Data Warehouse.

MPP is also available from other companies such as EMC Greenplum, Teradata, Oracle Exadata, and IBM Netezza, but those use proprietary systems, where PDW can be used with commodity hardware, providing a much lower cost per terabyte. But it’s still not in-expensive: The hardware and installation will cost around $2 million (not including software licenses), but gets you “200 times faster queries and 10 times the scalable than traditional Microsoft SQL Server deployments” (see press release). PDW also comes with its own support model.

Microsoft has had clustering capabilities in SQL Server for a while, but the scalability part was lacking. This is where PDW comes in. Scalability in PDW means handling tens of terabytes of data and then moving to hundreds of terabytes worth (up to 600 TB). At about 50 terabytes to 60 terabytes of data, clustering is needed; thereafter, clustering starts to approach its limits, and that is when you need to move to PDW. Clustering brings concurrency to the system and reduces load, but it can’t reduce the time that a single query would take without any resource latency. To break this barrier, parallelism would be required to execute bits of the same request simultaneously and this is what exactly this setup would bring to the table. PDW partitions large tables across multiple physical nodes, each having its own dedicated CPU, memory, storage, and each running its own instance of SQL Server in a parallel shared nothing design. Tables can either be replicated, where a copy will be on each node (usually for dimension tables), or distributed, where portions of a table are uniformly distributed across all nodes (usually for fact tables).

One drawback to PDW is that it does not use SQL Server Management Studio, but uses a third-party tool called Nexus Chameleon (this third-party tool is needed because SSMS hasn’t been reworked to connect directly to the control node of the Parallel Data Warehouse). It also uses its own query engine and not all features of SQL Server are supported. So, you might not be able to use all your DBA tricks. And you wouldn’t want to build a solution against SQL Server and then just hope to upsize it to Parallel Data Warehouse Edition.

PDW uses multiple servers within the appliance, virtualized as if they were one unified data warehousing resource available. It can use up to 480 cores. PDW Works by controlling several different physical servers each running their own instance of SQL Server 2008 R2. The database and it’s tables are spread across these physical servers but appear as one database and table(s) to the end-user. The data warehouse appliance or brain of the PDW manages query execution and the meta data for what is stored and processed on what portion of the PDW.

Microsoft IT’s experience with PDW showed when they migrated Information Security Consolidated Event Management (ICE) to PDW, they saw query performance improve to an average of 15-20 times faster in PDW, SSIS data load throughput of up to 285 GB/hour (with minimal query performance impact), and support for up to 12 TB/day in throughput in SSIS. See video.

Part of the technology incorporated into PDW includes a parallel database copy that enables rapid data movement and consistency between PDW and data marts used by SSAS.

In short, PDW is ideal for large data warehouses and BI, but not for OLTP systems. Write one check, and you get a complete soup-to-nuts data warehouse storage engine that includes everything from the servers, SAN, configuration, and training.

Here is the layout of the HP Enterprise Data Warehouse Appliance (full specs here with review and architecture overview and performance guide). The architecture is hub-and-spoke and supports up to 40 servers, made up of a control rack and a data rack: