How to configure DWFT for Microsoft SQL Server with Supermicro servers

We will configure a Data Warehouse Fast Track (DWFT) for Microsoft SQL Server using Supermicro's latest technology , leveraging their latest innovations.

Supermicro has remained at the forefront with its latest-generation servers supporting the most advanced processors, including the latest 4th and 5th Gen Intel Xeon Scalable (formerly known as Sapphire Rapids and Emerald Rapids, respectively) and 4th Gen AMD EPYC (Genoa and Bergamo) . These new processors offer significantly improved performance in terms of cores, threads, power efficiency, and, crucially for DW, increased PCIe Gen5 lane counts and support for DDR5 memory.

Fast Track Data Warehouse Configuration with Next-Generation Supermicro Technology

For a modern DWFT solution, we'll focus on the Supermicro GrandTwin or Supermicro Ultra series, which offer high NVMe density and support for the latest CPU platforms.

Key Components and Updated Technologies

• Next-Generation Supermicro Servers:
• GrandTwin (Ex: AS -2126TR-HNCR / SYS-2126TR-HNCR): Ideal for high density in a small space, with support for up to 24 NVMe E1.S or U.2 per node, and 4th Generation AMD EPYC processors.
• Ultra (Eg: SYS-221U-TNR / SYS-221U-TNR4T): 2U servers that offer an optimal balance between computing, memory and storage, with support for 4th/5th Generation Intel Xeon Scalable processors and numerous NVMe U.2 bays.
• Processors:
• 4th or 5th Gen Intel Xeon Scalable: With higher core counts (up to 60+ per CPU), larger L3 caches, and optimizations for AI and analytics workloads. Intel AMX (Advanced Matrix Extensions) technology can be beneficial for certain analytics workloads.
• 4th Gen AMD EPYC (Genoa/Bergamo): Offers even higher core density (up to 128 cores per CPU) and a large number of PCIe Gen5 lanes, which is great for connecting multiple high-performance NVMe drives.
• Memory (RAM):
• DDR5: Essential for the latest platforms. DDR5 offers higher bandwidth and lower power consumption than DDR4, which is vital for memory-intensive workloads like data warehouses. High capacity (e.g., 1TB or more per server) and the highest available speed (e.g., 4800MHz or 5600MHz) are ideal.
• Storage:
• NVMe PCIe Gen5 SSDs: The biggest improvement. Gen 5 NVMe SSDs deliver twice the performance of Gen 4, resulting in dramatically reduced I/O latency and unprecedented sequential and random read/write speeds. This is critical for SQL Server DWFT performance.
• NVMe U.2 or E1.S/E3.S: U.2 form factors are common, but EDSFF form factors (E1.S, E3.S) are gaining traction for their density and thermal efficiency in next-generation servers.
• NVMe over Fabric (NVMe-oF): For distributed DW scenarios or when a very low latency shared storage layer is required, NVMe-oF can be an advanced option to connect NVMe storage directly over the network (Ethernet or InfiniBand) with latencies close to local NVMe.
• Network Connectivity:
• 25GbE, 100GbE, or 200GbE: High-speed network cards are essential to ensure data can flow quickly between the server and clients or data sources. Latest-generation NICs from Mellanox (NVIDIA Networking) or Intel are ideal.
• PCIe Gen5: Network cards should also take advantage of PCIe Gen5 lanes for maximum performance.

---

Example of Optimized DWFT Configuration (Estimated for 50TB - 100TB of user data)

Based on a Supermicro Ultra SuperServer 2U with support for 4th/5th Gen Intel Xeon Scalable or a GrandTwin with 4th Gen AMD EPYC, the configuration could be:

• Server: Supermicro SYS-221U-TNR (for Intel) or AS-2126TR-HNCR (for AMD)
• Both systems are designed for high NVMe density and support for the latest processors.
• CPU:
• Intel: 2x Intel Xeon Platinum 84xx/85xx (e.g. 8592+ or 8490H) with high core count (50+ cores per CPU).
• AMD: 2x AMD EPYC 9654 (96 cores) or 9754 (128 cores) for extreme core density.
• Memory:
• 1.5TB - 2TB DDR5 ECC RDIMM: (e.g. 24x 64GB or 12x 128GB DDR5-5600MHz). The high capacity and speed of DDR5 will maximize the performance of SQL Server.
• Storage:
• User Data:
• 16-24x NVMe PCIe Gen5 U.2 SSDs from 3.2TB to 6.4TB (e.g., Micron 7450 PRO, Solidigm P5810, Samsung PM1743). Ideally configured in a single storage pool or clustered by SQL Server (Storage Spaces Direct if used in a cluster). Prioritize latency and IOPS.
• This would provide between 50TB and 150TB of ultra-fast NVMe storage for data.
• Transaction Logs:
• 4x NVMe PCIe Gen5 U.2 SSDs from 800GB to 1.6TB (high write endurance models) in RAID 10 (managed by software or hardware if the controller allows it and does not add latency).
• Operating System:
• 2x NVMe PCIe Gen5 M.2 SSDs 960GB - 1.92TB in RAID 1 (directly on the motherboard if M.2 PCIe Gen5 slots are present).
• Storage Controller:
• Typically, NVMe drives connect directly to the CPU's PCIe lanes. For any remaining SATA/SAS (less common in modern DWFT), a PCIe Gen5 RAID controller/HBA would be used.
• Network Connectivity:
• 2x 100GbE or 200GbE PCIe Gen5 Network Cards (e.g., NVIDIA ConnectX-7). Required for high network performance, especially when integrating with external ETL systems or clusters.
• Operating System:
• Windows Server 2022 Datacenter Edition: Optimized for modern workloads and supports the latest SQL Server features.
• Database:
• Microsoft SQL Server 2022 Enterprise Edition: Essential for DWFT, Columnstore Indexes, and other high-performance features.

Advanced Considerations for Next Generation

• Liquid Cooling: High-end processors can generate a lot of heat. Supermicro offers direct-to-chip liquid cooling solutions on some of its servers, which can be key to maintaining sustained performance under heavy loads and reducing the data center's PUE.
• Remote Management (OOBM): The latest versions of IPMI/Redfish on Supermicro servers offer robust remote management for monitoring, configuration, and diagnostics.
• Security: Integration with hardware-level security features (e.g., Secure Boot, Root of Trust) into the server platform.

---

To obtain a precise configuration and quote for the latest generation of Supermicro servers tailored to the specific needs of your Fast Track Data Warehouse in Chile, I highly recommend contacting Super Latam . As the largest authorized distributor in the country, they have the experience and direct access to the latest Supermicro technology to help you design the perfect solution.