Getting the most out of serverless Azure SQL

Microsoft released the serverless compute tier for Azure SQL databases to General Availability a little over a year ago, back in November 2019. Despite the fact that it’s been a while already, based on my personal experiences people are still a bit unfamiliar with serverless Azure SQL – what it is, how it works and, most importantly, when should I use it and how? So, I figured, why not try and shed some light into those points in this blog!

So, what is serverless Azure SQL?

Classically Azure SQL has been a provisioned PaaS-offering that relies on dedicated hardware: You select a service a tier for your database, the associated hardware capabilities are available to you 24/7 and Microsoft bills you with a fixed monthly rate (well, hourly rate). You could scale the database from one service tier to another manually or via Azure APIs to accommodate for increasing usage demands, and then scale back down to save on costs when your database is being used less. This is a model that works well, but it does have some drawbacks: First, scaling between tiers is very granular in the provisioned model. 2 vCores of General purpose Gen5 compute aren’t enough for your database and you need a little bit more power? Well, then the only option is to go straight to 4 vCores with twice the hourly rate. And second, as mentioned before, scaling the databases requires either manually performing admin work in the Azure Portal (…or through PowerShell or CLI), or implementing some monitoring scheme that automates the scaling for you. Oh, and lastly, you can’t turn off a provisioned Azure SQL database the same way you can turn off an Azure virtual machine. If your database is unused during the night, then with a provisioned database the best you can do is scale it as far down as possible, but even with the cheapest vCore-database you’ll be racking up the costs quite a bit.

I guess this is one kind of a serverless database, too?

Note! Performance in Azure SQL databases can be managed either in DTUs or vCores. Since serverless is only available in vCores, I’ll be mostly using vCores in this blog post. That’s not to say DTUs are bad – in fact, they are fantastic especially for lightweight solutions that don’t need full dedicated vCores worth of processing power!

At this point you might have guessed that serverless Azure SQL has something to offer to all of these points, and you’d be right! Essentially, the way a serverless Azure SQL database works is this: You give your database a minimum and a maximum performance tier, say, from 0,5 vCores to 6 vCores, and then you can decide on an idle shutdown period. Which is at the minimum 60 minutes. Then Azure spins up a SQL database that’s using the minimum number of vCores you chose and as the database is being used Azure dedicates more hardware to it as needed all the way up to your maximum vCores, and then frees resources as usage decreases. Completely automatically and without any required human input. What’s more, Azure only bills you based on actual usage (or based on the minimum number of vCores assigned, whichever is higher): So, if your database is currently using 3,49 vCores worth of compute or memory, then that’s what you will be billed for at that second. However, if your usage is below the minimum vCores provisioned, then you will always be billed for that minimum usage no matter what.

Note! While billing with serverless SQL goes by actual utilization of resources and although the actual lowest amount of vCores you can have on your database is 0,5, the actual minimum billing at any given time is by 0,68 vCores. This is because the usage is based on both CPUs used and memory used, and the minimum amount of memory an Azure SQL database requires is 2,05 GBs which translates to 0,68 vCores. If your usage is 3,49 vCores you’ll be billed for that (and not for 4 vCores), but if your usage is 0,5 vCores you’ll be billed for 0,68 vCores worth of serverless usage.

In addition, serverless Azure SQL databases can actually be turned off, which leads to zero billing for compute resources (that is, CPU and memory; you will still be billed for storage during this time). Mind you, you can’t turn the databases off manually, but Azure will do it for you if the database has absolutely no usage for the period of time that you have configured for its shutdown period. The shutdown period is at least 60 minutes long, so you can’t just fire up a database exactly when you need it and then immediately shut it down, but this feature still allows you to manage database costs nicely. One thing that’s important to be aware of is that when Azure notices that someone is trying to use your shutdown serverless database, it immediately begins to provision the minimum vCores that you have configured. This provisioning process takes about five minutes to finish, and during this period the database is still unusable. If you are going to use serverless Azure SQL databases with the idle shutdown feature, remember to have a mechanism that warms up your database before you start to actually use them!

With that said, Microsoft’s documentation on serverless Azure SQL has a nice diagram that illustrates how the scaling, idling and billing work. I also recommend reading the documentation for some deeper technical tidbits if you are planning to use the serverless compute tier in your projects.

Doesn’t this sound too good to be true..?

…is something you might be asking after reading all that. Well, yes, it’s true that serverless Azure SQL does have some downsides, and it doesn’t suit every application or solution. The most obvious case is if your database has a fairly steady usage that requires little scaling, and which can be nicely served with a provisioned database. In such scenarios serverless databases are actually a lot more expensive – 2 vCores in a serverless database costs more than twice as much as 2 vCores in a provisioned database do. Serverless is cost-effective if your usage fluctuates a lot, but otherwise it’s better to keep using provisioned databases.

Currently, in the North Europe data center, running 2 vCores for a month at 100 % usage capacity in serverless Azure SQL costs 668 €. Running a provisioned 2 vCore database for the same period costs 320 €!

So, what might that mean in practice? For example, a database used for storage in an IoT solution with a steady traffic of incoming sensor data would be a poor choice for a serverless Azure SQL database – unless it has to serve occasional workloads that process large amounts of data. Similarly, a business application that is only used during business hours and which spikes in usage at certain times of day – or even better, purely randomly – could work well in serverless Azure SQL: Outside of business hours the database could be shut down, and during usage it would accommodate to load seamlessly.

Another downside is that even though the scaling is automatic and pretty fast, it’s not instantaneous. If your application can’t under any circumstances stand momentary slowdowns while Azure is provisioning more resources for your serverless database, then a provisioned database that has enough hardware to meet any sudden spikes in usage is the only way to go.

Third thing that comes to mind is that currently serverless Azure SQL only supports the General purpose service tier. Azure SQL has three service tiers: General purpose, Business critical and Hyperscale (or if you are using DTUs instead of vCores, Standard and Premium). As the name implies, the General purpose tier (or Standard in DTUs) is pretty good for most cases, but it doesn’t suit every application. The next tier, Business critical (or Premium in DTUs), offers much faster I/O operations and read-only replicate databases, among other things. I/O-bound operations such as reading and writing data are incredibly fast on Business critical databases, and by employing the read-only replicas you can dedicate the main read-write -database’s resources only for performing database updates. Database workloads that require more I/O-resources than CPU will find the Business critical -tier to be very useful, but unfortunately these are not supported with serverless Azure SQL databases.

Doing serverless Azure SQL the right way

If you are planning on using serverless Azure SQL, the very first question to ask is this: “Does serverless actually benefit me?” You can’t utilize a service well if it doesn’t suit your needs in the first place, so let’s start with that. Sometimes it can be difficult to say for certain whether serverless is better than provisioned is for you, but there are a couple of things to look out for:

  1. Does your database consistently have zero utilization for at least 120 minutes straight during the day? The most obvious savings in serverless come from the ability to turn the database off entirely, although it takes at least 60 minutes of uninterrupted downtime for the shutdown to trigger. There’s no clear-cut limit on how much down time your database should have to benefit from serverless (the more the better, obviously!), but 120 minutes of daily uninterrupted downtime is something where I’d start doing cost calculations myself.
  2. Does your database generally have low utilization, but it occasionally has to serve heavy workloads such as Power BI refreshes on large amounts of data? If so – and if you have scaled your database up to meet the demands of the heavy workloads – then serverless might be for you.
  3. Does your database face varying loads that are difficult to predict? Serverless could be useful here due to its automatic scaling. However, you need to be careful that the usage spikes are actually high enough that serverless is more cost effective than simply creating a provisioned database that meets the spikes on its own would be.

My personal favorite discovery with the first point above is that serverless actually works really well for databases that are used for storing data from daily or twice-a-day batch jobs that are then used as a data source for imported Power BI datasets. The reason this works wonders is because imported Power BI datasets store a copy of the data in the Power BI service, so once the dataset refreshes are done the reports don’t connect to the database at all. It’s not a fancy solution, but if your solution refreshes only once a day (as is the case with many reporting solutions!), then a serverless database can be shut down for most of the day. Running 1 vCore of serverless for two hours a day (let’s say your batch job and Power BI refresh both take 30 minutes, followed by a 60 minutes of idle time) costs barely more than a S1 database with 20 DTUs – and the performance difference between these two is enormous for the benefit of the serverless database!

Note! The above price comparison is not including storage for the serverless database, while DTU databases include 250 GBs of storage in their costs. That would have to be calculated on top of the compute price. Regardless, the serverless still gives a ton more oomph for only a slightly higher price point compared to provisioned databases in this particular scenario. Just make sure to automatically trigger your Power BI refreshes right after the batch jobs have finished to maximize uninterrupted database downtime!

If you are considering serverless to support spiky demands, it’s important to consider how spiky the spikes really are. Consider the North Europe price example from above: 2 vCores in serverless cost 668 € / month, while provisioned 2 vCore would cost 320 € / month. On top of that, the minimum cost for serverless at 0,68 vCores is 228 € / month, so there’s not lots of room for cost optimization here if the spikes only require one or two additional vCores on top of your regular usage. Of course, it’s not just a matter of how tall the spikes are, but also how often they occur: If you only have those spikes few times a day, and they don’t last very long, then serverless can still be good for you. But in general, and I’m simplifying a lot here, if your resource usage metrics look more like gentle rolling hills than the alps, provisioned databases might work better for you.

Would a database with a CPU utilization graph like this benefit from serverless? There’s not enough uninterrupted downtime to benefit from shutdowns, but the spikes are generally very sharp and narrow, so I’d definitely investigate it further!
How about this one? There’s a lot of idle downtime here, so that looks very promising! However, the big plateau at 100 % utilization could end up being expensive in serverless. Unless this database is currently running on 20 DTUs in which case switching to serverless even at max 1 vCore would provide massive performance benefits!

Ultimately, in some scenarios it will be difficult to tell beforehand whether serverless is actually better for you than provisioned is. Fortunately, it’s fairly straightforward to test both configurations: You can swap an existing provisioned database to use serverless in the Azure portal with a few clicks, and reverting the changes is just as easy. You can expect there to be a very minor service break or service degradation as Azure reconfigures the database, so you should take that into account if you are doing tests on a production environment. Other than that, you don’t really need to do anything at all when switching to serverless – unless you are going to make use of serverless’ idle shutdown feature. In that case you need to modify your solution to either warm up the database before it’s being used again, or to implement a recovery mechanism to support a database that’s currently shut down.

If you are going to test serverless and how much it’s going to cost in practice, I generally recommend running it for a full week before making any conclusions. This way you get usage data from a few workdays and also from the weekend, which is generally a quieter time for most business solutions. You can then use the serverless database’s billing metrics to estimate costs and the performance metrics to estimate how well the current database configuration met its performance demands.

Summary

Serverless Azure SQL offers a nice flexible way to implement scaling in your databases, and in quite a few cases it can give you good cost savings or even performance boosts, if you are able to upscale to serverless from a lower tier database while keeping costs similar. Especially so if your database workloads are CPU-bound, since the Business critical service tier which is more suited for I/O-intensive operations is now available in serverless. However, it’s good to be careful, since serverless is also expensive when compared to provisioned databases in terms of how much sustained performance you can get for the same amount of money. So, should you go for serverless? As usual with data, it depends, and with serverless the answer usually lies hidden somewhere in your databases’ usage metrics.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s