385TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R940 and SC9000 Configuration and performance results Abstract This paper describes the design principles and guidelines used to achieve an optimally balanced 385TB Data Warehouse Fast Track reference architecture for SQL Server 2017 using Dell EMC™ PowerEdge™ R940 servers and Dell EMC SC9000 arrays.
Revisions Revisions Date Description November 2018 Initial release March 2019 Updated from 200TB to 385TB rating due to change in MS DWFT rating calculation Acknowledgements Author: Doug Bernhardt Special thanks to Jamie Reding at Microsoft. The information in this publication is provided “as is.” Dell Inc.
Table of contents Table of contents Revisions.............................................................................................................................................................................2 Acknowledgements .............................................................................................................................................................2 Table of contents .................................................................................................
Table of contents 7.3 Max degree of parallelism (MAXDOP) .............................................................................................................21 7.4 Resource governor ...........................................................................................................................................21 7.5 Database configuration .....................................................................................................................................21 7.
Executive summary Executive summary Dell EMC™ and Microsoft®, in cooperation, provide guidelines and principles to assist customers in designing and implementing a balanced configuration for Microsoft SQL Server® data warehouse workloads to achieve out-of-the-box scalable performance. These database reference architectures enable each of the components in the database stack to provide optimal throughput to match the database capabilities of the specific setup.
Microsoft SQL Server 2017: Data warehousing with improved column-store technology 1 Microsoft SQL Server 2017: Data warehousing with improved column-store technology Microsoft SQL Server 2017 has made significant improvements in data warehousing technologies and performance, including column-store features as well as many other improvements. Column-store indices offer great advantages over traditional row stores for analytics and data warehousing queries.
Recommended reference architectures 2 Recommended reference architectures The following subsections describe the two DWFT reference architectures for SQL Server 2017, comprised of PowerEdge R940 servers and SC9000 arrays. 2.1 Single-server reference architecture Figure 1 illustrates the single-server reference architecture with the major elements. Table 1 lists the component details.
Recommended reference architectures Single-server reference architecture details Component Description PowerEdge R940 Server Processors Four Intel® Xeon® Platinum 8168 Processors (2.
Recommended reference architectures 2.2 Highly available reference architecture For database high availability, Microsoft Windows® failover clustering is recommended. Using Microsoft clustering services, one database server is configured as the primary (active) server and the second server is configured as the secondary (passive) server. The secondary server should have exactly the same configuration as the primary server.
Recommended reference architectures Highly available reference architecture details Component Description Server Two PowerEdge R940 Processors Four Intel® Xeon® Platinum 8168 Processors (2.
Hardware components 3 Hardware components 3.1 PowerEdge R940 server The PowerEdge R940 server is a highly versatile, 4U rack server with up to four Intel® Xeon® Scalable processors, a large memory footprint, extensive I/O options, and a choice of dense, high-performance storage or low-cost, high-capacity storage. The R940 server offers simplified management, purposeful design, and energy efficiency with support for the latest Intel® Xeon® series processors as well as NVDIMM memory.
Hardware components • • Dell ProSupport™ services: Reduce deployment costs with remote installation options that ensure the project is successful the first time. Persistent software licensing: Future-proof the investment, and minimize the cost of upgrades and expansions. With up to 1,024 total drives and 6PB2 raw capacity in a single array, the SC9000 is massively scalable. All data is managed through the primary storage controller, deployed in redundant pairs.
Storage configuration 4 Storage configuration 4.1 Cabling The hardware components were connected using Dell EMC best practices. Port 1 on each HBA in the server and ports 1 and 2 on each HBA in the SC9000 were connected to the same Brocade 6505 switch. Port 2 on each HBA in the server and ports 3 and 4 on each HBA in the SC9000 were connected to the other Brocade 6505 switch.
Storage configuration 4.2 I/O ports The FC ports were configured to use two fault domains in virtual port mode. Ports 1 and 2 from each HBA on each controller were put into fault domain 1 and ports 3 and 4 from each HBA on each controller were put into fault domain 2. 4.3 Disk folder All 48 disks were added to the Assigned disk folder. The SC Series distributed spare feature introduced with SCOS 7.3.1 makes all disks active, so none are reserved as dedicated spares. 4.
Storage configuration Creating the custom storage profile, RAID 5 All Tiers 4.6 Read and write cache Read and write cache were both enabled on the array. 4.7 SC Series server object The process of mapping a volume to a server object creates the I/O path (or paths) between a volume and a server. When creating a server object, the operating system of the server is defined. The SC Series array includes both single path and multi-path definitions for each version of Windows.
Storage configuration volume assigned to each controller. If the volumes are created and mapped to the server, one right after the other, the array should automatically distribute the volumes evenly across the controllers. Otherwise, the controller can be manually selected using the advanced options when mapping the volume to the server. To achieve optimal results, the SQL Server data and tempdb volumes must be evenly distributed across the controllers.
Server configuration 5 Server configuration 5.1 System BIOS The system profile is set to Performance. All other options, outside of iDRAC configuration, were left at their factory default settings. The Logical Processor option, under Processor Settings, is left at its default setting of Enabled. This enables Intel® Hyper-Threading Technology, which maximizes the number of logical processors available to SQL Server. 5.
Windows Server 2016 configuration 6 Windows Server 2016 configuration 6.1 Power plan To maximize performance, the server was configured to use the High performance power plan as shown in Figure 5. Windows power plan 6.2 Lock pages in memory To prevent Windows from paging SQL Server memory to disk, the Lock pages in memory option was enabled for the SQL Server service account. For information on enabling this option, see the article Enable the Lock Pages in Memory Option. 6.
Windows Server 2016 configuration Table 6 shows the volume labels and access paths used for the reference configuration. Windows volume details 19 SC Series volume name Windows volume label Access path DWFT-4S - MPHost MPHost M:\ DWFT-4S - SQLSystem SQLSystem M:\ft\SQLSystem DWFT-4S - SQLLog SQLLog M:\ft\SQLLog DWFT-4S - SQLData01 SQLData01 M:\ft\SQLData01 DWFT-4S - SQLData02 SQLData02 M:\ft\SQLData02 DWFT-4S - SQLData03 SQLData03 M:\ft\SQLData03 DWFT-4S - SQLData04 SQLData04 M:\ft\
Windows Server 2016 configuration 6.4 MPIO MPIO was configured using Dell EMC best practices. MPIO best practices for the SC Series array are documented in the best-practices paper Dell EMC SC Series Storage and Microsoft Multipath I/O. The MPIO policy for all volumes is left at the default setting of Round Robin, allowing volumes to use all paths between the server and the storage array.
SQL Server 2017 Enterprise Edition configuration 7 SQL Server 2017 Enterprise Edition configuration 7.1 Grant perform volume maintenance task privilege During installation of SQL Server 2017, the option to grant the SQL Server Database Engine Service the Perform Volume Maintenance Task privilege was selected. 7.2 SQL Server maximum memory The maximum server memory for this reference architecture should be set to 3040GB which leaves 32GB for the operating system.
Additional considerations for the highly available reference architecture 8 Additional considerations for the highly available reference architecture The highly available (HA) reference architecture leverages Windows Failover Clustering to achieve high availability. When configuring a Windows failover cluster, there are additional storage considerations: • • • 22 The recommended quorum configuration is to allow all cluster nodes to have quorum votes and use a disk witness.
DWFT for SQL Server 2017 certification 9 23 DWFT for SQL Server 2017 certification 385TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R940 and SC9000 | 4032-RA-SQL
Summary 10 Summary Dell EMC, in partnership with Microsoft, enables customers to deploy tested and validated data warehouse solutions using Data Warehouse Fast Track reference architectures for SQL Server 2017. These uniquely designed architectures ensure optimal business intelligence solutions.
Technical support and resources A Technical support and resources Dell.com/support is focused on meeting customer needs with proven services and support. Storage technical documents and videos provide expertise that helps to ensure customer success on Dell EMC storage platforms.
