Azure SQL Database Serverless

Quite a significant change has taken place within the Azure SQL Database space, more specifically the development of Azure SQL Database Serverless. Currently in preview mode this “compute” tier changes how you are billed (/second) and addresses some behaviors that many have wanted in the past. There are things to be aware of though.

This is best used for those single databases that are ever changing with unpredictable patterns. With 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 with auto-pause becoming possible. The auto-pause delay defines the period of time the database must be inactive before it is automatically paused (only charged for storage). 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).  It will leave the paused state if any of the below takes place:

autoresume

Reference: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-serverless

To use this, as you can see below when you configure the database you will need to select Gen 5 Compute. You will also notice that you can actually disable the auto pause feature.

serless

Lets create one.

vcoredb

I cannot find any T-SQL to check the status of the database, whether it is paused or not. You will need to use PowerShell for that. I was curious as to when it goes into a paused state. (We will have to wait 6 hours as defined above).

It currently shows as ONLINE using the below script.

Get-AzSqlDatabase -ResourceGroupName yourRG -ServerName yourserver -DatabaseName vcoredb| Select -ExpandProperty “Status”

I wait for 6 hours and re-run the above command. It shows as PAUSED.

paused.JPG

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

resume1

Now, this is important. I used SSMS to get a connection but I received the following.

failed

This happened because it was in the resume state and not fully online. My testing suggests around 45-60 seconds for the database to come back online i.e. leaving the paused state.

online

So, if you can afford that delay trade-off it is worth considering.

 

 

 

 

 

3 thoughts on “Azure SQL Database Serverless

  1. Pingback: Azure SQL Database Serverless – Curated SQL

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