Five Reasons to run MS-SQL on All-Flash

Applications are often the key service that data centers provide to their organizations. The speed at which these applications can respond to the user’s requests is often THE measurement of how IT is perceived by the organization. As a result IT departments spend a lot of time and resources making sure the database application is running at peak performance at all times.

Over the last decade Microsoft SQL has emerged as the foundational component that these applications are built from. The infrastructure that surrounds them is critical to meeting user performance expectations. While CPU and memory resources have continued to improve to deliver an acceptable performance experience the storage infrastructure has not.

Now, however, all-flash storage systems have come to market along with significantly improved network infrastructures to finally reduce, and potentially eliminate, the performance bottleneck. The pay-off for an all-flash investment is not just performance; an all-flash array can lower operational, licensing and server hardware costs while simplifying database design and maintenance. At the same time an all-flash array also eliminates the complexity around designing a storage system to support a database application.

Reason 1: Consistent Performance and Low Latency

Hard disk based systems are constantly constrained in terms of performance. Not only are these systems short on IOPS (Input/Output Operations per Second) performance, they are also highly latent environments as drives attempt to rotate platters into position so the right data can be read at the right time. This latency leads to designs that are complicated and not suitable for scaling.

All-flash does not have these issues. There are no platters to rotate, data is instantly accessible. Reads are essentially “free” since data is read directly from the correct flash location. All-flash arrays should be able to respond to most read requests in sub-millisecond times, no matter the current workload.

In terms of IOPS, most all-flash arrays can deliver 200K-500k IOPS. But as we discussed in our article “What Are IOPS and Should You Care?” the reality is that IOPS are not the main attraction for the use of all-flash in MS-SQL environments. The overwhelming majority of MS-SQL applications do not need IOPS in excess of 100k and almost none need millions of IOPS.

Why all-flash then? The answer is consistent performance and very low latency. Consistent performance means that no matter what the database environment is asking of the storage system it receives rapid response. This means that the database administrators (DBA) and storage administrators don’t have to schedule and plan when certain activities will occur.

Reason 2: Rethink (simplify) Database Management

Database design and management is often a complex process that involves the storage administrator trying to be a DBA and the DBA trying to be a storage administrator. They have to plan where to place tempDB and log files as well as how to configure the LUNs or volumes that will store those files. This means trying to decide between the various RAID level choices and the type of drives to use. For example many DBAs will want to place these files on small solid state disk drives (SSD) in the server configured in a RAID 10 setup, and then place the core database itself as well as the application on the shared storage system.

When considered in a single database environment this does not seem too daunting of a task, but when multiplied across dozens if not hundreds of database instances, the complexity can be overwhelming. We’ve seen environments with as many as 400 volumes dedicated to various components of their database environment.

All-flash changes this, thanks to the above performance. These systems have so much excess performance that the entire database, log files and tempDB can be assigned to a single volume, without any performance impact. In fact as the organization becomes convinced of the consistent performance of the all-flash array they begin to move to a single volume for the entire database environment.

The net impact of all of this is to greatly simplify database design, layout and management. The simpler storage infrastructure also means that the data protection process is greatly improved.

Reason 3: DBAs can be DBAs

The third reason to invest in all-flash for the MS-SQL environment is that it allows DBAs to be DBAs again. The simplification of the storage infrastructure described above allows DBAs to focus on development, instead of spending their days wrapped up in performance tuning analysis. Thanks to the consistent performance of all-flash they can now focus on implementing new features and capabilities that Microsoft is advancing.

The all-flash array also enables this new development effort because of its ability to deliver snapshots and clones of database data sets instantly. It also allows those data sets to be operated on and tested without impacting the performance of the rest of the actual production environment.

Reason 4: Not Just for MS-SQL

One of the theoretical payoffs of the moves to a shared storage network was the consolidation into a single storage system. The reality is that this never happened. The storage system was the key limiter; it did not have the ability to handle the mixture of workloads the rapidly evolving data center began to throw at it including: server virtualization, desktop virtualization, analytics and unstructured data storage. This is not to mention the reality that most MS-SQL environments run multiple flavors of SQL ranging from MS-SQL 2005 to 2014.

In most data centers multiple silos of storage are the order of the day. In addition to dedicated storage systems for MS-SQL there is also often a storage system for the virtualized server environment, the virtual desktop infrastructure, analytics processing and the storing of unstructured data.

An all-flash storage system, again thanks to this excess performance and low latency, can support all of the above workloads, without performance impact. This allows the storage infrastructure to be greatly simplified into a tier for performance and a tier for capacity.

Reason 5: All-flash is cheaper

The big win for MS-SQL environments is that in almost every case an all-flash array can be purchased for less money than a performance focused hard disk drive based system. This statement is more than likely accurate even at a hard cost perspective, but is certainly so when you consider the fact that the hard drive based system will need many extra drives to achieve acceptable performance. Add to this the fact that an all-flash storage system should enable the database to scale a single server higher so it can support more users and even more database instances.

Finally, the soft cost savings described above by freeing up DBA and storage administrator time can be significant. This time savings can lead to improved database and storage infrastructure quality as they move from the role of firefighter to fire prevention.

Conclusion

All-flash storage in the MS-SQL environment is a no-brainer. The processing and memory bandwidth exists to push MS-SQL to its limits. The only limiting factor is the storage infrastructure. Thanks to 10GbE and 16Gb FC, the storage network is also ready.

The last remaining concern is the storage system itself. All-flash arrays are now priced at a point that they are less expensive than a high performance hard disk based system, but with significantly more performance capability. The DBA and storage administrator can leverage this excess capacity to both simplify the MS-SQL environment while at the same time consolidating non-database workloads onto a single system. The net result is a data center that is more responsive in its entirety and one that allow DBAs and storage administrators to proactively improve their organizations technological competitiveness.

Sponsored By Pure Storage

Pure Storage is an example of a company that delivers cost effective, feature rich all-flash arrays that hit the performance sweet spot the MS-SQL environments need. Their systems also feature deduplication and, most important in a database environment, compression which increases the effective capacity of the all-flash environment. Pure Storage customers have repeatedly indicated that the implementation of a flash storage system has not only met the performance demands of their users but it has made database management significantly easier.

Click Here To Sign Up For Our Newsletter

Twelve years ago George Crump founded Storage Switzerland with one simple goal; to educate IT professionals about all aspects of data center storage. He is the primary contributor to Storage Switzerland and is a heavily sought after public speaker. With over 25 years of experience designing storage solutions for data centers across the US, he has seen the birth of such technologies as RAID, NAS and SAN, Virtualization, Cloud and Enterprise Flash. Prior to 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.

Tagged with: , , , , ,
Posted in Article
4 comments on “Five Reasons to run MS-SQL on All-Flash
  1. yacoob patel says:

    Dear George:

    All well and good and i agree with the above article to a certain extent and that includes improved performance …we all want that.

    How is the write endurance addressed on flash…do we keep changing ssd as they reach their life span?

  2. George Crump says:

    Thank you for reading and your question. Honestly write endurance is a non-issue anymore. MLC drives have advanced to the point that under normal use they will last about five years, from a durability aspect. This is especially true with all-flash systems since they don’t have the turnover rate that hybrid systems do. See this article for more details on flash durability:

    https://storageswiss.com/2014/02/26/slc-vs-mlc-does-it-matter-any-more/

  3. ashminder ubhi says:

    The way to deliver the best performance to ms-sql is using storage which integrates SMB Direct (over RDMA). Microsoft added this to windows, hyper-v and mssql for ultimate low latency and importantly lower cpu usage leading to better density. A flash array with integrated SMB Direct will give the greatest benefits.

  4. John says:

    George, while I appreciate the concept of an all flash system delivering great performance with very low latency, there is really no way possible for an AFA to be cheaper/GB with out some form of data manipulation (i.e. de-dupe / compression). A hybrid approach should always deliver a better $/GB and may very well deliver equal performance. There are many instances where a spinning disk will deliver similar performance to SSD and to say that MLC handles writes w/ the same durability as hard drives or SLC is a bit of a stretch. And to say that all data belongs on SSD when only 5-10% of your data is active just does not make financial sense with an nearly 30:1 cost differential.

Comments are closed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 22,245 other followers

Blog Stats
  • 1,563,007 views
%d bloggers like this: