Considering a Hybrid Storage Array for Hosting your SQL Server Database?

Are you considering moving to a hybrid storage array for hosting your SQL server database? With the industry shift from spinning disk to SSD Flash storage, I'm commonly asked about performance concerns when migrating SQL servers to this newer flash technology.

Related: SQL Server Storage Best Practices

I have engaged with hundreds of IT directors, IT managers, and SQL DBAs within the past 24 months that typically have the same performance concerns and questions. The typical question is, how much flash or how many SSDs do I need to adequately support my SQL Server? Well, that's a multifaceted question that requires the user to look at several different dependencies—I'll break these down for simplicity and clarity. 

Size of Active Data 

First, the admin needs to know the size of the active data within the database. What does this mean? Well, let's say Bob has a 600GB SQL database. That 600GB database has been in use since 2010. Thus, this is a collection of data up to the present day. Not all of that 600GB data is accessed on a daily, weekly, monthly, or even yearly basis. So, knowing how much of it is critical to choosing the correct storage configuration for hosting the SQL database and logs. 

How Storage Vendor Handles Caching, Tiering

Second, the admin needs to understand how the storage vendor handles caching and/or tiering for the SSD flash media. Knowing how the storage vendor handles the incoming write and read requests will better prepare the user for avoiding performance pitfalls. For SQL, the database and the logs should be split into separate volumes on the storage server. If you do not split the database and the logs then you'll face severe performance issues.

The log files for SQL are sequentially in nature. This means they are better suited for disk-based performance and not on the SSD or flash based tier. However, the database should be in the SSD tier or on the flash based tier at all times in order to provide the lowest latency and faster response times for users. Thus, splitting the database and logs should always be done for hybrid storage arrays. 

Assigned, Pinned, or Allocated

Third, the admin needs to make sure that the SQL database can be assigned, pinned, or allocated to the SSD or flash-based tier. This means the volume for the database always resides on SSDs or flash media. If the storage vendor cannot guarantee or assign, the volume location then blocks within the database volume could be subject to demotion downstream to the spinning disk. This means that when the data within those blocks are accessed, the reads will be coming from spinning disk and the latency will be greatly increased. This is when users will be calling you wanting to know why their reports are running so slow! The SQL database volume should always remain on the SSDs or within the flash based tier to ensure optimal performance and happy users. 

Consider Throughput

Forth, the admin needs to consider throughput. For this, the admin needs to know the IOPS and reads-versus-writes ratio for the existing infrastructure today. This will provide the needed background for determining the number of NIC ports and how the NICs should be teamed/bonded for better performance and aggregated throughput. 

Available Tools

There are several tools on the market that the admin can run to help them gather the needed active/hot data, IOPS, reads-versus-writes, and throughput of their existing SQL setup today. Gathering this data and applying the tips above will help you avoid the SQL pitfalls that a lot of admins fall into.

Want to talk more? Still have questions? Please leave your comments or questions below, or I can be reached at [email protected].