Introduction
Azure SQL Database Serverless was first released back in 2019 and has gained much popularity since. This is a compute tier that is fundamentally different to the classic provisioned tiers that we are used to within Azure SQL Database. It changes how you are billed (/second) and addresses some behaviours that many have wanted in the past. Such as the ability for your PaaS (Platform as a Service) based database to pause when there is no activity and resume upon activity.
This is best used for those databases that are ever-changing with unpredictable patterns. The concept of being billed per second (based on the vCores used) rather than per hour means that pricing can become more granular, especially now that auto-pause is becoming possible. The auto-pause delay defines the period of time the database must be inactive before it is automatically paused (you are then only charged for storage costs).
You should only use this if you can afford some delay in compute warm-up after idle usage periods, otherwise, it is best to stick with provisioned compute tiers (classic tiers). There are two important metrics to understand first, these are min and max vCore counts.
Study the following image from Microsoft:
So here we have defined that the workload can burst from 1 vCore up to a maximum of 4 vCores and the ability for the service to pause compute based on a lack of action – the “inactive” period. Let’s set one up.
Creating Serverless Database
You start this by going to the create database blade and selecting configure database.
Now, this is only available for the General Purpose tier. At the time of writing serverless is not supported if using Business Critical.
I use the option of Generation 5 hardware and use the drag – bar section to set the min/max vCores. Don’t forget the memory you require is dictated by the number of vCores you select. For example, change the min and max to 1 and 6 respectively, this gives 3GB min memory to 18GB Max memory. This is very important to consider, SQL Server’s RAM requirements are dictated by the number of vCores you decide on, if you know you need a lot of RAM then it may force your database design to have a high number of vCores.
Just to prove the concept, I change min and max to 4 and 20 cores, see the difference in min and max memory? The SQL memory settings change. Since last June we have had the ability to scale up to a large 40 vCore count, before that we could only move up to 16 vCores.
The auto pause functionality, well as the message states we have 1 hour as the minimum.
Let’s look at a scenario. The blue circle below is when compute utilization completely drops off within the SQL Database.
I then use SSMS (SQL Server Management Studio) to connect to the database, then disconnect and leave it for 1 hour. The status of the database changes to PAUSED.
What triggers it to resume? One way is a login, but there are others as indicated by the following chart (https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview#autoresuming)
Don’t forget, this also includes system activities that I kick off. For example, I decide to turn OFF TDE (Transparent Data Encryption). You can see the status of the database switches to RESUMING.
The below diagram is a list of other settings that could cause the database to resume. They are definitely worth knowing about.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-serverless
Again, expect a performance dip on a resume because memory caches need to be rehydrated. This adds additional latency before optimal performance conditions return such as the ability to use cached plans.
Tracking Status
If you are a DBA or Engineer you may want to monitor the status of the database, whether it is paused or not. You will need to use PowerShell for that.
It currently shows as ONLINE using the below script.
Get-AzSqlDatabase -ResourceGroupName yourRG -ServerName yourserver -DatabaseName vcoredb| Select -ExpandProperty “Status”
I wait for a couple of hours and re-run the above command. It shows as PAUSED.
I initiate a connection so that the database auto-resumes. It goes into a RESUME state.
There are few limitations to be aware of, these are not technical limitations but general ones. You should be aware of the fact that this technology is available in most regions except China East, China North, Germany Central, Germany Northeast, and US Gov Central (Iowa). Additionally, you cannot leverage Azure Hybrid Benefit for cost-saving purposes.
How we use Azure SQL Database Serverless at Ballard Chalmers
Here at Ballard Chalmers, we find this feature useful for keeping costs low during development projects. Due to the large periods where the database is not used.
We also use it for the live databases of AppCan, where the usage patterns are such that the usage is low for many hours, higher in business hours and much higher first thing in the morning on a working day. The serverless database auto-scales up and down to support the load, which works perfectly.
You can find out more about our development work or our speciality in Azure or get in touch directly to find out how we can help.
