This paper describes the design principles and guidelines used to achieve an optimally balanced 200TB Data Warehouse Fast Track reference architecture for SQL Server 2017 using Dell EMC™ PowerEdge™ R940 servers and Dell EMC SC9000 arrays.
This Dell EMC reference architecture paper describes the design principles and guidelines used to build an optimally balanced 200TB Data Warehouse Fast Track (DWFT) configuration for Microsoft SQL Server 2017 using Dell EMC PowerEdge R940 servers and Dell EMC SC9000 storage arrays. It was published in November 2018 as an initial release and is aimed at database administrators, business intelligence architects, storage administrators, IT directors and data warehousing users looking for sizing and design guidance.
The paper presents two recommended architectures, a single-server design and a highly available design, then details the hardware components (PowerEdge R940 server, QLogic QLE2662 16Gbps host bus adapter, Brocade 6505 SAN switch and SC9000 array) and the storage, server, Windows Server 2016 and SQL Server 2017 Enterprise Edition configuration settings. It also covers SQL Server 2017 column-store improvements, additional considerations for the highly available architecture, and DWFT certification.
| Reference architecture capacity | 200TB |
|---|---|
| Servers | Dell EMC PowerEdge R940 (14th generation) |
| Storage arrays | 2 x Dell EMC SC9000 |
| Additional storage | 2 x Dell Storage SC420 |
| Host bus adapter | QLogic QLE2662 16Gbps |
| SAN switch | Brocade 6505 |
| Operating system | Windows Server 2016 |
| Database software | Microsoft SQL Server 2017 Enterprise Edition |
| Column-store compression | Up to 10X compression in data size |
It uses Dell EMC PowerEdge R940 servers together with Dell EMC SC9000 storage arrays, plus two Dell Storage SC420 systems in the single-server figure.
Database administrators, business intelligence architects, storage administrators, IT directors and data warehousing users seeking sizing and design guidance for SQL Server 2017 business intelligence solutions.
They are engineered jointly by Dell EMC and Microsoft; Dell EMC tests the hardware and software optimizations and Microsoft crosschecks the performance results.
The paper states column-store indices can provide up to 10X compression in data size, reducing space, cost and I/O requirements.