In this article

Microsoft has long since ended its extended support for SQL Server 2008 and 2008 R2. Gone are technical assistance, security updates and other critical lifelines. And yet, a sizable number of businesses continue to run some version of SQL Server 2008. They might not realize that by delaying a much-needed migration to SQL Server 2019, they're jeopardizing perhaps their most critical business asset: data.

If your organization is among those that are risking the consequences of security exploits, compliance issues and other vulnerabilities, it's time to get serious about planning for your next hardware upgrade. Yes, hardware, not just software — aging systems are ill-suited to provide the performance, security and cost-efficient operation needed to take full advantage of SQL Server 2019 capabilities.

But which hardware configuration works best at optimizing the performance of SQL Server 2019, while delivering the lowest cost and highest return on investment (ROI)? And how would variables such as socket count and persistent memory factor into the equation? 

Next-gen hardware brings to the table powerful new advantages that result in the whole being greater than the sum of its parts. Upgrades that adhere to status quos formed on yesterday's hardware can yield less than optimum outcomes, but dedicating the time and resources to assess and change the status quo is often a challenge.

To provide customers with the knowledge they need to make IT decisions with confidence, we called on the world-class facilities and expertise of the Advanced Technology Center (ATC) to proactively investigate and validate solutions for use cases such as SQL 2019 on 2- and 4-socket servers. For organizations looking to achieve business outcomes with long-term value, the results are well worth noting.

Creating a real-world server test environment in the ATC

In this series of tests, WWT measured how additional processors could increase the relative performance of SQL 2019 instances, all other factors being equal. The testing environments consisted of three configurations: a 2-socket server with conventional storage and memory; a 4-socket server with 2nd generation Intel® Xeon® processors, also with conventional storage and memory; and another 4-socket Intel-based server with half the memory replaced with Intel® Optane™ persistent memory

For those unfamiliar, Intel Optane persistent memory combines the most useful characteristics of memory and storage. It flexibly provides memory-bound workloads with access to high-performance, large-capacity memory. Virtual machines hosted the load generation component to guarantee a like-for-like workload on all three solutions.

SQL 2019 dedicated environment

When setting up any test environment in the ATC, we find far more value in representing a real-world scenario of what customers might actually deploy, rather than unrealistic situations designed to generate "hero numbers" for the purpose of deriving a more impressive result.

WWT canvassed its customers for actual workload examples to create an environment that reflects SQL 2019 operating in a typical customer data center. The objective was to simulate workloads that would be consistent with actual field conditions and measure what matters — in this case, transactions per minute, new orders per minute and ultimately cost per virtual machine (VM).

That last variable is important because any comparative ROI advantage cannot be judged by sticker price alone: a 4-socket server configuration inherently requires twice the number of CPUs and memory, increasing hardware costs over the 2-socket server configuration found in most data centers. Running the optimal amount of DRAM, Intel Optane persistent memory and Intel® Optane™ SSDs can further grow the hardware tab relative to 2-socket severs, but in return they also double the CPU and memory capacity for greater performance.

The question is, what impact will additional sockets, Intel Optane persistent memory and other technology advancements have on servers running SQL Server 2019? Is there a sufficiently compelling return on investment? And does the performance gain justify the dollars spent?

4-socket servers with Intel Optane PMem deliver optimum TCO and VM performance

testing stack configuration

We set up each of the three real-world testing environments identically, starting with a Dell MX7000 modular chassis with kinetic infrastructure; 2nd generation Intel® Xeon® Scalable processors designed for compute-intensive workloads; Dell PowerMax 2000 Storage incorporating Intel® Optane™ SSDs; and Dell PowerEdge MX740c and MX840c server blades. 

As previously mentioned, the first machine was a 2-socket configuration, while the other two were identical 4-socket servers. The first two servers were configured with 100 percent DRAM, while the third test server replaced 50 percent of the DRAM with 128 Gb Intel Optane persistent memory modules. 

Check out the ATC Insights write up for an in-depth technical discussion of these tests.

The 11+ tests sessions to date have tasked each server with running one, five, ten and the maximum number of VMs each server can manage. The results showed the most dramatic performance gain per virtual machine in the 4-socket server equipped with Intel Optane persistent memory:

  • The lowest per-VM cost — The 4-socket server with Intel Optane persistent memory saw nearly 38 percent lower per-VM cost than the 2-socket server and more than 18 percent lower per-VM cost than the 4-socket server with standard memory.
  • More VMs supported — The 4-socket server with Intel Optane persistent memory supported 36 percent more VMs when compared to the 4-socket server with DRAM only.
  • More transactions per minute completed — The 4-socket server with Intel Optane persistent memory completed 20 percent more transactions per minute when compared to the 4-socket server with DRAM only.
  • More new orders per minute completed — The 4-socket server with Intel Optane persistent memory completed 20 percent more new orders per minute when compared to the 4-socket server with DRAM only.
SQL 2019 Workload

The bottom-line takeaway from this three-way comparative test is clear and compelling: migrating isolated SQL workloads to a consolidated 4-socket server and adding Intel Optane persistent memory can significantly increase performance and return on investment per virtual machine, while also lowering total cost of ownership.

We can help implement a server solution that's ideal for database-intensive workloads on SQL Server 2019, allowing many more VMs per server while reducing capital expenditures and operating expenditures.

A move to SQL Server 2019 is in your future — make the best of it

It's past time to bid farewell to SQL 2008 and start deploying virtualized SQL 2019 Database workloads on the highly performant and cost-effective server configuration that best suits your data center demands, now and in the future.

It's a serious but necessary choice — especially when you consider the ever-increasing risk of continuing to run outdated SQL instances:

  • Diminished competitive capabilities as aging hardware and software hinder your ability to innovate, capture new opportunities and deliver rich customer experiences.
  • Security vulnerabilities that go unpatched, applications without updates and sophisticated cyber attacks all put critical data, assets and identities at risk.
  • Compliance failures from being unable to meet your customers' data protection standards: GDPR, HIPAA, PCI, SOX, US FISMA and others.
  • Escalating maintenance costs as legacy systems start to break down, requiring costly deskside visits, unplanned downtime and IT headaches.

WWT and Intel, along with Dell and Microsoft, have applied their collective capabilities to bring these SQL Server 2019 solutions to our Advanced Technology Center. If you're curious to see how they might work in your environment, we can arrange an on-site or virtual visit to the ATC so you can experience them first-hand or socially distanced. It's one more example of our continued focus on strategic data center modernization guided by results-driven testing and the value of the ATC.

Contact us today to get started.