Microsoft SQL Server 2022 at the Edge with Lenovo ThinkAgile MX455 V3

In today's environment, information is a valuable asset that requires appropriate tools for storing and managing essential business data. Companies are being overwhelmed by the growing influx of information in their data centers. Placing MX455 V3 servers at remote edge locations can help distribute the data and processing demands. Consolidating SQL Server and other workloads within virtual machines on Azure Stack HCI clusters can improve processing efficiency. The MX455 V3 is edge-optimized and also ideal for AI and Telco workloads.

Lenovo Solutions for Microsoft SQL Server on ThinkAgile MX455 V3 are optimized for Online Transaction Processing (OLTP) such as TPC-C workloads. This technical brief features Microsoft SQL Server 2022 Enterprise Edition running in virtual machines on compact, high-performance Lenovo 2U rack or shelf mounted systems. The server is configured with 4th Generation AMD 8004 Siena processors, TruDDR5 4800MHz memory and NVMe SSD drives. The new processors from AMD support up to 64 cores and 6x 96GB memory DIMMs.

The MX455 V3 server is a storage dense offering for its size. It supports up to 8 2.5" drives including 4 front and 4 internal bays plus an additional 2 internal M.2 drives for the OS. The MX455 V3 servers include Azure Stack HCI pre-loaded and ready to setup in a cluster configuration.

Microsoft Azure Stack HCI (Hyper-Converged Infrastructure) is a hybrid cloud solution that integrates on-premises infrastructure with Azure cloud services. It enables businesses to run virtualized workloads on industry-standard hardware, combining the benefits of cloud computing with the control and customization of on-premises solutions. Azure Stack HCI supports a range of applications, including virtual desktops, databases, and high-performance workloads, while providing features like software-defined storage and networking, enhanced security, and simplified management through integration with Azure.

SQL Server 2022 includes updates to existing features like Intelligent Query Processing in addition to management, platform or language.

Starting with SQL 2022, runtimes for R, Python, and Java are no longer installed with SQL Setup. Instead, install any desired custom runtime(s) and packages.

Here are some performance enhancements in SQL Server 2022:

• Improvements have been made to all columnstore indexes that benefit from enhanced segment elimination by data type.
• Concurrent updates to global allocation map pages reduce page latch contention
• Improvements in buffer pool scan operations on large-memory systems by using multiple CPU cores for parallel scans
• Improvements to Clustered ColumnStore Indices to sort existing data in memory before index builder compresses the data
• Support for Intel QuickAssist Technology (QAT) backup compression with software or hardware acceleration (only software compression is available in SQL Standard)
• TempDB performance enhancements for scalability
• Shrink database uses low priority processing to minimize impact on performance
• In-memory OLTP enhancements

Here are some management improvements:

• Additional Azure integration
• Link to Azure SQL Managed Instance
• Accelerated Database Recovery (ADR)
• Always On Availability Group enhancements

Lenovo ThinkAgile MX455 V3 offerings are ideal for virtualizing your legacy SQL Server applications because of their low cost and high-performance capabilities. They are industry standard servers providing cost effective computing and fast high-density local storage at the edge. With it’s smaller foot print, the MX455 V3 is ideal for deployment in remote office and edge locations.

Each server configuration features the following main components:

• Server: Lenovo ThinkAgile MX455 V3
• Processor: 1x 4th Gen AMD EPYC (Siena) 8324P 32C 180W 2.65GHz
• Memory: 192GB of TRUDDR5 4800 MT/s memory
• Storage pool: 4x 7450 PRO 1.2TB NVMe SSDs
• OS Storage: 2x 7450 PRO 480GB M.2 NVMe SSDs (RAID 1)
• Software:
  • Azure Stack HCI OS
  • Microsoft Windows Server 2022 VMs
  • Microsoft SQL Server 2022 Standard Edition

For a high-performance SQL Server solution, implement the following best practices:

• Configure UEFI (BIOS) settings for Operating mode to Maximum performance.
• Enable Simultaneous Multithreading.
• Set networking MTU to 9000
• Avoid over-subscribing VM memory or processors beyond physical availability.
• Configure power profile in Windows Server VM to ‘High performance’.
• SQL server database and log drives are recommended to be formatted with 64KB NTFS cluster size.
• SQL server database and log files should be on separate physical drives.
• The OS and SQL server binary drives are recommended to be formatted with standard 4KB NTFS cluster size.
• TempDB is shared by many processes and users as a temporary working area and should be configured appropriately. Default configuration will be suitable for most workloads. Use the install experience for guided configuration. More info in Microsoft TempDB Database documentation.
• If the server is dedicated to the SQL Server workload, use the default dynamic memory management model or follow Microsoft SQL documentation guidelines for manually configuring memory options if finer grain control is desired.

Figure 1. Lenovo ThinkAgile MX455 V3

HammerDB and VMFleet

HammerDB is an open-source load testing / benchmarking tool for databases available at: http:///www.hammerdb.com. It offers tools for testing performance of OLTP and Analytics workloads. The OLTP workload is based on TPC-C benchmark from http://www.tpc.org. HammerDB version 4.7 was used.

VMFleet is a Microsoft tool that automates Diskspd tests on multiple VMs for validating HCI storage I/O. It reports total IOPs achieved.

Lenovo is a US$70 billion revenue Fortune Global 500 company serving customers in 180 markets around the world. Focused on a bold vision to deliver smarter technology for all, we are developing world-changing technologies that power (through devices and infrastructure) and empower (through solutions, services and software) millions of customers every day.

For More Information

To learn more about this Lenovo solution contact your Lenovo Business Partner or visit: https://www.lenovo.com/us/en/servers-storage/solutions/database/

References:

Lenovo ThinkAgile MX Edge servers: https://lenovopress.lenovo.com/ds0112

Microsoft SQL Server 2022: https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16

Azure Stack HCI: Link