65TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R640 and Dell EMC PowerVault ME4024 Configuration and performance results Abstract Design principles and guidelines used to achieve an optimally balanced 65TB Data Warehouse Fast Track reference architecture for Microsoft® SQL Server® 2017 using Dell EMC™ PowerEdge™ R640 servers and Dell EMC PowerVault™ ME4024 storage.
Revisions Revisions Date Description September 2018 Initial release Acknowledgements Author: Doug Bernhardt Special thanks to Jamie Reding at Microsoft. The information in this publication is provided “as is.” Dell Inc. makes no representations or warranties of any kind with respect to the information in this publication, and specifically disclaims implied warranties of merchantability or fitness for a particular purpose.
Table of contents Table of contents Revisions.............................................................................................................................................................................2 Acknowledgements .............................................................................................................................................................2 Table of contents .................................................................................................
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 different DWFT reference architectures for SQL Server 2017, comprised of PowerEdge R640 servers and PowerVault ME4024 arrays. 2.1 Single-server reference architecture Figure 1 illustrates the single-server reference architecture with its major elements and Table 1 lists the component details.
Recommended reference architectures Single-server reference architecture details Component Description Server PowerEdge R640 Processors Dual Intel® Xeon® Gold 6126 Processor (2.
Recommended reference architectures 2.2 Highly available reference architecture For database high availability, 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 R640 Processors Dual Intel® Xeon® Gold 6126 Processor (2.
Hardware components 3 Hardware components 3.1 PowerEdge R640 server The PowerEdge R640 server is a two-socket 1U rack server with impressive processor performance, a large memory footprint, extensive I/O options, and a choice of dense, high performance storage or low-cost, highcapacity storage. The R640 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 All-inclusive software: The ME4 Series software provides volume copy, snapshots, IP/FC replication, VMware® VCenter Server® and VMware Site Recovery Manager™ integration, SSD read cache, thin provisioning, three-level tiering, ADAPT (distributed RAID), and controller-based encryption (SEDs) with internal key management. Management: An integrated HTML5 web-based management interface (ME Storage Manager) is included. For more information, see the ME4 Series product page.
Storage configuration 4 Storage configuration 4.1 Cabling The hardware components were connected using Dell EMC best practices. In a direct-connect configuration, each HBA should have one port connected to the top storage controller and the other port connected to the bottom storage controller (see Figure 3). Single server configuration cabling diagram 4.2 Storage pools Two virtual storage pools were created with twelve disks each and assigned a data protection type of RAID-5.
Storage configuration 4.3 Host setup The process of setting up a host object creates the I/O path (or paths) between a volume and a server. Each FC WWN will display as an initiator. In this configuration, all four discovered FC initiators (ports) are being added to the new host (see Figure 4). Host setup 4.4 ME4 Series volumes None of the volumes were configured to use snapshots. This reference architecture does not include hardware resources to support the use of snapshots.
Storage configuration 14 Volume name Pool Volume purpose SQLData04 B Data files for the data warehouse SQLData05 A Data files for the data warehouse SQLData06 B Data files for the data warehouse SQLData07 A Data files for the data warehouse SQLData08 B Data files for the data warehouse SQLTempDBData01 A Data files for tempdb SQLTempDBData02 B Data files for tempdb 65TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R640 and D
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, visit https://msdn.microsoft.com/en-IN/library/ms190730.aspx. 6.
Windows Server 2016 configuration Table 5 shows the volume labels and access paths used for the reference configuration. Windows volume details 6.4 ME4 Series volume name Windows volume label Access path MPHost MPHost M:\ SQLSystem SQLSystem M:\ft\SQLSystem SQLLog SQLLog M:\ft\SQLLog SQLData01 SQLData01 M:\ft\SQLData01 SQLData02 SQLData02 M:\ft\SQLData02 SQLData03 SQLData03 M:\ft\SQLData03 SQLData04 SQLData04 M:\ft\SQLData04 SQLData05 SQLData05 M:\ft\SQLData05 SQLData06 SQLData
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 864GB which leaves 32GB for the operating system. 7.
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: • • • 19 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 20 DWFT for SQL Server 2017 certification 65TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R640 and Dell EMC PowerVault ME4024 | 3918-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 solutions technical documents provide expertise that helps to ensure customer success on Dell EMC storage platforms. The following ME4 Series publications and additional resources are available at Dell.com/support.
