Azure SQL Database is an Infrastructure-as-a-Service component for migrating your on-premises SQL Server to cloud infrastructure. We always look for performance, cost, and scaling resources while we plan resources in the cloud. Many times, we do not want our databases running 24*7*365. Suppose you have a development or training database. Your developers work during the daytime on the weekdays. If you deploy an Azure SQL Database, you don’t get an option to stop it. You get charged for it whether you use it actively or not.
Similarly, you might have specific workloads such as batch data load process that requires high compute resources for limited hours. In that case, we do not want to configure a high compute azure SQL database and pay for higher resources all the time.
Azure SQL Database Serverless compute resources for a single database with the automatic scaling feature. It automatically scales up and down resources based on your workload requirements. If you do not have active connections for specific hours, it automatically pauses databases. Usually, while the serverless database runs, you get charged for storage and per second compute resource usage. Once the database is in paused status, it charges you only for storage, no bills charged for computing resources.
Cost comparison of Azure SQL Database in Serverless and Provisioned compute tier
Once we start deploying the azure database, by default, it configures a general-purpose compute tier with 2vCores and 32 GB storage.
Click on the configure database, and you get Provisioned, and Serverless compute tiers. In the Provisioned tier, your price is dependent on the VCores and max storage. As shown in the below image, it costs you 26660.08 INR estimated cost per month.
In case you increase the vCores and data max size, you get a higher estimated cost per month.
Now, let’s check the estimated cost for Serverless computing. Here, you define min and max vCores. For the below min 0.75 vCores and max 1 vCores, it automatically selects 2.02 GB min and 3 GB max memory.
In the estimated price, you get the following estimates:
- Estimated storage cost per month
- Estimated compute resource cost per second
You can change the min and max vCores and max storage to estimate database cost in a serverless architecture.
Compute bills in Azure database serverless architecture
As shown above, Azure SQL Database serverless architecture charges you for the total number of seconds you have active workload. Now, suppose you have done the following configurations for the serverless database.
- Min vCores: 1
- Max vCores: 4
- Minimum memory: 3 GB
- Maximum Memory: 12 GB
- Max data storage: 200 GB
You get the estimated price for running workload as below.
As per Microsoft docs, the serverless SQL database uses the following formula for billing.
Amount billed: vCore unit price * max (min vCores, vCores used, min memory GB * 1/3, memory GB used * 1/3)
In the following table, we estimate the number of seconds vCores seconds you get billed.
|
Active Duration |
vCores used per second |
GB Memory used each second |
Calculations for billed vCores |
Billed vCores seconds |
|
1 minute (60 seconds) |
1.5 ( Greater than min vCores) |
3 ( Less than max Memory GB) |
(1.5 vCore *( 3 GB /3 GB)) * 60 seconds= 90 seconds |
90 seconds |
|
1 minute (60 seconds) |
4 ( max vCores) |
9 ( Less than max memory) |
4 ( max vCores) * 60 Seconds = 240 seconds |
240 seconds |
|
1 minute (60 seconds) |
0.5 (less than min vCores) |
12 ( max memory) |
12 (max memory) /3 GB * 60 seconds = 240 seconds |
240 seconds |
|
1 minute (60 seconds) |
0.5(less than min vCores) |
9( Less than max memory) |
(1 (min vCore) * 9 GB( memory used) /3 GB )* 60 seconds= 180 seconds |
180 seconds |
|
1 hour( 3600 seconds) |
0.5(less than min vCores) |
9( Less than max memory) |
(1 ( min vCore) * 9 ( Memory used) /3 GB )* 3600 seconds= 3600 seconds |
3600 seconds |
Auto-pause delay
In the Azure Serverless architecture, the database automatically pauses if it is inactive during the specified duration. By default, it is enabled and set to 1 hour (3600 seconds).
- Minimum auto-pause: 60 minutes (1 hour)
- Maximum auto-pause duration 10080 minutes (7 days)
It automatically triggers the auto-pause mechanism if it satisfies the following conditions:
- Number of sessions : 0
AND
- CPU = 0 for any user workload
However, exceptions are always there. If you use the following azure SQL database features, the database always remains online irrespective of auto-pause delay configurations.
- Long-term backup retention (LTR)
- Geo-replication
- SQL data sync
- Elastic jobs
- DNS aliasing
Once the azure database is in pause status, it resumes automatically in the following conditions:
- Database connection
- database export or copy
- Viewing auditing records
- Viewing or applying performance recommendation
- Vulnerability assessment
- Modifying or viewing data masking rules
- View state for transparent data encryption
- Modification for serverless configuration such as max vCores, min vCores, or auto-pause delay
Let’s implement an Azure SQL Database in the serverless architecture. In the configure database of creating SQL database page, define a minimum and maximum vCores, storage and auto-pause delay as per your requirement.
Click Apply, and it returns to create a database page. As we can see, it creates general-purpose Serverless Gen 5 with a maximum of 4 vCores and 10 GB of data storage.
On the review page, you can review serverless database configuration and cost. If you find any configuration issues, you can go back and correct them.
Click Create and within a few minutes, your azure SQL database with serverless architecture is available.
Connecting to a serverless database is similar to a provisioned SQL database. Copy the Server name from the azure portal and paste it in the SSMS connection window.
We can verify database service level objective, edition and maximum size from the Configure SLO page of database properties. Here, you need to sign in with your Azure credentials.
Serverless compute tier behavior
In the below graph, we can observe the vCores and their usage for calculating your azure database cost.
- It has a minimum of 1 vCore and 4 maximum vCores
- At 8 AM, we started the database load, and it gradually increases and becomes inactive at 10 PM
- Due to inactive database connections, it gets paused between 00:00 to 04:00 hrs
- At 4 AM, you connected to the database again, and it resumes the database
- The green bars show the vCores billed and you can note that from 00:00 to 04:00 you do not get charged for vCores
Image reference: Microsoft docs
Configure a Serverless Azure SQL Database using Azure CLI
Previously, we explored Azure portal configurations for creating a SQL database in Serverless configuration. You can use Azure CLI or Azure PowerShell or SSMS as well to create the database. You can refer to the article, An Overview of the Azure Cloud Shell for Azure CLI configuration.
For example, in the below CLI command, we define resource name, Azure server, database name, compute tiers as General purpose, Gen5 with minimum vCPU 0.5, maximum vCPU 2 , Serverless compute model with auto-pause delay of 720 minutes.
|
1 2 3 4 5 |
$resourceGroupName='azuresqldemo' $serverName='azuredemoinstance' $databaseName='myserverlessazuredb' az sql db create -g $resourceGroupName -s $serverName -n $databaseName ` -e GeneralPurpose -f Gen5 --min-capacity 0.5 -c 2 --compute-model Serverless --auto-pause-delay 720 |
In the output, it returns database properties in JSON format.
Configure a Serverless Azure SQL Database using PowerShell
Similarly, we can use New-AzSqlDatabase cmdlet with parameters similar to the azure CLI command.
|
1 2 3 4 5 6 |
$resourceGroupName='azuresqldemo' $serverName='azuredemoinstance' $databaseName='myserverlessazuredb_1' New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName ` -ComputeModel Serverless -Edition GeneralPurpose -ComputeGeneration Gen5 ` -MinVcore 0.5 -MaxVcore 2 -AutoPauseDelayInMinutes 720 |
Refresh Azure portal for SQL databases, and it reflects the newly created azure SQL database with serverless computing.
Create a database using T-SQL for serverless computing
If you have already connected to Azure SQL Server, you can use the CREATE DATABASE command to deploy a database with serverless computing.
In the below T-SQL, we defined the edition as General Purpose and service objective as GP_S_Gen5_1. It creates a database with default values for min vCores and auto-pause delay. Therefore, you should use Azure portal, Azure CLI or PowerShell for creating a database in serverless compute with custom configurations.
|
1 2 |
CREATE DATABASE Sampledatabase ( EDITION = 'GeneralPurpose', SERVICE_OBJECTIVE = 'GP_S_Gen5_1' ) ; |
As shown below, it has a default auto-pause delay of 1 hour.
It has a minimum of 0.5 vCores and 1 maximum vCores using the T-SQL script.
Essential points about the Serverless compute model of the azure database
- You should use SSMS version 18.1 or higher for working with the Azure SQL Server serverless compute. The lower SSMS version resumes auto-paused database if you connect to any other database in the same server
- It is supported only in the Generation 5 hardware of the VCore computing model
- It is suitable for a single database with intermittent and unpredictable usage pattern workload
- It is an excellent way to start with the azure database where you don’t have to an upfront cost and pay only as per usage
Conclusion
Azure SQL Database serverless compute model is an excellent way to start with SQL database in Azure. It is similar to the standard database with an auto-pause and resume mechanism based on your configuration. However, it is not a fit for each database. You should evaluate your requirements, workload, analyze vCPU requirements before deploying it.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023