Blog: Reducing operating costs by upgrading SQL Server 2008 workloads using DataON SQL Server 2017 solutions


What are your options when the end of support for SQL Server 2008 R2 occurs in July 2019, and moving to the cloud is not an immediate option? In a joint benchmarking project between DataON, Western Digital, and DB Best Technologies, we demonstrated how you can reduce your operating costs for upgrading SQL Server 2008 workloads using DataON SQL Server 2017 solutions.

DataON S2D-5224w Server

DataON S2D-5224w server – 2U 24-bay 2.5″ All-NVMe Hyper-Converged Cluster Appliance

DataON SQL Server 2017 solutions feature Windows Server Hyper-Converged Infrastructure (HCI) solutions using Storage Spaces Direct. While this sounds a bit scary and expensive, we showed how you could run up to 80 VMs at 3.6K transactions per second per VM. Based on the total cost of the hardware and a five-year straight-line depreciation schedule,  the cost per VM came to $180 per month.

 

How to save costs

By virtualizing your multiple workloads on a Hyper-Converged Infrastructure (HCI) Platform, you can reduce your overall licensing costs by reducing the IO bottleneck using NVMe SSD drives to make the use of CPU and memory more efficient for OLTP workloads.

The HCI platform is built on Storage Spaces Direct, a new feature in Windows Server 2016. It uses servers with locally-attached drives (such as the DataON SQL 2017 Server Solutions) to create highly available, highly scalable software-defined storage at a fraction of the cost of traditional SAN or NAS arrays.

Using Windows Server Datacenter Edition with Storage Spaces Direct to create an HCI storage and compute solution.

  • Storage Spaces Direct utilizes NVMe storage devices for better performance and efficiency. These devices greatly reduce the I/O latency for storage, as well as You can also use SATA storage devices to lower the cost of storage.
  • In a DataON hyper-converged system, storage uses RDMA over Converged Ethernet (RoCE) as the storage fabric instead of having a shared physical storage fabric behind the servers which reduce the CPU utilization to server storage.
  • Storage Spaces Direct is included in Windows Server 2016 Datacenter edition.

The end result is being able to use fewer resources than the original system to support greater VM density using the DataOn HCI solutions for Microsoft SQL Server.

Raw OLTP power with DataON SQL Server 2017 solutions

DataON’s SQL Server solutions, with two- and four-node HCI clusters, are capable of achieving 10 million transactions per minute with automatic failover when the entire system is dedicated to running SQL Server.

We wanted to test a hybrid scenario where the application and SQL Server instance are running on the same VM.

In our case, the application was represented by the open-source Hammer DB application running the TPC-C benchmark at full-speed.

So, how many VM running an application with SQL Server can you move to the DataON Hyper-Converged Infrastructure Platform?

The answer depends on the size of your databases, as well as the expected transactions per minute rate.

Benchmarking SQL Server with DataON

To answer this question, we ran a series of benchmarks on the DataOn “entry-level” solution using a four-server cluster.

Each server had four Ultrastar® SN200 1.92TB NVMe U.2 2.5″ SSD drives which were all in a single Windows Storage Spaces Direct storage pool using the hyper-converged deployment option with triple storage replication for maximum availability.

Because, our VM used 4 virtual cores, 10 GB of RAM, and 115 GB of storage for the 60 GB TPC-C database, we were limited to a total of 52 VMs based on the storage available.

The end result was that we showed that you could run each of the 52 VMs at 5.7K transactions per second. This was almost twice as fast as our goal of 3.0K transactions per second based on similar tests on hardware from 2010 with SQL Server 2008!

Transactions (TXN) per second results for four NVMe drives per node with DataON SQL Server 2017 solution

In addition, with 294K total transactions per second still did not max out the system.

We need more storage!

By adding two more NVMe drives per server for a total of six, we were able to support up to 80 VMs. This time we were limited by the total of 160 physical cores across the four nodes. Even so, we were able to achieve 3.3K transactions per second per VM for each of the 80 VMs.

Transactions (TXN) per second results for six NVMe drives per node with DataON SQL Server 2017 solution

What this means is that by increasing the overall system price by 26% ($17.6K for 8 additional drives and $208.2K for software licenses for 28 additional VMs), we were able to improve workload density by 35%.

Customer benefits upgrading SQL Server 2008 workloads using DataOn SQL Server 2017 solutions

DB Best, Western Digital, and DataOn proved that DataON’s SQL Server solution to be a cost-effective, reliable and powerful host for virtualized workloads running OLTP workloads with SQL Server 2017.

If you are looking for a way to upgrade your SQL Server 2008 R2 database servers within your data center, please contact us today to learn how you can benefit from DataON’s Windows Server Hyper-Converged Infrastructure Solutions for Microsoft SQL Server 2017.

Be sure to check out DataON’s announcement of this great solution at Microsoft Ignite 2018 at http://www.dataonstorage.com/news/dataon-announces-windows-server-hyper-converged-infrastructure-solutions-for-microsoft-sql-server-2017/.

You can also read the full white paper at http://doc.dataonstorage.com/solution-briefs-whitepaper/Cost_Effective_Workload_Consolidation_using_the_DataON_HCI_Platform_Sept_2018.pdf.