Deploying the 65TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R640 and Dell EMC PowerVault ME4024 Deployment guide with step-by-step instructions Abstract Step-by-step instructions for building a Microsoft® SQL Server® data warehouse workload, and requirements for preparing the hardware platform and provisioning the OS to achieve a balanced, optimized 65TB configuration.
Revisions Revisions Date Description September 2018 Initial release 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. Use, copying, and distribution of any software described in this publication requires an applicable software license.
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, 65TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R640 and Dell EMC PowerVault ME4024 (available on Dell.com/support).
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 R640 Processors Dual Intel® Xeon® Gold 6126 Processor (2.
Solution requirements 6 Hardware component Firmware version Intel® Gigabit 4P X710/l350 rNDC 18.5.7 Lifecycle Controller 3.21.21.21 OS Collector 3.0 Power supply 00.24.7D QLogic QLE2662 14.04.09 System CPLD 1.0.
Deployment workflow 2 Deployment workflow This section outlines the sequence of deploying the 65TB Data Warehouse Fast Track for SQL Server 2017 reference architecture using the PowerEdge R640 server and PowerVault ME4024 storage array. To deploy the reference architecture, perform the following tasks: 1. 2. 3. 4. Configure the PowerVault ME4024 storage array. Configure the PowerEdge R640 server. Install and configure the Windows Server 2016 operating system.
Cabling requirements 3 Cabling requirements Figure 2 shows how to cable the PowerEdge R640 server and the PowerVault ME4024 storage array. 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 2).
Configure the PowerVault ME4024 storage array 4 Configure the PowerVault ME4024 storage array This section describes the configuration of the PowerVault ME4024 storage array. ME4 Series arrays can be configured using the ME Storage Manager interface or ME4 Series command-line interface (CLI). This guide covers the configuration steps using ME Storage Manager. 4.1 Configure local ports On the Ports tab > System Settings, the Host Port Mode should be either FC or FC-and-iSCSI.
Configure the PowerVault ME4024 storage array 8. Using the Add Row button, create the volumes listed in Table 3. The volume sizes will depend upon your database configuration.
Configure the PowerEdge R640 server 5 Configure the PowerEdge R640 server 5.1 Update the firmware The firmware update feature of the Dell 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 the PowerEdge R640 server 5.3 Reset the HBAs to default settings To reset the HBAs using QLogic Fast!UTIL, which is accessible during the server boot cycle, 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. In Fast!UTIL Options, select Configuration Settings and press [Enter]. b.
Install and configure Windows Server 2016 6 Install and configure Windows Server 2016 6.1 Install 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 PowerEdge R640 server. To install Windows using the Lifecycle Controller, perform the following steps: 1. 2. 3. 4. 5. 6. 7. 8.
Install and configure 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 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 Windows Server 2016 6.2.9 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.10 Configure Windows Updates (optional) 1. 2. 3. 4. 5. 6. 6.2.11 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 Windows Server 2016 6.2.13 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. 5.
Install and configure Windows Server 2016 Execute the following steps for each volume, one at time, in order as the volumes are listed in Table 4: 1. Open ME Storage Manager and connect to the ME4 Series array. 2. Click the Storage tab. 3. Map the volume in the ME Storage Manager: a. Expand the volume folder containing the volume. b. Right-click the volume and select Map Volume to Server. The Map Volume to Server dialog box appears. c. Expand the server folder containing the server object. d.
Install and configure SQL Server 2017 Enterprise Edition 7 Install and configure SQL Server 2017 Enterprise Edition 7.1 Install 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 appears. Click Installation in the pane on the left-hand side.
Install and configure SQL Server 2017 Enterprise Edition 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\SQLTempdb01\Data. Click Add. The Browse for Folder dialog box appears. Select the folder and click OK. d. Add the directory M:\ft\SQLTempdb02\Data. Click Add. The Browse for Folder dialog box appears. Select the folder and click OK. e. In the Log directory list box, enter M:\ft\
Install and configure SQL Server 2017 Enterprise Edition 7.3.3 Set SQL Server maximum memory Set the SQL Server maximum memory to 864GB: 1. Click New Query on the toolbar. 2. Enter the following T-SQL commands: EXECUTE sp_configure 'max server memory (MB)', '884736' GO RECONFIGURE GO 3. Click !Execute on the toolbar. 7.3.4 Configure the resource governor 1. Click New Query on the toolbar. 2.
Install and configure SQL Server 2017 Enterprise Edition 7.3.5 Expand tempdb files 1. Click New Query on the toolbar. 2.
Verify the deployment 8 Verify the 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 Solutions Technical Documents provide expertise that helps to ensure customer success on Dell EMC storage platforms. A.
