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:
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.
Lets create one.
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.
I initiate a connection so that the database auto-resumes. It goes into a RESUME state.
Now, this is important. I used SSMS to get a connection but I received the following.
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.
So, if you can afford that delay trade-off it is worth considering.