·
5 min read

Leveraging Flash across the Microsoft SQL Server Stack

Guest blog post by @SamMarraccini of EMC Corporation.

CPU to HDD Performance GapFlash technology is changing everything, nothing new there. Chances are the device you are reading this BLOG on contains some type of Flash memory. Within the datacenter, it has been well documented that the increasing performance capabilities of today’s CPUs have created an I/O performance gap. Processor speeds and network bandwidth have been able to keep pace with new enterprise application requirements. Spinning disk drives just can’t keep up with these new performance needs. Most performance trouble shooting today points to I/O resource requirements, or the lack thereof.

With Flash comes the promise of never needing to think about storage design; it doesn’t matter… no spinning media, no seek time, no rotational delay. It just works, right?

EMC_Flash_NextFlash does indeed work, but the solution isn’t quite that easy. While server side PCIe Flash can provide latency as low as 50 microseconds, you may be sacrificing database availability and service levels to gain that performance. While being able to achieve phenomenal response times, the focus of our solutions portfolio here at EMC remains consistent: Performance, Protection and Intelligence! EMCs Flash portfolio is designed to put application and database design criteria before hardware. With an “Xtrem” portfolio that includes Server Side PCIe Flash (XtremSF), Server Side caching (XtremSW Cache), Hybrid Storage arrays (VMAX, VNX), and an all new all Flash array (XtremIO)

EMC offers the right technology based on your applications and database needs. Whatever your needs, we have an Xtrem solution to match the performance and availability needs.

EMC changes the game via the first Hybrid Arrays (circa 2008)

EMC first introduced Flash technology into our enterprise storage arrays back in 2008. The hybrid array was born. Just as important now as it was then, the software designed to complement the Flash hardware. Software like “FAST” Fully Automated Storage Tiering, designed to move the most frequently referenced data to a tier of Flash drives inside the array, while lesser referenced data migrated to lower cost resources within the array. Microsoft SQL Server Datasets were none the wiser to the data movement happening within the array, just that IO response time improved significantly. More important with the hybrid solution, you still see the benefit of the enterprise storage platform (multi-site active/active hyper-v clustering for example).

EMC XtremSF (Server side PCIe Flash)

The introduction of PCIe Flash moves the storage resource as close to the CPU as possible, providing tremendous gains in IOPS (I/O per second) and reduced response time (measured in microseconds). The closer you can get to the CPU, the more performance gain you’ll see. EMCs XtremSF (Server Flash) does exactly that, moving IO as close to the CPU as possible. The EMC XtremSF 2200 can provide around 208,000 IOPS at 87 microsecond response time (70/30 read/write 4k block). WOW!!! The downside is its local storage and needs to be protected and treated as local storage.

Microsoft_SQL_Server_Transactions_Performance_Comparative_BenchmarkA whitepaper from the EMC Global Solutions Team “EMC XtremSF: Delivering Next Generation Storage Performance for Microsoft SQL Server” highlights the deployment considerations for local PCIe Flash storage. The raw performance gains over local storage are incredible. The local spinning disk was able to generate 1,920 transactions while the XtremSF Server Flash card produced 58,203 (over 300x increase). (On a side note, the bottleneck moved from IO to the CPU, so the 300x increase was limited based on the hardware tested).

Obviously, XtremSF provides superior application performance, but like any other PCIe Flash device, it’s DAS (Direct Attached Storage) and lacks the HA and enterprise features DAS brings. One option is to take advantage of Microsoft SQL Server AlwaysOn, integrated HA found in Microsoft SQL Server 2012. There are lots of variables to be considered before deploying such a solution: The volume of transaction logs to be shipped, the distance between the primary and secondary sites and the quality of the interconnect or log replication network between sites. A hybrid approach is possible by implementing XtremSF as local storage for the primary site while leveraging an enterprise storage array at the target. The enterprise array can then provide the secondary copy while cloning features can be used to create reporting instances and copies of the production data.

EMC XtremSW Cache (Cache Software)

XtremSW_SoftwareSo, you can have the Xtrem performance of XtremSF Server Flash (Local Storage), or the enterprise functionality, protection and performance of an EMC VMAX or VNX Hybrid storage array, right? Wrong, you can have BOTH!! How? EMC XtremSW Cache, host based intelligent caching software leveraging local Flash devices to accelerate performance while maintaining database protection in the underlying storage array. XtremSW Cache software caches the most frequently used data on the server-based Flash device, thereby putting the data closer to the application, reducing the need to access data across the storage network to the array, increasing performance and reducing latency. The XtremSW Cache software automatically adapts to changing workload by identifying which data is most frequently referenced and promoting it to the server Flash cache. The “Hottest” data is serviced from the local PCIe Flash device, all while writes are committed to your existing enterprise storage array. This write through cache provides the perfect solution for reporting instances of Microsoft SQL Server or Microsoft SharePoint BI implementation. A side effect of that performance gain is an increase in write transactions. In reality, the reads have been moved from the storage array to the host, allowing the array to spend cycles on writes. On a side note, EMC XtremSW Cache doesn’t require an EMC Storage array; of course, there is advanced integration via Storage Tiering, monitoring, reporting services and even phone home supportability. EMC XtremSW Cache can be used to move read operations as close to the CPU no matter the storage array.

EMC XtremSF & XtremSW Cache (Better Together)

XtremSF_and_XtremSW_Cache_Split_Card_ModeXtremSF Server Flash, the fastest anywhere. XtremSW Cache software, move the most frequently referenced data to a local Flash device (XtremSF or other). The flexibility of the EMC Xtrem Portfolio allows the use of BOTH!! Here’s an example… An XtremSF Server Flash card, say 2.2TB, installed in a Windows 2012 host and partitioned as two 1.1TB drives. The first partition, Microsoft SQL Server TempDB (Who doesn’t need 208,000 IOPS for Temp), the second configured as a read cache leveraging XtremSW Cache Software. Performance (for TempDB), protection (for .DBFs) and service levels that are based on the back end storage infrastructure. This provides a great option for clustered environments as well. The data volumes live on shared storage while temp space, no longer a cluster resource, receives the full benefit of local flash, Nice!

Need more options? Just wait for XtremIO! EMCs all Flash Array built from the ground up to support and exploit Flash technologies. That’s the subject for another blog. Until then, be sure to follow me on twitter @SamMarraccini for the latest in Flash developments here at EMC. Headed to EMC World or Microsoft Teched, look me up, and don’t forget, you’ll find everything you need to know about EMC and Microsoft at www.emc.com/everythingmicrosoft