Provisioning SQL Server on Azure VMs

It’s incredibly easy to stand up an Azure Virtual Machine (VM) to host your SQL Server. But have you noticed? Depending on the size you choose those suckers are expensive! When it comes time to choose a VM size we definitely want to avoid over-sizing.

Then again, if we under-size the VM, SQL Server may not have the resources it needs and we’ll be paying the price in poor performance.

Just Right

One of the drivers in moving to the cloud is to economize on resource usage. Finding the VM size that accommodates a given workload yet avoids over-provisioning is important. We are looking for a Goldilocks solution – it should be just right. Identifying and maintaining (e.g. scaling-up) that just-right fit is the way to get your money’s worth out of the cloud.

If you plan to migrate an on-premise SQL Server to Azure you can start by making note of the existing server’s resources CPU, RAM and Disk capacity. Then look to Microsoft’s Azure VM sizing web page to match up the collected parameters as best as possible to what Azure VMs offer. Maybe make some adjustments, as needed, for existing resource bottlenecks and future growth. Maybe use the Azure VM pricing calculator to consider cost-per-month. Finally, pick an Azure VM size. The provisioning effort and size selection doesn’t have to be perfect – just a best effort to point out a good VM size to start. It’s easy enough to scale up or down if some post-migration analysis suggests a smaller or bigger VM size is “just-right.”

Just (not-quite) Right

But provisioning the Azure VM size is only half the battle. There is another component here that is just as important for cost and performance and just-right considerations. It’s not as forgiving as VM sizing since, once committed, it is not as easy to scale. This tricky component is storage. Not just storage capacity as mentioned above, but also throughput and cost.

The disks that you can attach to an Azure VM come in 4 sizes, each with different capacities, speeds, and prices.

*standard disks are pay-for-capacity-used, this price assumes 1TB of data

Microsoft published some best-practice suggestions for Azure VMs which make good sense. One of their suggestions is to “use a minimum of 2 P30 disks.” No doubt, that is a good “in-general” recommendation. Sure, we want high-capacity and fast storage. But it’s not uncommon to migrate smaller line-of-business type SQL instances to Azure. Does a few infrequently used, 50-100GB databases warrant 2TB of P30?

Probably not.

On the other hand, could an enterprise caliber SQL instance get off cheap with 8 Standard disks striped under an 8TB, 480MB\sec, 4000IOPS virtual disk?

Maybe… (probably not, but maybe).

“In-general” style suggestions are OK, but for storage – same as for the VM sizes – to get the most out of SQL Server on Azure VM, we are going for “just-right.”

Sunday! Sunday! Sunday!

When it comes time to attach and configure storage for an Azure VM we have a handful of opportunities and limitations to take into account.

The limitations are spelled out, per VM size, on the Azure VM sizing web page. Note these parameters:

•         Max Data Disk – this is how many disks you can attach to this size VM
•         Max Data Disk Throughput: IOPS\MBps – this is the speed limit for any\all disks attached to this VM.

The opportunities arise out of Windows Server 2012 Storage Spaces. This feature allows disks to be simple-striped (think of it as RAID0) into multiple disk pools. Virtual disk can then be defined from the pool-space and formatted as lettered volumes (e.g. the L: SQL Logs drive for database log files). Each pool aggregates the individual capacity and throughput capabilities of the pooled disks.

Figure 1: Storage Spaces and Storage Pools http://bit.ly/29X83vq

Let’s look at an example. Imagine we have gone through the steps to provision the Azure VM size and now we are thinking about configuring the storage. We have a Standard_DS4. Here is the info from the VM size web page:

We can attach up to 16 disks. In theory, via Storage pool disk striping, we could create one super-charged 16TB M: Monster-Disk for EVERYTHING; data log and temp and etc.

But no. First, that would cost too much. Also, we want to follow some of the Azure VM best-practices (i.e. 1 disk for data, another disk for logs). Most importantly, we can’t ignore the VM’s IOPS and MBps limits. So, the question is –  what is the best option here to set up the disks on this VM … what types and how many?

Behold… The Calculatorinator

To visualize all this and make better provisioning decisions I have invented an “Azure VM Size and Disk Calculatorinator.” (Yes, like all Doofenshmirtz inventions it has a self-destruct button). Unlike the Microsoft Azure VM Price Calculator, this calculator includes disk as well as VM prices (approx.) and also shows how striped (or un-striped) disk throughput and capacities fit with in IOPS thresholds for a given VM size.

Figure 2: calc-ing 3 single standard disks for DS4 (but why would anyone do this?)

To operate, one follows directions on the left and edits the green cells to select a VM size, a disk type and a disk count. The results show capacity and throughput for single disks on three proposed pools: Data Disk, Log Disk and TempDB. As Fig. 2 shows, Data, Log and TempDB don’t necessarily have to be pools – a disk count of 1 implies the Azure disk is attached directly. However, in most cases, to make the most out IOPS and capacity, and to allow the possibility for additional disk (scale), the disks should be striped.

The calculatorinator results also show over-all capacity, throughput and approximate price.

Another result section illustrates how the selected configuration fits within the Azure VM throughput thresholds. IOPS and\or throughput for the ideal disk configuration will be close to the VM limits -not too much over the limit, not too much under the limit … yeah, you know it … just-right.

Figure 3 “this storage pool is too small” – Goldilocks

Be a Striper

In contrast to the standard disk example above, here’s a better setup for the DS4 we have been looking at so far as an example.

Figure 4: calc-ing DS4 with 6 P20s and 3 P10s

Figure 5: juusssstttt-right (for a throughput workload)

You can build your own calculatorinator pretty easily using some 101 level Excel skills. All you need is the Azure VM sizing page, the Azure disk info above, and a couple other pages out there on VM and disk prices per hour. Contact me in the comments if you have questions or let me know if you want a copy of my calculatorinator and I’ll send you one.

For additional resources please check out our whitepaper: Investigating SQL Server Database Backup Options in Azure.

Happy provisioning!