Pause and resume Azure SQL database

In this Azure SQL tutorial, we will discuss how to pause and resume Azure SQL database.

• Can we pause and resume a database in Azure SQL?
• Serverless database Azure SQL
• How to create serverless database in Azure SQL
• Enable auto-pause in Azure SQL
• Enable auto-pause while creating the database
• Enable auto-pause after the database has been created

Can we pause and resume a database in Azure SQL?

You might have faced the need of pausing the database to save the cost several times, majorly, because of inactivity in the database. There are several times when your database is not consuming enough resources or it is inactive for a long time.

You need to create a serverless database to pause and resume a database. But, you cannot pause and resume a database manually. The Azure SQL will automatically do it for you. To know more about a serverless database, refer to the below section.

Serverless database Azure SQL

Microsoft Azure SQL provides the creation of a serverless database for the scenarios where the database is frequently inactive or not being accessed for a long period of time.

For example, if your database is accessed for 4-5 hours in a day only and remains inactive for the rest of the time, you can create a serverless database.

But, this model is quite expensive than the provisioned database model in terms of computing cost. If your database remains inactive only for a short period of time, you should use the provisioned model for creating a database.

Also, there is a time delay when the database is resumed after remaining in a paused state. Therefore, use this model only when the inactive time period is long enough and you can afford short delays.

Points to Remember:

• Compute cost is zero while the database is paused. However, you have to pay the storage cost even if the database is in paused state.
• Azure SQL prvides the automatic scaling of the database in the serverless model.
• Billing cost is calculated per second.
• You can decide the auto pause time delay. If the database remains inactive for this time delay, only then the database will be paused.

Read How to Connect to Azure SQL database

How to create serverless database in Azure SQL

There are few things that you have to take care of while creating a serverless database. Some of the common parameters that you will select are:

• Create a single database and select General Purpose in service tier.
• Select Serverless in the Compute Tier option.
• Enable the auto-pause option and select the time duration for auto-pause.

For a full tutorial on how to create a serverless database, you can read our article How to create a database in Azure SQL.

Enable auto-pause in Azure SQL

In this article, you will learn about the auto-pause feature in Azure SQL. Azure SQL provides the auto-pause feature for serverless databases.

You have to determine an auto-pause delay after which the database will be paused automatically and resumed if any request is made to the database. You are not charged for the computing cost for the time the database is paused. however, you have to pay for the storage.

The range of this time delay is between 60 minutes to 7 days. This means you cannot set this value to less than 60 minutes and greater than 7 days.

Enable auto-pause while creating the database

We can decide the auto-pause delay while creating the serverless database.

While creating the database, you have to check the Enable auto-pause option if you want to enable auto-pause. If you uncheck it, the database will always remain in the running state. You can refer to the below image:

Read How to rename a database in Azure SQL

Enable auto-pause after the database has been created

You can also enable or disable the auto-pause option once you have created a serverless database. Follow the below steps to enable or disable the database:

• Open the Azure portal in the browser and login into your account.
• In the Azure portal, click on Resource Groups and click on the resource group under which you have created the database.

• You will see a list of resources. Click on the database whose auto-pause settings you want to change.

• Click on the Auto-pause delay option as shown in the picture below:

• Scroll down until you find the auto-pause option as shown below in the image:

• Now you can enable or disable the auto-pause option. Also, you can change the time delay.

Thus, you might have learned how to enable or disable the auto-pause option in Azure SQL.

• Can we pause and resume a database in Azure SQL?
• Serverless database Azure SQL
• How to create serverless database in Azure SQL
• Enable auto-pause in Azure SQL
• Enable auto-pause while creating the database
• Enable auto-pause after the database has been created

You may like:

• Cannot open server requested by the login. client is not allowed to access the server.
• How to create table in azure sql database
• Connect to Azure SQL database using Python