Create a Serverless Azure SQL Database

In this article, we will take a look at how to create a serverless Azure SQL database.

Introduction to Serverless Computing Model

Traditionally on-premise or private data-center infrastructure was used to host the IT eco-system. With the advent
of the cloud, the IT ecosystem composed of applications, data repositories and services started moving to the cloud.
One of the core benefits of cloud that drives migration from on-premise to cloud is the financial incentive, which
is achieved due to a heavy reduction in capital and operational expenditure on hardware and infrastructure.
Generally, those moving from on-premise to cloud consider the Infrastructure As a Service (IaaS) option i.e. in
simplified words, hosting systems on a Virtual Machine (VM). In the context of the Azure cloud and SQL Server as the
database server, hosting it on a VM. With this model, while one does not have to permanently own or maintain
hardware, but still there is a significant degree of software, infrastructure capacity and OS management involved.

The next logical step from here is to move to Azure SQL Database instances or Managed SQL Database or Elastic Pooled
database instance on Azure. With this model, the onus of managing the OS and the software itself gets eliminated,
but still one needs to know the capacity one needs to meet the requirements of their workloads. To deal with this
situation, a serverless model comes in which can auto-scale based on the threshold criteria to meet the elastic
scaling needs of the workloads. SQL Server Database on Azure supports the serverless model and in this article, we
would learn how to create a serverless Azure database.

Serverless Azure SQL Database Setup

A pre-requisite to follow this exercise is to have an Azure Account with required privileges on Azure SQL Server
Database as well as Azure SQL Server service. As the first step, log on to the Azure portal and navigate to All
Services. Click on the Azure SQL Database service from the Databases section as shown below.

Azure SQL Server Database

Once you click on this option, you would be navigated to the database Dashboard page. Let’s start creating a new
database on Azure. Click on the Create SQL Database button to initiate database creation.

Create New Database

Provide the subscription and resource group details as shown below. It’s assumed that you already have a resource
group in place, if not, you may need to create one and specify in the resource group option.

Basics of Azure SQL Server Database

In the next step, we need to provide a database name and select the SQL Server instance under which this database
would be hosted. It’s assumed that you already have a SQL Server instance in place, if not, you can create a new one
by clicking on the Create New button under the Server dropdown.

Basics

In the next step, click on the Configure database button. By default, the selected tier is S0 with 10 DTU and 250 GB
storage capacity. This falls in the provisioned tier where the capacity is fixed. We intend to change this to the
serverless mode so that we can configure it to be elastic.

Baiscs of SQL Server Databases on Azure

Once you click on Configure database, you would see a screen as shown below where you would see the Basic tier, 10
STU And 250 GB storage selection. If you are using the trial subscription, you may find some tiers unavailable for
use based on the type of your subscription. Click on the arrow near vCore-based purchasing options, to uncover the
other tiers.

Serverless Settings

Once you click on the arrow, the below options would appear. You would find that by default the Provisioned compute
tier is selected, which means that compute resources are pre-allocated and fixed. In this model, we pay for the
compute resources irrespective of whether it’s used or not.

Serverless Settings on Azure

Click on the Serverless option as shown below, so that the compute resources are elastically scaled based on the
workloads. Considering the elasticity, one needs to define the minimum and maximum thresholds so that based on the
volatility of the workloads, the resources do not get too under-provisioned or over-provisioned which may have cost
or performance impacts.

Serverless Settings of SQL Server Database.

Here you can see that a minimum of 1 vCore and a maximum 2 vCore are selected, and based on this the available memory
of 3 GB to 6 GB is auto-selected, as this is the capacity which is tied to the selected vCore sizes.

Azure SQL Server Database Serverless Settings

The next setting that we need to configure is the Auto-pause delay. This setting causes the database to pause after
a fixed interval of inactivity and resume once the activity resumes. You can disable this setting too. In
non-production workloads, when the environment is dormant, this can save a significant amount of costs. Depending on
your requirements, select a relevant time duration for pausing the database. You can also configure the storage
capacity as required. By default, the storage capacity is 32 GB.

Serverless Settings

Once done click on the Apply button, and you would find the configuration as shown below.

Databasse Serverless Settings

You can choose to customize the rest of the settings or continue with the default. Once you are done with the rest
of the settings, when you reach the Review section, you would find the details as shown below. Based on our
selection, the estimated monthly cost is shown to be just $4.78 per month due to the serverless model of the
compute.

Create option

Review the details and click on the Create button to create the server Azure SQL Server database.
Once the database is created, you would be able to see the dashboard page of this database as shown below.

Dashboard of SQL Server Database

You can connect to this serverless Azure SQL Database using a locally installed SSMS too and execute some queries.

SSMS

Once the database is kept idle for a duration that is more than the auto-pause threshold, the database would
automatically get paused. You can check the database utilization and status from the dashboard page as shown below
where you would find the spike and drop in compute usage when the database gets paused and when it resumes.

Monitor Activity

In this way, we can create a serverless Azure SQL Database, which provides us the required elasticity as well as
cost savings depending on the needs of the workloads.

Conclusion

In this article, we understood how the serverless model provides elasticity and reduces the management or need to
pre-determine the capacity allocated or required for the database. Then we learned step-by-step, how to create and
configure an Azure SQL database in serverless mode, as well as define the thresholds for the elasticity.

Rahul Mehta
Latest posts by Rahul Mehta (see all)

Author: admin

Leave a Reply

Your email address will not be published.