Applications based on Microsoft SQL (MS-SQL) seem like obvious candidates for some type of flash performance boost. The high IOPS and low latency of flash storage is almost a perfect match for the high transaction MS-SQL environments. While almost any addition of flash storage to that environment will improve performance, it is important to have a MS-SQL flash strategy so that the flash investment is both efficient and optimally used. This includes making sure the environment is optimized for flash and that the right type of flash (server side, hybrid array or all-flash array) is selected.
Pre-Flash Conditioning
Prior to implementing a flash solution, it is important to make sure the environment is ready for it so maximum value can be extracted from the flash investment. The first step, since MS-SQL runs on Windows, is to make sure the volume that the instance resides on is defragmented. Although often a forgotten concern fragmentation still happens, no matter if the MS-SQL server is virtualized or resides on an array. Tools exist today to make sure that data is always written in a contiguous pattern to storage. This ensures that no IOPS are wasted reassembling fragmented data.
It is also worth spending some time to make sure that the database design is optimized. For example storage and SQL administrators should work together to make sure the queries being run against the database are still valid. Many times orphaned queries can be quickly identified and eliminated. Second, indexes should be looked at and it should be confirmed that the right columns are indexed. Finally, DBAs may want to look at the database code itself to see if it can be optimized.
The above steps require time and effort and in some cases a skill set that the organization may not have. The value of all-flash and hybrid flash arrays is that they can overcome the residual impact of non-optimal designs. It then becomes a business decision on the part of IT as to whether or not they address the design issues prior to implementing flash, or if they simply move on with their flash strategy.
Selecting the Right Flash Hardware
Once some of the above optimization steps are taken, its time to decide which flash storage type should be used. There are essentially three choices to consider; server-side flash, shared hybrid flash, and shared all-flash.
Server Side Flash
Server side flash is often considered the quick fix for database performance problems because it is not very strategic. It does have the advantage of location; there is no concern over the quality of the storage network. But this location can be problematic since leveraging that flash storage will likely require the use of a third party caching software application, which vary widely in scope and capability. Some blindly cache all the data on the MS-SQL server regardless of type, others are file specific and can be tuned to cache specific SQL components but may miss important operating system files.
Another concern is write acceleration. If the decision is made to cache both reads and writes for maximum performance, then some high availability function has to be considered in the event of a flash failure while it is storing uncommitted data. This availability should include protection from server failure in addition to flash failure. The complexities in maintaining a highly available write cache can make server side caching solutions more expensive than they initially appeared and cause them to lose most of their “local” appeal, since cached write data should be redundantly stored externally to protect from server failure.
In the end the biggest concern with server side flash is scale. Implementing it across multiple MS-SQL servers, especially if those servers are clustered, is difficult to manage. Also as the number of servers using server side flash increases the cost advantage that internal flash may have enjoyed is lost since flash resources are not used efficiently. It becomes the classic direct attached storage problem where some MS-SQL servers are using all of their flash capacity and some are not using any of it.
Shared Hybrid Flash
The next option is shared hybrid flash arrays. These arrays leverage a flash storage tier to deliver high performance, and a hard disk tier to deliver cost effective capacity. These arrays have a built-in caching algorithm to automatically move data between the two tiers depending on frequency of access and user preference. Unlike server side systems, a hybrid array has all the redundancy needed to safely cache new writes to the flash tier until it can be copied to the hard disk tier.
The user preference capability is important, especially in the MS-SQL use case, as it allows the storage administrator to discreetly lock certain files or volumes in flash. For MS-SQL, a volume can be created that stores high access items, like indexes and log files, in flash 100% the time and then allow the caching software to automatically cache the most active portions of the main database itself. The rest of the data along with any other environments that the system may be supporting are stored on the more cost effective hard disk tier.
The discreet control over flash utilization allows a hybrid system, especially one focused on MS-SQL, to require a very small flash tier, which keeps the overall cost of the system low.
There are two downsides to hybrid arrays to keep in mind. The first is that all this specific tuning takes time to set up and it requires continual monitoring of flash utilization to make sure that as the environment evolves these settings continue to deliver the right level of performance. The more frequently the MS-SQL environment changes or new applications are added the more difficult it becomes to maintain these performance adjustments.
The other downside is when, despite all the above effort, there is a cache miss and data is accessed at hard drive speeds. While there are varying degrees of cache misses, repeated misses could lead to a noticeable performance impact that causes users or customers to complain. The sensitivity there is to a cache miss as well as the level of granular control over flash utilization will be the key drivers to the decision.
Shared All-Flash
The final choice is the all-flash array. As the name implies, there are no hard drives in these systems, all data is stored on flash drives and receives the same high performance. All-flash arrays essentially eliminate storage tuning, and their high performance and low latency allow for much more dense database designs. Performance is consistent, as there is no risk of a cache or tier miss. There is, of course, the cost concern since there is no hard drive tier to offset the investment in flash.
Deciding on all-flash as a MS-SQL storage strategy comes down to price. Storage Switzerland’s guidance is that if that if the all-flash array can meet the capacity demands of the environment (or environments) that it is being deployed in for less than the projected budget, the organization should make the leap to all-flash. The gains brought about by consistent high performance like highly dense MS-SQL infrastructures, consolidation of other environments and potential elimination of storage performance tuning can bring incredible value for the organizations.
On the other hand if enough flash capacity cannot be bought for the given budget then a hybrid array needs to be considered. With proper tuning and adjusting of flash utilization, near all-flash performance can be realized most of the time. There is also the potential for a hybrid system to consolidate even more workloads since they do not have the capacity concerns that an all-flash array would.
Data Efficiency
Any strategy for all-flash or hybrid flash arrays needs to include a discussion on data efficiency. This is especially true when discussing database environments like MS-SQL. Most all-flash and many hybrid arrays include either compression and/or deduplication. Compression is essentially the elimination of redundancy of data within a file, where deduplication is the elimination of redundancy across files. For most databases, MS-SQL included, deduplication only provides a very small increase in efficiency. Compression, in the database use case, is very valuable, typically delivering 2:1 or greater efficiencies.
Given the cost of flash, compression should be considered a “must have” when deploying MS-SQL on shared flash arrays. Deduplication becomes more of a “must have” when other workloads are deployed on the shared arrays along with MS-SQL. Since consolidation is likely to be greater with hybrid arrays, it is important to look for a hybrid array that not only performs deduplication but also compression for both the flash and hard disk tiers.
Conclusion
Flash should be considered a “go to” storage choice for MS-SQL environments and almost any implementation of flash will improve performance. It is critical to consider which flash storage option can deliver the best long-term benefit. At least for the near future flash will be considerably more expensive than capacity disk drives, and as such its deployment should be better planed. The MS-SQL flash strategy should not only include what is best for the long term needs of the MS-SQL environment, but the entire data center.
Sponsored by Tegile Systems
Tegile Systems makes both All-Flash and Hybrid arrays that are feature rich. This allows their customers to select the type of flash solution that makes the most sense for their specific data center. Tegile’s storage arrays also include data efficiency techniques like deduplication and compression, making them ideal for MS-SQL and virtualized environments.
Watch On Demand
George Crump is the Chief Marketing Officer at VergeIO, the leader in Ultraconverged Infrastructure. Prior to VergeIO he was Chief Product Strategist at StorONE. Before assuming roles with innovative technology vendors, George spent almost 14 years as the founder and lead analyst at Storage Switzerland. In his spare time, he continues to write blogs on Storage Switzerland to educate IT professionals on all aspects of data center storage. He is the primary contributor to Storage Switzerland and is a heavily sought-after public speaker. With over 30 years of experience designing storage solutions for data centers across the US, he has seen the birth of such technologies as RAID, NAS, SAN, Virtualization, Cloud, and Enterprise Flash. Before founding Storage Switzerland, he was CTO at one of the nation's largest storage integrators, where he was in charge of technology testing, integration, and product selection.
StorageSwiss.com - The Home of Storage Switzerland 