The PowerEdge R740 server is designed to maximize compute density for enterprise applications and databases with a small 2U, 2-socket rack configuration. For this SQL Server with Unity testing, we used three identical PowerEdge R740 servers to provide a highly available VMware vSphere cluster that could sustain the loss of one server with minimal impact to the SQL Server database workloads. Our final testing configuration had six SQL Server VMs, as shown in the following figure, with two VMs running on each server.
Figure 5. PowerEdge R740 servers
We enabled vSphere HA so that two VMs could automatically restart on the surviving servers if a single server failed. A VMware administrator using vSphere vMotion can also proactively migrate the VMs on a server when a server requires patching or maintenance. During a vMotion migration, users experience only a temporary and minor impact on database performance.
We configured each VM with 24 vCPUs and 192 GB of memory. VMware recommends that the allocation of vCPUs not exceed physical CPUs for production workloads. In this implementation, we assigned 24 vCPUs per VM, which is less than half the number of 112 logical cores (hyper-threading was enabled) that are available in each server. We were well under the guideline of not exceeding the number of physical cores, thus preventing a possible processor performance bottleneck.
Each VM also had 192 GB of reserved memory. SQL Server and other databases use memory efficiently and are dependent on memory for optimal performance. Using a memory reservation prevents memory contention or pressure that might affect database performance. In this case, we reserved a total of 384 GB of vMem across two VMs. The physical server memory was 768 GB, leaving 384 GB (768 – 384) free memory headroom on the server.
Dell EMC PowerPath/VE Multipathing software integrates with the VMware ESXi hypervisor to intelligently manage data paths between servers and storage. PowerPath/VE software has patented algorithms that provide improved performance under ever-increasing workloads. Because our use-case approach was to incrementally increase the SQL Server workload on the solution infrastructure, we used PowerPath/VE software to optimize path management.
The Unity 650F storage array is Dell EMC’s fastest midrange storage system. Unity All Flash systems can provide the high-throughput, low-latency IOPS that consolidated SQL Server database environments need. Our Unity test configuration used an extreme performance layer of 116 x 400 GB SAS flash drives to deliver high performance and low latency. All-flash storage is ideal for mixed OLTP and decision-support system consolidation. SSDs provide low-latency performance for small-block random reads and writes as well as larger-block table and range scans.
Each VM and SQL Server database used the same storage configuration. For the operating system and VM files, we created a 200 GB VMFS datastore. For each SQL Server database, we created RDM files for the following storage:
Adhering to a best practice, we separated files into different LUNs for performance analysis, as shown in the following figure. Having separate LUNs for data, log, and tempdb files enabled us to monitor throughput and latency for each type of I/O profile. Having separate LUNs also provided the capability to report on storage performance at a granular level.
Figure 6. Unity 650F all-flash storage array with LUN mapping
Using Quest Benchmark Factory, we configured workloads to simulate a small to medium environment. The Unity array can scale to support large SQL Server workloads, and the DBAs can use multiple data LUNs to reduce the potential for I/O queuing managed by Windows for each device. Although having multiple data LUNs does not increase performance, it does enable I/O distribution across more devices and paths to the storage array so that performance bottlenecks are less likely to develop. Dell EMC has SQL Server specialists and consulting services that can help customers design a Unity storage configuration that is based on workload analysis to optimize manageability and performance.
The Unity 650F array simplifies storage design and enables fast provisioning of storage for SQL Server databases. The all-flash array offers the performance that is needed for small- and medium-size businesses to consolidate their SQL Server database ecosystem. Key Unity features that apply to SQL Server and other databases include:
In validating and testing SQL Server 2017 on this Ready Solution with Unity storage, we demonstrate how well the infrastructure can support your databases.