Bring Enterprise-Class Flash Performance to SQL Server with MSA 2042 Entry-Level Storage

StorageExperts · ‎09-27-2016

A new Demartek report shows that the flash-accelerated HPE MSA 2042 powered by intelligent software can improve SQL Server business outcomes for SMBs affordably.

The relentless growth of flash storage

The market for all-flash storage arrays (AFAs) continues to grow explosively, with HPE reporting a 70% year-over-year 3PAR all-flash array (AFA) sales increase, for Q316¹. Flash storage is enjoying mainstream adoption in the large enterprise market, delivering benefits across multiple workload scenarios such as Online Transaction Processing (OLTP), Data Warehousing and VDI.

Here comes flash-accelerated storage for SMBs

We’re extending the benefits of flash-accelerated storage to small and midsized businesses with the introduction of the HPE MSA 2042 storage—with a “dash of flash” SSD storage coupled with software intelligence to economically deliver accelerated application performance and improved business outcomes. Read more about the new MSA—and what a little flash can do.

Now let’s explore how SQL Server database performance can be improved with this entry-level storage.

Why SQL Server 2016

Microsoft SQL Server has been at the heart of enterprise IT across its many generations, with a broad array of use cases spanning virtually all industry verticals. Gartner, in October 2015, assessed SQL Server as the industry leader for Operational Databases² and the National Institute of Standards and Technology public security board has recognized SQL Server as the industry’s least vulnerable database³.

Adding to these accolades, SQL Server 2016 delivers value with its industry leading TCO⁴, making it a natural choice for the purposes of this test evaluation effort on HPE’s value-priced storage system.

Demartek SQL Server test report: a tale of two value-added MSA 2042 options

Recently, Demartek evaluated SQL Server 2016 performance using the MSA 2042 Storage system with standard 800GB (two 400GB SSDs) of built-in flash storage in two performance enhancing software configurations, available and enabled, at no additional cost:

SSD read caching—The two embedded SSDs were deployed as Read Cache to improve random read performance.
Performance auto tiering (included in the Advanced Data Services Software Suite)—The MSA 2042 supports sub-LUN tiering and automated data movement between tiers. Three distinct tiers are supported with a performance tier with SSDs, a standard SAS tier with Enterprise SAS HDDs and an archive tier utilizing midline SAS HDDs.

The MSA 2042 automated tiering engine moves data between available tiers based on the access characteristics of that data. Frequently accessed “pages” will migrate to the highest available tier delivering maximum performance to the application (performance tiering).

Tiering provides benefits by using flash to accelerate write as well as read transactions.

Background and key results for the HPE MSA 2042 SQL Server Online Transaction Processing (OLTP) testing

The Demartek testing focused on measuring transactional aspects of storage performance for a real SQL Server 2016 OLTP workload, simulating a financial brokerage firm with customers who generated transactions related to trades, account inquiries and market research. With a focus on business outcomes, the key workload metrics measured included:

Database transactions per second
Latency improvement

In addition, traditional storage metrics of Throughput and Input Output Operations per second (IOPS) were also investigated.

Demartek reported the following results from its independent testing, shown in the two charts below:

Read caching

Database transactions per second : +35%
Latency reduction: 4.5x improvement

Performance tiering

Database transactions per second: +60%
Latency reduction: 11x improvement

Note: Demartek ran the benchmark for 24 hours to ensure a steady state for I/O and providing enough time for the SSD read cache to “warm” fully and for the tiering algorithm to migrate “hot” data to the flash SSD storage tier.

The takeaways

Results from both read caching as well as performance tiering configurations demonstrated that not only was more real work accomplished (database transactions per second) but the user experience for the SQL Server application was significantly improved (I/O latency) as I/O service requests were completed in a fourth to a tenth of the time.

Solution highlights for Demartek study

HPE MSA 2042 takes HPE’s flagship entry array (with nearly half-a-million units sold to date) and turbocharges it with 800GB of built-in SSD capacity plus an all-inclusive software suite that delivers enterprise capabilities, at a starting price under $10K. With flash acceleration, automated tiering and data protection features are now standard and price-reduced, making the MSA 2042 ideal if for SMB customers looking to accelerate SQL Server performance.
HPE SN1100E Gen 5 16Gb Fibre Channel adapter from Emulex delivers up to 1.2 million IOPS on a single port, and a published MTBF of 10 million hours for guaranteed reliability and up time. The SN1100E also supports the Virtual Fibre Channel (VFC) feature in Microsoft Windows Server for virtualized SQL Server deployments. (NOTE: The Demartek report utilized the older HPE SN1000E 16Gb Fibre Channel, now replaced by the HPE SN1100E.)
HPE SN6000B Fibre Channel switch (Brocade 6510), designed to enable greater flexibility and investment protection, scales from 24 to 48 ports and supports 4, 8, 10 or 16 Gb/s speeds in an efficiently designed 1U package. It offers low-cost access to SAN technology while providing "pay-as-you-grow" scalability to meet the needs of an evolving storage environment.

Why this is such good news for SMBs

As flash storage deployments continue to proliferate in large enterprises, the HPE MSA 2042 storage is a compelling and affordable solution for small and midsized businesses that offers built-in flash acceleration and enterprise data services right out of the box.

Now you can improve your SQL Server Database Performance with HPE MSA 2040 storage flash upgrades at entry-level cost.

Learn more on this topic

Demartek Report: Accelerating Database Workloads Using HPE MSA Storage with Built-In Flash
Blog: The New HPE MSA 2042: What a Little Flash Can Do
HPE MSA 2042 Storage data sheet
HP StoreFabric SN1100E 16Gb Dual Port Fibre Channel Host Bus Adapter
HPE SN6000B 16Gb 48-port/24-port Active Fibre Channel Switch

¹ HPE CEO Meg Witman on Q3 2016 Results
²Why Microsoft for your database management system
³ SQL Server 2016: The database for mission-critical intelligence
⁴ SQL Server 2016 is generally available today