Microsoft SQL Server 2012 Fast Track Reference Configuration using PowerEdge R720 and PowerVault MD3620i This whitepaper describes the Dell Microsoft SQL Server Fast Track reference architecture configuration and performance results Jisha J Dell Database Solutions Engineering
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i This document is for informational purposes only and may contain typographical errors and technical inaccuracies. The content is provided as is, without express or implied warranties of any kind. © 2012 Dell Inc. All rights reserved. Dell and its affiliates cannot be responsible for errors or omissions in typography or photography.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Contents Executive summary ..................................................................................................... 4 Introduction to Microsoft SQL Server Fast Track reference architectures ................................... 4 What’s new with Dell Microsoft Fast Track reference architectures? .........................................
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Executive summary Dell™ and Microsoft®, in cooperation, provide guidelines and design 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.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i to the traditional row-based approach. This technology enables better compression rates within the database, which is very beneficial for data warehouses because of the huge amount of data handled. Column store indexes also benefit common data warehousing queries such as filtering, aggregating, grouping, and star-join queries. (See msdn.microsoft.com/en-us/library/gg492088.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Single server reference architecture Figure 1 depicts the single server reference architecture. Table 2 lists the details of the configuration. Figure 1. Table 2. Single server reference architecture Single server reference architecture details Hardware Components Details Server PowerEdge R720 Processors 2* Intel Xeon Sandy Bridge CPUs E5-2643 @ 3.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Highly available reference architecture For achieving high availability for the database, we recommend using Microsoft Database Clustering. Using Microsoft clustering services, one database server is configured as the primary (active) server and the second server is configured as secondary (passive). The secondary server should have exactly the same configuration as the primary server.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Disks 48* 146G, 6Gbps 15k SAS drives Network Switch 2 * PowerConnect 8024 Operating System Windows 2008 R2 SP1 Enterprise Edition Clustering Software Microsoft Windows Clustering Database Software SQL Server 2012 Enterprise Edition Reference architecture configuration hardware component details This section describes the hardware details used for the specific reference configurations.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i PowerVault MD3620i iSCSI storage arrays PowerVault MD3620i storage arrays support 2.5 inch 6Gbps SAS disks, which enable better backend performance. MD3620i supports up to two controllers. Each controller has two 10GbE host side ports and one SAS out port, which is used to connect to any additional expansion enclosures.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Enable Jumbo Frames on all storage iSCSI host ports Jumbo frames need to be enabled on all four storage iSCSI host ports and the MTU size should be set to 9000 bytes/frame. This can be done using the Advanced Host Port Settings window (Figure 4), accessible from the Configure iSCSI host ports link of the PowerVault Modular Storage Manager (MDSM) Setup tab. Figure 4.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Figure 5. Enabling storage High Performance Tier(Turbo Mode) Set the Storage Cache Block Size to 32KB In this specific Fast Track configuration, Dell recommends setting the storage cache block size to 32KB for maximum array throughput. The Cache block size is set in the Change Cache Settings window (Figure 6). Figure 6.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Set the virtual disk segment size to 256KB For the recommended Fast Track configurations, Dell recommends setting the virtual disk segment size to 256KB. This benefits the large block sequential workloads. The segment size is set in the Customize Advanced Virtual Disk Parameters window (Figure 7). Figure 7.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i PowerConnect 8024 Switch Tuning For the reference configuration, Dell proposes to have Jumbo frames enabled on all participating iSCSI ports of the configuration. The MTU size of all the concerned ports should be set to 9216. Figure 9 shows how to enable jumbo frames for all the switch ports from the serial console window. Figure 9.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Storage system configuration The Fast Track Reference Architecture guidelines define three primary layers of storage configuration: • • • Physical disk array (RAID Groups for Data and Logs) Operating system volume assignment (LUN) Databases: User, System Temp, Log On each storage array, for the primary user and system temp database files: • • Four RAID 5 disk groups were created, each consisting of
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i For Fast Track architectures, Dell recommends using mount points for the storage access rather than using drive letters. It is also important to assign the appropriate LUN/volume and mount point names to the configuration in order to simplify troubleshooting and performance analysis.
Microsoft SQL Server 2012 Fast Track Reference Configurations using PowerEdge R720 and PowerVault MD3620i Reference architecture performance details Table 5 shows the performance numbers reported for the recommended reference configuration. Table 5.
