So you’re a SQL Server DBA. And, it’s Tuesday.
But, it’s not just any Tuesday.
It’s that magical Tuesday where you find out that you’re responsible for a cloud migration: Your SQL Servers are moving to Azure, specifically to “SQL Server on Azure VM”. (Not to be confused with Azure SQL Database, or Azure SQL Managed Instance – which are, of course, totally different but really close in name. Because, Microsoft.)
Now what?
One of the first decisions you’ll face is to choose the right Azure VM sizing (aka “SKU”). And, I’ve been there, so I’ve got you:
RAM-to-vCPU Ratios for SQL Server on Azure VM
When optimizing SQL Server on Azure VMs, it’s essential to favor VMs with favorable RAM-to-vCPU ratios – which, for SQL Server, generally means stuffing as much RAM as you can into the VM.
If you’re new to Azure, however, what’s less obvious is that you cannot just “add more RAM” in a vacuum. In Azure, “more RAM” also comes with “more vCPUs” – in a ratio that is standard across a given Azure VM series.
Currently, the D-series VMs boast ratios such as 4:1, while the E-series VMs feature the highest a ratio of 8:1 (meaning, 8GB of RAM for each vCPU).
Microsoft
For most enterprise settings, I really wouldn’t waste my time with anything less than a D-series VM to run SQL Server, at least not in production. Of course, you’re free to go for a cheaper VM and find out the hard way how I’ve come to that conclusion.
Example Recommendation: Although you’ll still want to benchmark the resource usage of your current on-premises SQL Server(s) to evaluate whether a D-series or E-series VM best meets your workload needs, you should bias towards the E-series Azure VMs when running SQL Server due to the higher RAM-to-vCPU ratio. A standout choice would be the Edsv5 series or the Eadsv5 series.
(Note: The “d” in the SKU signifies the inclusion of attached ephemeral storage, a crucial aspect for optimal performance, discussed below. Additionally, the inclusion of the letter “s” in the SKU is also important – although we’ll discuss SQL Server on Azure VM storage considerations in a later blog post – because it means the VM is compatible with the Premium tier of Azure Managed Disks. Finally, the “a” in the SKU means that the CPU is an AMD-based processor, instead of an Intel CPU; the AMD variants tend to run a bit cheaper, and in my testing were actually a bit faster for our SQL Server workloads, but of course your mileage may vary there.)
vCPU-Constrained VMs For The Win
So, now you’ve chosen a beefy E-series Azure VM for your main SQL Server, loaded with all the RAM you could ever want. Sure, that also came with tons of vCPUs, too – but who cares, amirite?
Wrong. You care. Your wallet cares, because you just destroyed your budget with SQL Server licensing costs – which are charged per vCPU. (And, if you’re licensing Enterprise Edition, your wallet was doused in rocket fuel before being lit on fire, too.)
Controlling SQL Server on Azure VM licensing costs is paramount, particularly for Enterprise Edition deployments. So, how do you do that while also getting the RAM you still need to (properly) run your SQL Server workload?
Answer: vCPU-Constrained Azure VMs.
Each vCPU-Constrained VM comes with the higher RAM you’re otherwise looking for (plus the increased VM throughput and IO you’ll likely want/need, topics for later), but restrict the VM’s active vCPUs to a lower number than the typical ratio for that VM series.
This means you can license SQL Server on an Azure VM and sleep at night, because you’re getting the performance you need at a price that won’t get you fired.
Example Recommendation: So, let’s say you want the 256 GB of RAM, etc. that comes with Azure VM SKU E32ds_v5. But, remembering the 8:1 ratio for the E-series, that SKU also comes with 32 vCPUs. Licensing SQL Server for that Azure VM would get quite pricey, even moreso if you’re running Enterprise Edition – an expense that makes little sense when considering a SQL Server workload is far more RAM-intensive than it is CPU-intensive. In this situation, you’d be far better off with Azure VM SKU like E32-8ds_v5, which only has 8 vCPUs instead of the full 32, saving you 75% on your SQL Server licensing costs while still providing all the delicious RAM goodness your SQL Server desires.
Temporary SSD Storage for TempDB
Any solid DBA will tell you that TempDB configuration is critical to your SQL Server’s performance.
And, that’s where selecting the correct Azure VM comes in – you need to ensure selected VM sizes include appropriately sized temporary storage.
Wait, what’s “temporary storage”?
Without over-complicating things, Azure VMs don’t typically come with “storage” – the VM is “compute”, and you then have to bolt on any “storage” you want to run that “compute” against. While storage considerations are a later post, keep in mind that storage comes in different speeds and can introduce both network-related and disk-related latency – all of which can slow down your SQL Server on Azure VM.
There are, however, certain Azure VM sizes that include, with the VM itself, an attached “temporary disk” for storage. This is almost always an SSD (translation: fast), and is physically located with the server (translation: low latency).
It is called “temporary” because that is a defining feature – anything stored on this “temporary disk” does not persist when the VM is turned off, etc. So, storing your MDFs or LDFs here would result in a really bad day.
But, all those features together make it the perfect home for TempDB MDF and LDF files on the Azure VM.
Make sure whatever Azure VM size you choose to run your SQL Server includes this attached temporary storage, and that is it big enough to house your TempDB files.
If the Azure VM SKU includes the letter “d” in it, that means the VM includes the desired attached temporary storage.
from Microsoft’s Azure documentation
How to configure and use the temporary disk for TempDB is another post for another time; it is enough for now to just know that whatever SKU you choose needs to come with a right-sized temporary disk (because all VM series have variants that are exactly the same, with the only difference being the inclusion or exclusion of temporary storage).
Final word: Really pay attention to this. If you screw up and select a VM in the wrong series, it’s fairly easy to resize that VM to a different series. But, if you screw up and forget to select a VM with attached temporary storage, you’re in for some pain – because you can’t resize that VM to a new SKU that does contain attached temporary storage. Instead, you’ll have to rebuild the VM – which, while do-able, isn’t nearly as easy as just reassigning the SKU. Again, because, Microsoft.
Example Recommendation: When considering SQL Server on Azure VM, opt for VM sizes that include temporary storage – such as the Edsv5 series or the Eadsv5 series (notice the “d” in the SKU!).
Strategically making Azure VM sizing choices based on these three key factors can help DBAs turn a random Tuesday – where a cloud migration project was announced – into the chance to deliver both database performance gains and cost reductions.
Before you think that isn’t possible, I’m proof it is: That’s exactly what we were able to achieve with our Azure cloud migration at Premier Trailer Leasing.
By reviewing and mapping to the proper Azure VM sizes, I was able to reduce database infrastructure spend by approximately 25%, while boosting database performance well over 100% at the same time.
That’s a win-win I’ll take, every time. Even on a Tuesday.
–Scott