Selecting the right storage for SQL Server high availability in the cloud

When it comes to the type of storage you might use for a cloud-based SQL Server deployment, all the major cloud providers provide a bewildering array of options. Azure offers Standard HDD as well as Standard SSD, Premium SSD as well as Premium SSD v2. Oh, and then there’s Ultra Disk. And AWS? The options are no less eye-glazing: ST1 and Standard, GP2 and GP3, IO1 and IO2.

Even if you could easily differentiate the offerings, what would be your best choice if you plan to configure your infrastructure for high availability (HA) -- by which we mean an infrastructure designed to ensure that your SQL Server database will be available and operating no less than 99.99 percent of the time?

Let’s start by looking at function rather than form: If you’re configuring your SQL Server infrastructure for HA, you’re likely to choose one of two approaches to HA. You could use the Availability Groups (AG) feature that’s built into SQL Server, or you could use a third-party SANless clustering solution. Both approaches involve creating a primary and secondary instance of SQL Server, and both involve deploying those instances in separate cloud availability zones (AZs). Both provide synchronous data replication services to ensure that the updates to the database in the active instance of SQL Server are fully replicated to the secondary instance, and both will cause the primary instance of SQL Server to fail over immediately to the secondary instance if something causes the primary instance to malfunction.

That said, you need to configure at least two identical systems capable of providing the level of performance and storage your applications require -- and for that reason it’s easy to eliminate certain storage solutions right away. Azure Files and AWS FSX provide shared storage solutions, but they won’t provide the level of performance required in a high performance SQL Server environment. You’re looking for high performance storage solutions that will be locally attached to each of your SQL Server instances in the different AZs.

Options in Azure Cloud

In the Azure world, you can bypass the Standard HDD and Standard SSD offerings for the same reason you’ll bypass the shared disk options: neither are fast enough to support the demands of a high performance SQL Server environment. That leaves you with Premium SSD, Premium SSD v2, and Ultra Disk offerings. The key difference between Premium SSD and the others is that Premium SSD comes in fixed chunks. You’ll need to configure some number of Premium SSD disks in a Raid 0 array to ensure the I/O per second (IOPS) and MB/second performance levels your application requires — and that frequently means you need to overprovision storage in order to get the performance you need. The Premium SSD v2 and Ultra Disk offerings can be configured to meet your IOPS and MB/Sec throughput and capacity requirements so that you don’t need to overprovision storage.

So Premium SSD v2 vs. Ultra Disk? The practical difference between the two boils down to price. In a high performance SQL Server environment, there's minimal difference in latency between the two systems except at the very edges of throughput ranges, yet Ultra Disk costs significantly more than Premium SSD v2.

Options in AWS

Among the storage options available in AWS, you can bypass both HDD-based options (ST1 and Standard) for the same reason you should bypass those in Azure: they’re just not fast enough for a high performance SQL Server environment. That still leaves you to sort out whether to configure your SQL Server instances with GP2, GP3, io1 or io2. Like Ultra Disk in Azure, Io2 and io2 are very high performance -- and very high cost -- storage options that for most organizations will be overkill. Unless you need latency no higher than 1-2 milliseconds, there’s no reason to spend four to five times what you’d spend on the GP2 or GP3 offerings.

In terms of performance, GP2 is similar to Azure Premium SSD v2 and GP3 is more like Azure Premium SSD -- though there’s an important difference. In AWS, the constraints on IOPS and throughput are not necessarily tied to your choice of storage (as in Azure Premium SSD vs. Azure Premium SSD v2). Instead IOPS and throughput constraints are tied to your choice of VM. Rather than provisioning storage to ensure the level of IOPS and throughput you need, in AWS you need to provision your VMs to deliver the level of IOPS and throughput you need. Unlike in Azure, where you might overprovision storage to get the performance you seek, you can configure a VM in AWS with the number of CPUs that will provide the performance you need and then turn off the CPUs you’re not using. You’ll get the IOPS and throughput but you won’t be charged for the CPUs you are not using -- nor will you have to purchase extra licenses for SQL Server, which is licensed on a per-CPU basis.

Operationalizing HA

Questions of performance and licensing bring us back to the question of how you are going to implement HA. How many databases will you be replicating from primary to secondary infrastructure? If you expect to replicate multiple SQL Server databases using the AG approach, you will likely need to use SQL Server Enterprise Edition and its Always On AG functionality. If your application only requires SQL Server Standard Edition, you’ll find that an expensive upgrade no matter which storage solution you’ve decided to use. If you use a third-party SANless clustering solution, you can replicate as many SQL Server databases as you want and still rely on SQL Server Standard Edition because a SANless clustering solution, which performs block-level replication, is database agnostic. As you weigh the overall cost of configuring SQL Server for HA in the cloud, you need to keep in mind that the overall cost can be influenced not just by your choice of storage and VM configuration but also by the approach you choose to ensure that these critical infrastructures provide the levels of availability you require.

Dave Bermingham is Director of Customer Success at SIOS Technology. He is recognized within the technology community as a high-availability expert and has been honored to be elected a Microsoft MVP for the past 12 years: 6 years as a Cluster MVP and 6 years as a Cloud and Datacenter Management MVP.

Dave holds numerous technical certifications and has more than thirty years of IT experience, including in finance, healthcare, and education.

Joey D’Antoni is Principal Consultant at Denny Cherry & Associates Consulting, a SIOS partner. He’s a Microsoft Data Platform MVP and VMWare vExpert. He has over 20 years of experience working with databases.