Rajendra Gupta
Intro to SQL Database: Single database DTUs by tier and level

Azure Automation: Auto-scaling Azure SQL database with Azure Logic Apps

May 12, 2021 by

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

  1. Basic
  2. Standard
  3. Premium

In this model, we have the following service tiers.

  1. General Purpose
  2. Business-critical
  3. Hyperscale

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.

service level tier

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.

Intro to SQL Database: Single database DTUs by tier and level

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.

modify the service tier using the Azure portal

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.

Notification

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.

Current Azure SQL database service tier

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.

Azure credentials

Let’s select the basic tier from the drop-down.

basic tier

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.

ALTER DATABASE script

Execute the query and it quickly (with small downtime) downgrades DTU service objective.

DTU service objective

Verify pricing tier

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.

Upgrade resources

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.

Azure Logic Apps

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.

Logic app designer

The logic app designer page gives you common triggers to quickly start designing logic for implementation.

common triggers

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

specify schedule

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

Add a SQL Connector

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.

Execute a SQL query(V2)

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.

configured connection

Click on Add new parameter and select query from filter parameters.

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

Scale-up Azure SQL Database

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.

Send an email

In the actions, select Send email (V2). Specify a connection name for mail service. Here, I specify the connection name Gmail Connection.

Send email (V2).

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.

Specify recipient email address

Click on Add new parameter and select the required field Subject, Body as shown below.

Add new parameter

Specify a custom email subject and body for the email alert. In the body, I have specified the query also.

custom email subject and body

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.

Click on Run

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. View progress of workflow

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.

get failure reason

Therefore, go back to the logic app homepage and click on Edit.

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.

remove the Go clause

You get an email alert per your configuration.

email alert

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.

verify the pricing tier

Scale down Azure SQL Database

Similarly, you can create another logic app to scale down the pricing tier from Standard to Basic.

Scale down Azure SQL Database

Modify your Gmail email subject and body to reflect the query we execute for scaling down the pricing tier.

Modify email content

Do a manual execution, and it runs the workflow successfully, as shown below.

manual execution

Here, you see an email for scaling down the pricing tier for the Azure database.

scaling down pricing tier

Verify pricing tier either using SQL query or Azure portal.

Verify pricing tier

Add a Delay operator

You can combine both scaling up and down resources using a delay operator.

Add a Delay operator

For demonstration purposes, I added a 5-minute delay in the logic app.

 5-minute delay

Once you execute the logic app, it starts a timer and waits for the next task execution for 5 minutes.

task wait

As shown below, the workflow scales down resources after a specified delay.

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

Azure Automation: Export Azure SQL Database to Blob Storage in a BACPAC file
Azure Automation: Create database copies of Azure SQL Database
Azure Automation: Automate Azure SQL Database indexes and statistics maintenance
Azure Automation: Automate Pause and Resume of Azure Analysis Services
Azure Automation: Automate data loading from email attachments using Azure Logic Apps
Azure Automation: Building approval-based automated workflows using Azure Logic Apps
Azure Automation: Auto-scaling Azure SQL database with Azure Logic Apps
Azure Automation: Analyzing Twitter sentiments using Azure Logic Apps
Azure Automation: Use Azure Logic Apps to import data into Azure SQL Database from Azure Blob Storage
Azure Automation: Publish LinkedIn posts and tweets automatically using Azure Logic Apps
Azure Automation: Export Azure SQL database data to CSV files using Azure Logic Apps
Azure Automation: Translate documents into different languages using Cognitive Services with Azure Logic Apps
Azure Automation: Azure Logic Apps for face recognition and insert its data into Azure SQL Database
Azure Automation: Automatic scaling Azure SQL databases based on CPU usage threshold

Rajendra Gupta
Azure, SQL 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

2,085 Views