Azure SQL Database supports DTU-based and vCores based purchasing models for deploying your databases in the Azure managed service.
|
DTU-based |
VCores-based |
|
A DTU (database transaction unit) is a blend of computing, memory and disk IO resources. |
In vCores mode, we choose several vCores, Memory, Storage size and speed. |
|
It has three different performance levels
|
In this model, we have the following service tiers.
|
|
In the DTU model, we cannot increase individual resources. You can scale resources as per the DT. |
We can configure the vCores, Memory storage as per our requirements. |
Once we configure an Azure SQL database, we must configure a service level tier from DTU or vCores computing models.
Azure allows you to dynamically scale-up or down resources based on your workload requirements. In the below image, we see different DTU service tiers and their approximate pricing.
Image Reference: Microsoft docs
Suppose your database receives maximum workload during specific hours. If you build a database with high DTU, you end up paying the higher cost for running it 24*7*365. You cannot stop the Azure database as well. Therefore, Azure supports scaling up and down resources as per your requirements. For example, you can modify your service tier with higher DTU during day hours and switch to a lower service tier during the night hours.
To modify the service tier using the Azure portal, click on the pricing tier in the azure dashboard and it redirects you for configuring the service tiers.
For example, currently, my lab azure database is in the basic service tier. Suppose I want to modify it to a standard service tier. Click on Standard, configure required DTU and max data size and click on Apply. It starts scaling up the database, as shown below.
As shown below, my lab SQL database is running with Standard S0: 10 DTUs pricing tier. Now, I can run queries with updated compute resources.
Similarly, you can scale down the pricing tier when you expect a minimum workload (for example Night hours). You can change the service tier using the Azure portal or using SSMS as well. Connect to Azure SQL database using SSMS and go to properties-> Configure SLO -> Sign in Azure credentials and modify the edition as shown below.
Let’s select the basic tier from the drop-down.
You can click ok to configure it. However, I am interested here in generating the equivalent T-SQL script. Here, we get ALTER DATABASE script to modify your edition and service objective.
Execute the query and it quickly (with small downtime) downgrades DTU service objective.
Similarly, if I select the Business-critical tier with BC_Gen5_2 service objective in configuring SLO for scaling up, I get the following T-SQL script.
Suppose we wanted to do the same process – Scale-up at 7 AM and Scale-down at 10 PM daily for our Azure SQL database. It is not feasible to follow this process daily manually. Therefore, you require an automation process for it. There are multiple ways to automate azure database service tier modification. You can either use Azure automation runbooks or Azure Logic apps. This article focuses on the Azure Logic apps for automation of scale-up, scale-down azure resources.
A quick overview of the Azure Logic Apps
Azure Logic Apps provides a solution to automate workflows and business process execution based on integration with your apps, data, services in the cloud or on-premises. For example, trigger a process based on input data in your azure databases, sends on triggering events. It is a simplified solution for app, data, and system integrations.
You can refer to Getting started with Azure Function Apps to understand Azure Function apps. Let’s start with azure automation using Azure Logic apps.
Create your logic app for Azure Automation of scaling resources
In the Azure portal, search for logic apps and Click on Create logic apps. In the logic app form, enter the resource group, logic app name, region.
Click on Review+ Create for input validation and resource deployment. Once resources are deployed, it takes you to the logic apps designer. Here, you can run a tutorial video to get familiar with it.
The logic app designer page gives you common triggers to quickly start designing logic for implementation.
In the logic apps designer, we specify the schedule in the recurrence trigger. You can specify your schedule. For example, I want it to execute every weekday at 7 AM IST (UTC + 5.30 timezone).
Add a SQL Connector
Now, click on the +New step. In the choose an operation section, search for SQL Server. It returns the highlighted result with a few actions such as delete row and executes a SQL query (v2).
Click on Execute a SQL query (V2) in the actions. Once you select to execute a query, it opens another window for SQL Server connection.
On this page, enter the connection name, authentication type, SQL Server name, SQL database name, credentials and your Azure subscription.
Enter the connection name authentication type, SQL Server name, database name and credentials in the connection form and click on Create. It creates connections and returns to execute a SQL query (v2). In the server name and database name, select your configured connection from the drop-down as shown below.
Click on Add new parameter and select query from filter parameters.
Scale-up Azure SQL Database
In the query section, specify the ALTER DATABASE statement as shown below. It modifies the azure SQL database and configures the standard service level with the S0 objective.
|
1 2 3 |
ALTER DATABASE [labazuresql] MODIFY (EDITION = 'Standard', MAXSIZE = 250 GB, SERVICE_OBJECTIVE = 'S0'); GO |
You might want to be notified whenever any service objective or tier changes. For this purpose, click on +New Step.
You can send email through various platforms such as Microsoft O365, Outlook, Gmail etc. This article sends email using the Gmail application. Therefore, keyword search send an email Gmail.
In the actions, select Send email (V2). Specify a connection name for mail service. Here, I specify the connection name Gmail Connection.
Click on Sign in and provide your Gmail credentials. The Azure Logic apps ask for permissions to access the Gmail profile for sending an email. In the To field, specify the email address for receiving an email alert.
Click on Add new parameter and select the required field Subject, Body as shown below.
Specify a custom email subject and body for the email alert. In the body, I have specified the query also.
Once your configuration is completed, click on Save in the logic apps designer. As we have specified the schedule in the recurrence, it runs the logic at a specific schedule. Therefore, let’s run it manually for testing the workflow.
It quickly runs, but the task to execute a SQL query step failed as we see below. Therefore, the SQL query is not
executed, and no email triggers.
Click on the Execute a SQL query step to get a failure reason. As shown below, it fails due to the go statement in the SQL query. The logic app does not recognize the go statement in a SQL query. Therefore, we need to remove it before rerunning it.
Therefore, go back to the logic app homepage and click on Edit.
Select the execute SQL query step, remove the Go clause and rerun it. It successfully executes SQL queries on the Azure SQL database for modifying the service tier from basic to Standard.
You get an email alert per your configuration.
You can verify the pricing tier from the Azure portal or run the following query. It returns the modified edition and service objective for the Azure SQL database.
|
1 2 |
SELECT Edition =DATABASEPROPERTYEX('labazuresql','Edition'), ServiceObjective=DATABASEPROPERTYEX('labazuresql','ServiceObjective') |
Scale down Azure SQL Database
Similarly, you can create another logic app to scale down the pricing tier from Standard to Basic.
Modify your Gmail email subject and body to reflect the query we execute for scaling down the pricing tier.
Do a manual execution, and it runs the workflow successfully, as shown below.
Here, you see an email for scaling down the pricing tier for the Azure database.
Verify pricing tier either using SQL query or Azure portal.
Add a Delay operator
You can combine both scaling up and down resources using a delay operator.
For demonstration purposes, I added a 5-minute delay in the logic app.
Once you execute the logic app, it starts a timer and waits for the next task execution for 5 minutes.
As shown below, the workflow scales down resources after a specified delay.
Conclusion
This article explored Azure logic apps for automating Scale up and down Azure SQL database pricing tier per your workload requirements. It is an excellent way to automate workload, logics, sending email based on triggering conditions. Similarly, you can configure Azure Automation logic apps and configure your application logic with minimal steps and coding requirements. You can refer to Microsoft docs for understanding different service tiers and their comparisons.
Table of contents
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
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023