Auto-scaling Azure SQL database with Azure Logic Apps

Scenario:

On weekdays for about day times, we would like to automatically scale up our Azure SQL Server database to a higher compute tier and night time would to like to automatically scale down our Azure SQL Server database to a lower compute tier. I am aware that we may manually accomplish that through the Azure portal. Is there a way to automatically do this procedure every day?

Solution:

Certain scenarios in our application create a high load on the system, and may negatively impact performance. To meet the performance requirement, we can easily scale up Azure SQL Servers Database to higher compute tiers in Azure. Using the portal, we can scale up the Azure SQL Servers DB at any time.

Upgrading the Azure SQL Servers DB Compute Tier using the Azure Portal

You can upgrade or downgrade the computing tier for the Azure SQL Servers database by using the configure blade in the menu section.

For deploying your databases in the Azure managed service, Azure SQL Database supports DTU-based and vCores-based purchasing models. You can choose as per your need.

With Azure, you may dynamically scale-up or down resources according to the demands of your application. Several DTU service tiers and their approximative prices are shown in the graphic below.

Why Autoscaling is important?

One of the benefits of running databases on Microsoft Azure SQL Database is the ability to manage them dynamically in response to changing workload demands. Autoscaling databases is the most cost-effective way to improve data operations performance.

It allows you to easily adjust the maximum DTUs (CPU/memory/IOPS resources) used or the storage and also, allows you to scale into other editions at any time.

This suggests that resources can be scaled up for heavy workloads or down for light ones. Costs often go down as a whole as a result of this. In reality, upscaling may take place at the beginning of a working day or an ETL run because heavy workloads are anticipated at those times.

Autoscaling using Azure Logic Apps:

A quick overview of the Azure Logic Apps

Based on connection with your apps, data, and services in the cloud or on-premises, Azure Logic Apps offers a solution to automate workflows and business process execution. For instance, you may send triggering events to a process that is triggered based on input data from your Azure databases. It is a more straightforward approach to system, data, and app integrations.

The Azure Logic Apps is a component of Azure App Service, It aims to use code-free automation to access and use data across clouds. Azure Logic Apps is priced on a per-execution basis. All the Logic App Connectors that are available to support integration and orchestration across products are one of its key selling factors.

Prerequisites:

For creating a Logic App which will scale our SQL Servers DB, you need the following items and access.

• Azure portal access where Azure web App is deployed.
• SQL Server Name
• SQL Database Name
• SQL Server Username
• SQL Server Password

With Azure Logic apps, let’s begin with Azure automation.

6 Steps for Azure Autoscaling SQL Database

Step #1: Create Logic App:

To create logic app, after login in azure portal, and search the “Logic Apps” in the search box and select the logic app.

After navigating on the Logic apps section, add a new logic app. Create the logic app in a similar screen as:

Fill details of a logic app like name, its resource group, location etc. and click on create a button. (select plan according to your needs)

The system will open the logic app designer window on which you will see a list of templates to build workflows quickly or we can create it from a blank.

Select Blank App to start with a new template.

Step #2: Schedule time:

The first thing that needs to be defined is what will be the trigger to the Logic App or What time will be trigger the logic app. For this search for Recurrence and select request:

After selecting recurrence, you can schedule time as per your need, I am selecting every weekdays 8:00 pm, this logic app will be triggered.

Set details as below:

Step #3: Add a SQL Connector

Now, select the +New step option. Look for SQL Server in the Choose an operation section. It returns the highlighted result after performing a few actions such as deleting a row and running a SQL query (v2).

Click on Execute a SQL query (V2) in the actions. When you choose to execute a query, another window for SQL Server connection opens.

Enter the connection name, authentication type, SQL Server name, SQL database name, credentials, and your Azure subscription on this page.

In the connection form, enter the connection name, authentication type, SQL Server name, database name, and credentials before clicking New. It establishes connections and then comes back to run a SQL query (v2). Choose your configured connection from the drop-down as shown below in the server name and database name fields.

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

Scale-up Azure SQL Database

As shown below, specify the ALTER DATABASE statement in the query section. It modifies the Azure SQL database and sets the standard service level to the S9 goal with Max Size 250GB.

ALTER DATABASE [your_database_name] MODIFY (EDITION = 'Standard', MAXSIZE = 250 GB, SERVICE_OBJECTIVE = 'S9');

You might want to be notified whenever any service objective or tier changes. For this purpose, click on +New Step and you can add email notification but I’m not adding it here.

I’ll go with next step. Now, I’ll make a 12 hours delay, then scale-down database as before.
Let’s see.

Step #4: Add Delay

Click on +New Step and search for “Schedule”, and you can find as below.

After click on “Delay”, set details as below.

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.

Next, adding logic for scale-down SQL server database.

Step #5: Add SQL Connector

Scale-down Azure SQL Database

As shown below, specify the ALTER DATABASE statement in the query section. It modifies the Azure SQL database and sets the standard service to S2 goal with Max Size 100GB.

ALTER DATABASE [your_database_name] MODIFY (EDITION = 'Standard', MAXSIZE = 100 GB, SERVICE_OBJECTIVE = 'S2');

Finally, our logic app is ready. Our whole app design will be look like as below:

Now, Save it. Let’s test it by manually trigger, for that click on “Run trigger” from upper menu section.

For confirmation open the Azure portal in another window and open the Azure SQL Servers database and click on “Compute + Storage” in the menu section. Here, you can see current selected plan details.

Here, you can see the DB Max Size 100 GB and Service Objective(DTU) 10 which is S2.
Now, I’m running the app.

As you can see here, our app running successfully for scale-up db, let see in the sql server db of SQL which has opened in another window, just refresh it and it might be takes 1-2 minute to show a new scaled plan.

Perfect!, here, DB plan is scaled.

Now, our app is wait for 5 min, then it will execute logic of scale-down of SQL server DB. Let’s see.

As you can see, our db is scaled down as before after 5 min of delay.
So, in this way you can automated your scale-up and scale-down your SQL server database resources using logic app as per your requirements.

Summary:

In the above article, we saw how to Auto-scale Azure SQL database with Azure Logic Apps.

Moreover, if you’re dealing with enterprise cloud software or large-scale cloud applications, you may wish to seek professional assistance. For an expert helping hand, Get in touch with our Microsoft Azure consulting services experts having a minimum of 8+ years of experience working on Azure IoT, Cloud Integration & Migration, DevOps, Analytics and many more Azure services.