By James Carpignano, Sr. Staff Apps Engineer
It’s been an exciting first few days at HPE Discover 2016 and I’ve had the opportunity to learn even more about the challenges facing IT today. I’ve truly enjoyed meeting so many new people and I’m thrilled to share my first post on the SanDisk® IT Blog about our new VMware All-Flash Virtual SAN solution that’s currently demoing at the HPE booth #329 – the industry’s first OLTP performance results for SQL Server 2016 database on Virtual SAN.
SQL Server® 2016 Business Critical Performance on VMware All-Flash Virtual SAN
We’ve been busy working with our partners and with customers on enabling VMware Virtual SAN (VSAN) solutions since the launch of the platform. I would like to share some exciting new results we’ve achieved working with one of our largest partners, Hewlett Packard Enterprise (HPE). I’m especially excited about this because I have never seen such a large NOPM number achieved for a transaction processing system using VMware All-Flash VSAN with virtualized Microsoft® SQL Server 2016. This is a solution for the new SQL Server platform that was released just a week ago!
For this solution, we worked with our partner HPE on enabling Virtual SAN for business critical applications. One of the workloads we chose to test was an online virtualized transaction processing system (OLTP) using SQL Server 2016 databases.
For our test, we configured a 4-node all-flash Virtual SAN using HPE DL360 G9 servers and Lightning® Gen. IISAS SSDs for both the cache tier and persistent data tier. We used 2 VMs in each node in a 4-node all-flash cluster and ran an OLTP workload simultaneously on each.
To generate transaction oriented processing, we used an automated in-house tool, Jarvis, as a wrapper for generating SQL Server transaction workloads using HammerDB. HammerDB is a database load-testing tool that allows the tester to create simulated workloads. There are many parameters to choose from depending on size, scale and transaction type and Jarvis makes this process easy for us.
For this testing, we created a 5,000 scale OLTP warehouse in each Virtual Machine (VM), simulating real-world online transactions. We ran 8 VMs in this 4-node all-flash VSAN, totaling a 40,000 OLTP size warehouse which equates to about 8TB of database records. The OLTP database workload is simulated by running 250 users simultaneously.
In simplified terms, in this test we simulated 250 end users doing a transaction (e.g. a purchase, checking their order status, looking for inventory, adding to cart etc.) in an 8TB database.
From a virtualization perspective, each VM was configured with 16 vCPU and 96GB of RAM and with 8 virtual disks storing ~1TB of data. The reason to choose 16 vCPU is in order to utilize a SQL server standard license which helps keep the cost of the solution significantly low.
Here are the results for the New Order Per Minute (NOPM) we achieved in our testing:
2,360,318 is a significantly large number on its own, and as VMware Virtual SAN supports up to a 64-node configuration, by extrapolating this 4-node test results linearly, we can achieve up to 38 Million NOPM. Amazing!
This configuration is currently undergoing testing and we will have additional details published in a white paper soon. The final numbers and metrics that will appear in the final document may change from the initial testing that I’ve shared here, but regardless, this is an incredibly fast system for running business critical applications and databases.
One of our next projects will be to test the OLTP workloads with VMware’s new VSAN de-dupe and compression features enabled on the cluster. These features will reduce the total capacity footprint and therefore reduce the cost of your investment.
Of course, every feature comes at a cost. In this case, I’m not talking about license costs, but rather a performance trade-off. So how much performance is sacrificed for these features, if at all, and what’s the best all-flash configuration? We hope to answer that question shortly. I’ll share the results of our testing on the blog – subscribe below to get our latest posts to your inbox and follow us on @SanDiskDataCtr to see live tweets from Discover 2016.