Rajendra Gupta
compute tier behaviour

Automatic Pause and Resume of an Azure SQL database

April 2, 2021 by

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.

No option to pause or stop azure database

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.

Cost comparison

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.

Provisioned, and Serverless compute tiers.

In case you increase the vCores and data max size, you get a higher estimated cost per month.

vCores and data max size

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

Serverless computing

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.

Compute bills in Azure database serverless architecture

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).

Auto-pause delay

  • 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:

  1. Number of sessions : 0

AND

  1. 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.

Minimum and maximum vCores

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.

create a database page

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.

serverless database configuration

Click Create and within a few minutes, your azure SQL database with serverless architecture is available.

Note down server name

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.

Verify DB SLO

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

compute tier behaviour

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.

In the output, it returns database properties in JSON format.

SQL Database using Azure CLI

Configure a Serverless Azure SQL Database using PowerShell

Similarly, we can use New-AzSqlDatabase cmdlet with parameters similar to the azure CLI command.

SQL Database using Azure PowerShell

Refresh Azure portal for SQL databases, and it reflects the newly created azure SQL database with serverless computing.

Refresh Azure portal

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.

As shown below, it has a default auto-pause delay of 1 hour.

Verify Auto-pause delay

It has a minimum of 0.5 vCores and 1 maximum vCores using the T-SQL script.

Verify minimum and maximum vCores

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.

Rajendra Gupta
Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

244 Views