Deploying the 385TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R940 and SC9000 Deployment guide with step-by-step instructions Abstract This paper provides step-by-step instructions for building a balanced configuration for a Microsoft® SQL Server® data warehouse workload and requirements for preparing the hardware platform and provisioning the OS to achieve a balanced, optimized 385TB configuration.
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 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 .................................................................................................
Overview Overview This guide provides step-by-step instructions to build a balanced configuration for a Microsoft® SQL Server® data warehouse workload, as specified in the companion reference architecture document 385TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R940 and SC9000, available on the SC Series technical documents page.
Solution requirements 1 Solution requirements This section lists the hardware and software components required to implement the single server reference architecture. The versions of firmware on the server used to validate the reference architecture are also listed. Hardware and software components Component Description Server PowerEdge R940 Processors Four Intel® Xeon® Platinum 8168 Processors (2.
Deployment workflow 2 Deployment workflow This section outlines the sequence of deploying the 385TB Data Warehouse Fast Track for SQL Server 2017 reference architecture using the PowerEdge R940 server and SC9000 storage array. To deploy the reference architecture, perform the following tasks: 1. 2. 3. 4. Configure the SC9000 storage array. Configure the PowerEdge R940 server. Install and configure the Windows Server 2016 operating system. Install and configure SQL Server 2017 Enterprise Edition.
Cabling requirements 3 Cabling requirements Figure 2 shows how to cable the R940 server, the Fibre Channel (FC) switches, and the SC9000 storage array. Port 1 on each host bus adapter (HBA) in the server and ports 1 and 2 on each HBA in the SC9000 should be 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 should be connected to the other Brocade 6505 switch.
Configure SC9000 storage array 4 Configure SC9000 storage array This section describes the configuration of the SC9000 storage array. 4.1 Configure local ports Configure the FC ports to use two fault domains in virtual port mode. Put ports 1 and 2 from each HBA on each controller into fault domain 1, and put ports 3 and 4 from each HBA on each controller into fault domain 2. 4.2 Configure disks Put all 48 disks in the Assigned disk folder. The distributed spare feature introduced in SCOS 7.3.
Configure SC9000 storage array 4.6 Create server object A server object is used when presenting storage to a server. To create the server object, perform the following steps: 1. 2. 3. 4. 5. Open the DSM client and connect to the SC Series array. Click the Storage tab. Right-click Servers and select Create Server. The Create Server dialog box appears. In the Name textbox, enter the name of the server. In the Operating System drop-down list, select Windows Server 2016 Singlepath.
Configure SC9000 storage array 4.8 Create volumes A total of thirteen SC Series volumes are used in the reference architecture and are listed in Table 3.
Configure PowerEdge R940 server 5 Configure PowerEdge R940 server 5.1 Update firmware The firmware update feature of the Lifecycle Controller can be used to update the firmware on the server. The Lifecycle Controller is accessible during the server boot cycle. To update the firmware using the Lifecycle Controller, perform the following steps: 1. 2. 3. 4. 5. 6. 7. 8. 9. 5.2 Reboot the server and press F10 when prompted to enter the Lifecycle Controller.
Configure PowerEdge R940 server 5.3 Reset HBAs to default settings The HBAs can be reset using QLogic Fast!UTIL, which is accessible during the server boot cycle. To reset the HBAs using QLogic Fast!UTIL, perform the following steps: 1. Reboot the server and press [Ctrl]+[Q] when prompted to enter QLogic Fast!UTIL. 2. In Select Host Adapter, select the first port in the list and press [Enter]. 3. Reset the HBA port to factory defaults: a.
Install and configure Microsoft Windows Server 2016 6 Install and configure Microsoft Windows Server 2016 6.1 Install Microsoft Windows Server 2016 The OS Deployment feature of the Lifecycle Controller can be used to install the operating system. One advantage of this approach is that Windows will be installed with the proper drivers for the R940 server.
Install and configure Microsoft Windows Server 2016 6.2.1 Set the server name (optional) 1. In Server Manager, click Local Server on the left-hand side of the screen. 2. In the Properties pane, click the current server name next to the label, Computer name. The System Properties dialog box appears. 3. Click Change. The Computer Name/Domain Changes dialog box appears. 4. In the Computer name text box, enter the server name and click OK. 5. Click OK to acknowledge the computer restart. 6.
Install and configure Microsoft Windows Server 2016 5. On the Select installation type screen, select Role-based or feature-based installation, and click Next. 6. On the Select destination server screen, select the server and click Next. 7. On the Select server roles screen, click Next without selecting any roles. 8. On the Select features screen, perform the following: a. Scroll down and select Multipath I/O. b. Click Next. 9. Click Install. 10. When the installation is complete, click Close. 11.
Install and configure Microsoft Windows Server 2016 6.2.10 Remove SMBv1 (optional) 1. Run the following PowerShell command: Remove-WindowsFeature -Name "FS-SMB1" 2. Reboot the server. When the server is back online, log in as Administrator. 6.2.11 Configure Windows Updates (optional) 1. 2. 3. 4. 5. 6. 6.2.12 Click the start icon. Click the settings icon. Click Update & security. Under Update Status, click Check for updates. Apply all important updates.
Install and configure Microsoft Windows Server 2016 6.2.14 Change the optical drive letter (optional) Change the drive letter for the optical drive to Z: 1. Open Disk Management. 2. Right-click the optical drive in the list of disks in the lower pane of the center of the screen and select Change Drive Letter and Paths. The Change Drive Letter and Paths dialog box appears. 3. Click Change. The Change Drive Letter or Path dialog box appears. 4. Select Z from the drop-down list of drive letters and click OK.
Install and configure Microsoft Windows Server 2016 6.3.2 Map and format the volumes. All volumes will need to be mapped to the server and formatted in Windows. These volumes, along with required parameters, are listed in Table 4. Remaining volumes to map and format Volume name Drive letter/ mount point Volume label Allocation unit MPHost M:\ MPHost Default SQLSystem M:\ft\SQLSystem SQLSystem 64K SQLLog M:\ft\SQLLog SQLLog 64K SQLData01 M:\ft\SQLData01 SQLData01 64K SQLData02 M:\ft\SQ
Install and configure Microsoft Windows Server 2016 g. h. i. j. k. l. Right-click the unallocated space of the new disk and select New Simple Volume. The New Simple Volume Wizard starts. Click Next. On the Specify Volume Size screen, keep the default size, and click Next. On the Assign Drive Letter or Path screen, use the drive letter or mount point listed in Table 4 for the volume and click Next. On the Format Partition screen: i. In the File system drop-down list, select NTFS. ii.
Install and configure Microsoft SQL Server 2017 Enterprise Edition 7 Install and configure Microsoft SQL Server 2017 Enterprise Edition 7.1 Install Microsoft SQL Server 2017 Enterprise Edition To install SQL Server 2017 Enterprise Edition, perform the following steps: 1. 2. 3. 4. 5. 6. 7. 8. Insert the SQL Server 2017 Enterprise Edition install media. Double-click Setup.exe. The SQL Server Installation Center window will appear. Click Installation in the pane on the left-hand side.
Install and configure Microsoft SQL Server 2017 Enterprise Edition On the TempDB tab: a. In the Number of files list box, enter 8. b. Remove existing directories in the Data directories list box. Select the directory and click Remove. c. Add the directory M:\ft\TempdbData01\Data. Click Add. The Browse for Folder dialog box appears. Select the folder and click OK. d. Add the directory M:\ft\TempdbData02\Data. Click Add. The Browse for Folder dialog box appears. Select the folder and click OK. e.
Install and configure Microsoft SQL Server 2017 Enterprise Edition 7.3.3 Set SQL Server maximum memory Set the SQL Server maximum memory to 3040 GB: 1. Click New Query on the toolbar. 2. Enter the following T-SQL commands: EXECUTE sp_configure 'max server memory (MB)', '3112960' GO RECONFIGURE GO 3. Click Execute on the toolbar. 7.3.4 Set the max degree of parallelism (MAXDOP) During rowstore tests, MAXDOP=16 was used for I/O testing and MAXDOP=72 was used for CPU testing.
Install and configure Microsoft SQL Server 2017 Enterprise Edition 7.3.6 Expand tempdb files 1. Click New Query on the toolbar. 2.
Verify deployment 8 Verify deployment Use telnet from a client machine to verify that applications can successfully connect to the database server. Note: The telnet client must already be installed on the client machine. Perform the following steps on a client machine: 1. Open the command prompt. 2. Type telnet 1433, replacing with the IP address of the SQL Server machine, and press [Enter]. A blank screen will be displayed if the connection is successful. 3.
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. A.
