The Professional Association for SQL Server (PASS) Summit 2016 starts the week of October 24, and our Data Propulsion Lab (DPL) is ready to rock it! Or should I say rocket? Yes, indeed—the DPL ha an HA/DR solution that leverages the latest-and-greatest improvements brought to us by SQL Server 2016 Always On Availability Groups (AGs). This three-replica system is fast!
Let me share with you some of our testing results and takeaways.
The Stars of the Show
The Western Digital portfolio of SanDisk® and HGST® flash solutions is huge. For this particular testing we used HPE ProLiant DL380 Gen9 Servers slinging IOPS via SanDisk Lightning Ascend™ Gen II with firmware optimized specifically for HPE P440 Smart Array I/O controllers.
The HPE ProLiant DL380 Gen9 Server is capable of supporting up to twenty-four 2.5” HPE-branded SanDisk SSDs. But in our tests we used only ten 800GB SSDs. This is just one of the many flexible aspects of this architecture and the fantastic features this system provides. For our testing we focused on four facets:
- Consistent ultra-high performance
- HA/DR
- Affordability
- Flexibility
There’s much to excited about regarding this solution, and we have a white paper covering each of these aspects. If you’re at PASS Summit, you’ll see that our key focus there will be on the performance solution enables.
Ultra-High Performance
Our test environment was designed to emulate a typical enterprise HA/DR scenario. This consisted of an AG comprised of three replicas (one primary and two secondaries) in Synchronous Commit Availability Mode.
By bringing together the improvements made to SQL Server 2016 Always On AGs, HPE ProLiant DL380 Gen9 Servers, SanDisk Lightning SSDs, and our DPL team’s configuration and tuning expertise, the system generated over 70K transactions/sec!
It did so by generating 1GB/sec of total I/O throughput, flushing over 200MB/sec of log from the primary replica database, and sending over 400MB/sec across the wire to the secondary databases. Owing to the improvements to the AG Log Transport, the data is hardened on the secondary replica logs in real-time, optimizing RPO.
Flash is required to consume this much data, this quickly. Not only does SanDisk brand flash do the job, it does so consistently in sub-millisecond latency—that includes 50K – 80K mixed, random IOPs even during checkpoint as well as the sequential I/O consumed by log files on both primary and secondaries.
Think again about this: Generating over 4 million data transactions per minute, generating 1GB/sec of I/O throughput, and consistently consuming 200MB/sec of log not in milliseconds, but at microsecond latency. The opportunities and possibilities this enables are truly game changing.
Performance Monitoring
Let’s drill down a bit by reviewing the Perfmon images below. Both display the very same data. However, on the left, we’ve kept the default scale for latency of 1.0, which has for decades been Perfmon’s default setting. The values for both read, write, and total latency at this scale are zero. Is Perfmon broken? No. Simply change the counter scale from 1.0 to 10,000 as we’ve done on the right; doing so provides the resolution necessary to expose the actual latency curve. In this image I’ve highlighted the data volume. Avg. Disk sec/Transfer is still reported as 0.000, and the highlighted line reveals why–the actual latency here is actually ~400µs, which Perfmon rounds down to zero.
At the DPL we’re used to amazing perf, yet this astonished even us!
HA/DR, Affordability, and Flexibility
System performance is impressive, yet the system offers much more. Its incredible cost savings and simple deployment features make it extremely compelling as an enterprise solution.
This system takes full advantage of the fantastic SQL Server 2016 AG Log Transport improvements. By leveraging the design characterized here you can maximize ROI, including saving on SQL Server licensing dollars. The in-server flash capabilities eliminate the need for a SAN infrastructure, so that the costs of footprint, proprietary hardware, licensing, and a dedicated skill set are no longer required.
Lastly, the easy-to-implement configuration offers generous resource bandwidth, particularly CPU and I/O. It provides flexible deployment options, including populating both sockets or just one, modifying I/O controller or SSD count (up to 24), capacity plays (up to 35TB raw), consolidation opportunities, and more.
Taking Advantage of SQL Server 2016 Always On AGs
The powerful updates made to SQL Server 2016 AGs can be maximized by deploying state-of-the-art (yet affordable) HPE hardware powered by high-performance and ultra-low-latency SanDisk flash storage. Performance and HA/DR service levels can be met that were previously simply not possible. And best yet, you can achieve this in a straightforward, easy-to-implement, and affordable configuration.
There’s a lot to be excited about.
Learn More
See for yourself how HPE, SQL Server, and the Western Digital SanDisk brand flip the /faster bit, unleashing the power of flash! Visit the SanDisk booth #222 at SQL PASS in the Exhibitor Hall, where I’ll be presenting regularly and we’ll have a SQL Server 2016 Always On AG demo station.
