Scaling SQL Server 2022 VMs on Dell Integrated System for Microsoft Azure Stack HCI
Download PDFWed, 30 Aug 2023 21:20:09 -0000
|Read Time: 0 minutes
Summary
This document describes a scalable, high performing, cloud-ready solution for Microsoft SQL Server 2022 enabled by the Dell Integrated System for Microsoft Azure Stack HCI.
Market positioning
Microsoft presented SQL Server 2022 during the Microsoft Ignite 2022 event and introduced several key improvements for database operations, availability, security, and performance.
Dell Integrated System for Azure Stack HCI is a perfect choice for SQL Server 2022 requirements. It provides a fully productized platform offering intelligently designed configurations to minimize the need for hardware and software customizations.
Solution Architecture
Dell Integrated System for Microsoft Azure Stack HCI
For the SQL Server tests, four AX-7525 nodes were set up, equipped with AMD EPYC 7473X processors, 2 TB of RAM, and 12 x 1.6 TB NVMe disks per node. Between one and 12 SQL Server VMs were created and installed on Windows Server 2022 to test system performance and scalability. HammerDB, an open-source tool, was used to simulate a workload that performs transactions against the database. An Online Transactional Processing (OLTP) benchmark (TPROC-C) was used to measure system performance.
Figure 1. Solution architecture in detail
Table 1 lists the component level detail of each AX-7525 Azure Stack VxFlex hyperconverged node used to build the cluster for this solution.
Table 1. AX-7525 component detail
Component | Detail (per node) |
Processor | 2 x AMD EPYC 7473X |
Memory | 2 TB of Quad rank DDR-4 memory @ 3200 MT/s |
Physical disks | 12 x 1.6 TB Dell Enterprise NVMe CM6 MU Drives |
Management NIC | Broadcom Adv. Dual 25 Gb Ethernet Adapter |
RDMA NIC | NVIDIA Mellanox ConnectX-6 Dx Dual Port 100 GbE QSFP56 Adapter |
Azure Stack HCI operating system | Azure Stack HCI, version 22H2 (operating system build 20349) |
Storage architecture
The physical drives used in this solution were configured into a single pool. To access the pool, we created parity and mirrored Cluster Shared Volumes (CSVs) based on throughput and availability requirements. On the CSVs, we placed the Virtual Hard Disks (VHDs) which were connected to each VM across multiple SCSI controllers. The volume and controller layout for our disk pool is described in the following figure:
Figure 2. Solution storage layout
Network architecture
Designing and deploying an appropriate network architecture is key to obtaining optimal performance results in HCI scenarios as these networks facilitate intracluster communications and provide access to the storage layer. In this case we chose two Dell S5248F-ON switches as ToR switches, providing L2 multipath support through Virtual Link Trunking (VLT) and high-availability across the switches. The NVIDA Mellanox ConnectX-6 Dx Dual Port 100 GbE QSFP56 Adapters provide Remote Direct Memory Access (RDMA) with RDMA over Converged Ethernet (RoCE) capabilities for our storage network.
The global network architecture for this setup is described in the following figure:
Figure 3. Network architecture
Results
The methodology used to perform the SQL Server tests consisted of creating several SQL Server VMs (starting with one, and scaling to two, four, eight, and twelve). HammerDB is installed on the virtual machines, and the TPROC-C benchmark is used to assess the results.
The goal was to obtain as many linear performance results as possible, while maintaining a relatively low CPU utilization which left capacity to run other workloads in the test environment.
The results obtained both in Transactions Per Minute (TPM) and New Orders Per Minute (NOPM) can be viewed in the following figure:
Figure 4. Performance results for SQL Server 2022
More details on the tests, best practices, and recommendations can be found in the following references.
References
- Scaling SQL Server 2022 VMs on Dell Integrated System for Microsoft Azure Stack HCI
- Accelerate your SQL Server Workloads with Dell Integrated System for Azure Stack HCI