An Overview Of Performance Differences of Highly Available SQL Server Storage Configurations In #Azure: SMB 3.0 File Service Or Premium Storage

There are a few options when it comes to SQL server storage configurations in Azure. If you want to know, you can get some good idea from this article Windows Server Failover Cluster on Azure IAAS VM – Part 1 (Storage). It talks about the newly released Azure File Service that can be used to host SQL Server cluster data over SMB 3.0. Remember, till date Azure File Service cannot support Premium Storage. You are bound to about 1,000 IOPS or 60 MB/s per file share. With these limits in mind, Azure File Service is probably going to to be an option for databases with minimal IO demands.

Check Out My Test Results

So the plan was to test a few different SQL Server Storage Configurations. I provisioned a DS4 VM and attached some premium storage. Next, I attached a SMB 3.0 File share using Azure File Service. Here’s how I configured my SQL Server Storage Configurations.

• F:\ – Three 1 TB P30 Premium Storage Disks added to a single 3TB pool
• G:\ – One 1 TB P30 Premium Storage Disk (no Storage Pool)
• Z:\ – SMB 3.0 File share on Azure File Services

The Process

Be really careful when you’re configuring the Storage Pool for use in a cluster. Either you create the Storage Pool before the cluster is up, or use the Powershell script in Sql Alwayson with Windows 2012 R2 Storage Spaces if the cluster had already been created. I’ve created a Simple mirror (RAID o) Please note that I’m not worried about redundancy since Azure storage has triple redundancy on the backend.

To configure the Storage Pool for use in a cluster, you have to be careful on how you proceed. You either have to create the Storage Pool before you create the cluster or if the cluster is already created,  use the Powershell script described in Sql Alwayson with Windows 2012 R2 Storage Spaces. For increased performance, the pool I created was a Simple mirror (RAID 0). I’m not concerned about redundancy since the Azure storage on the backend has triple redundancy.

I should get up to three times the performance of a single disk, since I’ve three disk in the Storage Pool in a RAID 0. Now, if I choose to add even more disk to the pool, I’ll enjoy even higher performance. A single P30 disk gives me 5000 IOPS and 200 MB/S. Based on this, I should expect up to 15000 IOPS and 600 MB/S throughput for my pool.

Now that I have the storage out of the way, I configured Dskspd to run the same test on each of the different volumes. Here is what I did with the parameters using Dskspd.

Diskspd.exe -b8K -d60 -h -L -o8 -t16 -r -w30 -c50M F:\io.dat

Diskspd.exe -b8K -d60 -h -L -o8 -t16 -r -w30 -c50M G:\io.dat Diskspd.exe -b8K -d60 -h -L -o8 -t16 -r -w30 -c50M Z:\io.dat

And The Results Are Out

The results on different SQL Server Storage Configurations were rather predictable and summarized below.

Looking at the result, this particular job did not push the upper limits of the theoretical maximum of any of these storage solutions. However, the latency had a significant impact on the overall performance of this particular test. The test used 8k blocks in a mix of 30% writes and 70% reads to simulate a typical SQL Server OLTP workload.

Of course, the more money you want to spend, the more performance you can expect to achieve. It’s relative.

Price Comparison Of SQL Server Storage Configuration in Azure

As of November 24, 2015, the price for the best solution shown here (F:\) would cost $1,216/month. It promises full access to 3 TB of storage with unlimited reads/writes.

The second best solution (G:\) would give you 1 TB of storage at 1/3 the price, $405/month. Azure File Share is priced at $0.10/GB plus additional charges for read/write operations. You are only charged for the actual usage. So estimating the actual cost will be very dependent on your usage. You are at about 25% of the cost of Premium Storage before the additional charges for read/write operations.

Prices, like everything else in the Cloud, tend to change rapidly to address the market demands. Have a look at the latest price information at https://azure.microsoft.com/en-us/pricing/details/storage/ for the latest price information.

Summary

From this compilation and price overview of SQL Server Storage Configurations, Azure File Services does look enticing from a price perspective. The latency at this point does not make it a viable option for any serious SQL Server workload. Instead, have a look at utilizing premium storage and leveraging either host based replication solutions such as SIOS DataKeeper to build SQL Server Failover Cluster Instances (SQL Standard or Enterprise) or look at SQL Server Enterprise Edition and AlwaysOn AG.

Reproduced with permission from https://clusteringformeremortals.com/2015/11/24/highly-available-sql-server-storage-options-in-azure-smb-3-0-file-service-or-premium-storage-a-look-at-performance-differences/