skip to Main Content

Azure SQL Database Serverless

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:

CPU Usage

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.

Create SQL Database

Now, this is only available for the General Purpose tier. At the time of writing serverless is not supported if using Business Critical.

Service and Compute Tier

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.

Max & Min 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.

Max and Min vCores

The auto pause functionality, well as the message states we have 1 hour as the minimum.

Auto-pause Delay

Let’s look at a scenario. The blue circle below is when compute utilization completely drops off within the SQL Database.

Compute Utilisation

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.

Resource Group 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.

Data Encryption

Resource Group 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

Autoresume Trigger

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.

Paused

I initiate a connection so that the database auto-resumes. It goes into a RESUME state.

Resumed

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.

Post Terms: Azure SQL | Database | serverless

About the Author

Arun Sirpal

Arun Sirpal, writing here as a freelance blogger, is currently a two-time Data Platform Microsoft MVP, specialising in Microsoft Azure and Database technology. A frequent writer, his articles have been published on SQL Server Central and Microsoft TechNet alongside his own personal website. During 2017/2018 he worked with the Microsoft SQL Server product team on testing the vNext adaptive query processing feature and other Azure product groups. Arun is a member of Microsoft’s Azure Advisors and SQL Advisors groups and frequently talks about Azure SQL Database.

Education, Membership & Awards

Arun graduated from Aston University in 2007 with a BSc (Hon) in Computer Science and Business. He went on to work as an SQL Analyst, SQL DBA and later as a Senior SQL DBA and now DBA Team Lead. Alongside his professional progress, Arun became a member of the Professional Association for SQL Server. He became a Microsoft Most Valued Professional (MVP) in November 2017 and he won it for the second year in 2019.

You can find Arun online at:

Back To Top