Select Page

Provisioning SQL Server on Azure VMs

Author: Andy McDermid | 6 min read | January 9, 2017

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.

 Disk Type Max IOPS Max MB\sec Capacity GB Per\month\1TB (approx.)
Standard 500 60 1024  $   51.20*
P10 500 100 128  $ 157.68
P20 2300 250 512  $ 146.44
P30 5000 200 1024  $ 135.17

*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

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:

Size CPU RAM Disk Limit IOPS Limit (cached) IOPS Limit MB\sec Limit(cached) MB\sec Limit
DS4 8 28 16 32000 25600 256 256

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!

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Oracle RMAN Backup and Recovery with Restore Points

Oracle restore points are useful for benchmark testing. Find out how you can use Oracle’s Recovery Manager (RMAN) tool to create and use restore points.

Cindy Putnam | May 3, 2019

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.