Getting started with Azure SQL Serverless.
The usage guide with cases, code, and video.
I was intrigued by features of Azure SQL Serverless in preview, and general availability on November 4th doesn’t disappoint me either. So I decided to share my experience with this article, demo GitHub repository, and video. These materials are part of the #AzureAdventCalendar community event.
TL;DR; I will share my experience with burstable and pauseable SQL Serverless database in Azure. Provide the YouTube video, GitHub demo repository with infrastructure script, so you can run tests by yourself.
Usage scenarios.
Let’s take, for example, my scenario. It consists of a workload during office hours from 7.00 till 19.00 with heavy ETL afterward. So a burstable database (from 0.5 to 6 vcores) that can pause looks like a perfect match for a mix of Azure Service Fabric stateless services and Azure Functions applications.
In the case of resource consumption around 100 DTU during the day and need for 400 DTU during the data processing pipeline, there were only two options for me before. The first one is to scale-up the database tier manually with a stored procedure or Azure CLI. And the second one is to run DB always around 200–300 DTU targets.
So I created a test solution to run load tests and collect results. And It seems that the SQL Serverless database with 0.5–6 vCores can adequately handle the load from up to 50 servers of Azure Functions based on the Consumption plan. ETL time significantly decreased as well, and average work hours usage was about 20% of vCore resources, which is a brilliant result.
Use case scenarios for Azure SQL Serverless.
- Spatial and low compute usage.
- Enterprise workloads during office hours.
- Average compute utilization with prolonged idle periods.
- Event-driven application with the spike loads.
- Azure Functions applications with disabled database autopause.
Burstable SQL database that can scale to zero.
Azure SQL Serverless can be created only with vCore based tier of Azure SQL and 5th generation hardware, and it can be only run as a single instance. With the Standard tier, you can allocate any vcore number from 0.5 till 16 and the memory allocation pattern equals to 3 GB RAM per core.
Autopause, i.e., scale down to zero, is the main feature offered by the Azure SQL Serverless database. But it is related only to vcores and memory because you still need to store database files.
It can be disabled if you need only the burstable feature to work with Azure Functions. But I want to advise against it for other usage scenarios because Serverless SQL Compute resources more expensive than provisioned ones.
Shutdown timeout can be in the range of 60 to 7000 minutes, and the former is the best option for the idle period. The first request to the stopped database instance in the morning will result in a timeout, and SQL Server error with code 40613 “Database is unavailable.“
Its better to pre-warm database with time-triggered Azure Function or Logic App (Link) with the full set of your production queries so that Azure SQL Server can rehydrate cache.
A database can be moved to Serverless tier or easily created from the Azure Portal or a command line.
There is a need for proper solution design.
- Allocate additional storage for TempDB system database.
- Implement in a client app transient failures handling and request retry.
- Create Compute warm-up with a range of queries to rehydrate cache.
Cost estimation
Microsoft documentation provides an excellent explanation in this area so that I will mention the basics and my takeaways.
There are three parts of the price component:
- Compute cost is $0.0001479 per vCore-second.
- Premium storage cost $0.1265 GB/month.
- Backups cost PITR $0.22 and LTR $0.055 GB/month.
With my preliminary usage estimate and load tests — it seems that Azure SQL Serverless can save about half of the budget expenses. Keep in mind that with a vCore based SQL Server model, you are also paying for Azure SQL Server TempDB system database compute and storage space.
So how do you know how many VCores are usually active?
You should look into the app_cpu_percent metric, where 100% is equal to maximum vCore allocation. So if you have a 15% metric value for a maximum of four vCores — it means that only one vCore is in use at the moment.
Is it cheaper than CosmosDB? It’s not the correct question, but I will try to answer it in the next article about CosmosDB :).
When will DB autopause? It depends.
The autopause is a significant feature that enables you to save money with specific scenarios, but there is always a catch :). So it’s crucial to know limitations and test autopause behavior by yourself.
The Azure SQL Serverless DB autopause may not happen at all, if.
Number sessions > 0
CPU > 0 for user workload running in the user pool
Active geo-replication is enabled.
Long-term backup is executing.
Azure infrastructure update.
The sync database used in SQL data sync.
A few points.
I want to emphasize again the topic of “Transient failures” and the need to implement the “Retry” code when working with cloud SaaS solutions. There is a chance for automated updates that can result in errors and exceptions.
You can also be extremely unlucky if Azure is allocated VM with vCores number less then configured maximum in the DB settings. In this case, a scale from 2 to 4 vCores can take up to 3 minutes without any database downtime, but in the end, connections to the old VM instance will be dropped. I think you can step into this situation when there will be an average usage of 1 vCore for a prolonged period and then burst up to the four vCores.
In case of the long idle period or a cold start — intensive disk IO operations may be required to rehydrate data pages into the SQL buffer pool. And query plans may need to be recompiled too. That happens because of aggressive memory reclaim by the Azure to free-up used resources and eventually pause the database instance.
Conclusion.
Is it for everyone? Definitely not, but if you need a burstable relational database or there is a particular usage pattern that fits Azure SQL Serverless, then it’s a great option.
That’s it, thanks for reading. Cheers!
