Auto Scale Azure SQL DB using Azure Logic Apps


By:   |   Updated: 2020-08-27   |   Comments   |   Related: More > Azure


Problem

We would like to auto scale our Azure SQL database to a higher compute tier during the weekends for around 5 hours. I know we can do that manually from the Azure portal. Is there a way by which we can automate this process every week?

Solution

There are certain scenarios in our applications when there is more load on the systems and performance may take a hit during that time. In Azure, we can easily scale up the Azure SQL database to a higher compute tier in order to meet the performance requirement. We can scale up the Azure SQL DB any time using the portal. You can refer to these tips (tip1, tip 2) to learn more about the various compute tiers that are available.

Upgrading the Azure SQL DB Compute Tier using the Portal

By using the configure blade in the settings option on the Azure SQL DB, you can upgrade or downgrade the compute tier for the Azure SQL database.

configure azure sql db

Here, you can see that the Azure SQL DB is at Basic. If you want to change it to "Standard", just click on the option and select the required sizes and click on "Apply".

configure azure sql db

Scaling up or down is usually quick. However, as per Microsoft, the below steps are performed:

  • New compute instance is created for the database
  • Connections are routed to the new instance

It is mainly during the switch over process there is a brief interruption. This may take longer based on activity on the database. One good thing to note is there will never be any data loss.

Benefits of scaling up the Azure SQL database based on business requirements

You can estimate the cost of the various compute tiers by using the Azure pricing link.

Take for example, if you are running the database on P15 compute tier it will cost you USD $23.8087/hour. For the entire month, it will be USD $17,380. If you have to run the database for the entire year on P15, it will cost you a fortune. But instead, you can scale up the database to a higher compute tier only based on your requirements.

Another good thing is you will be charged only on an hourly basis for the amount of time the database was running on the higher compute tier. Take for example, if the database was on P15 for 4 hours 25 minutes, you will be charged for 5 hours. It doesn’t matter if you ran the database for 10 minutes or 15 minutes on the higher compute tier, it will be rounded off to the hour.

Performing these steps using Azure Logic Apps

Azure Logics Apps is a cloud service that helps us to automate, orchestrate tasks, etc. You can refer to this tip to learn more about Azure logic apps. You can search for ‘Logic Apps’ in the portal and start with the process of designing the app.

azure logic apps

Once done, click on the "Add" option to start the process to create the app.

azure logic apps

This will take you to the below screen where you can fill in the required fields like name, etc. Enter the required fields and click on "Review + Create".

azure logic apps

Once you click on "Review + create" button, you will see this screen. Click on "Create".

azure logic apps

Logic App Deployment

Once you click on "Create", the deployment process will start and will finish in a few seconds. You will see this screen.

workflow overview

Click on "Go to resource", and you will see this screen. Click on "Logic app designer".

logic app designer

You will see this screen where you can make some selections.

logic app designer

Recurrence

From the screen, select a common trigger "Recurrence" as we need this process to be triggered every weekend. Once done, you will see a screen with options for you to select the required parameters. For this demo, as we want auto scaling to occur on weekends, we will make this selection. After reviewing, click on "New Step".

azure logic app recurrence

Execute a SQL Query

In the next step, we will perform the upgrade of the compute tier. Select the option as shown to "Execute a SQL query (V2)".

azure logic app execute query

Once you click on "Execute a SQL query (V2)", you will see this option where you need to enter the required details for server name and database name. We will be using below SQL query for upgrading the compute tier.

ALTER DATABASE [mo] MODIFY (SERVICE_OBJECTIVE = 'S0');
azure logic app execute query

Once done, click on "New step".

Delay

Once the Azure SQL DB is scaled up, we would like to leave it at the higher compute tier for around 5 hours. So, we will call have an action to set a delay timer. Search for "Delay" action as shown.

azure logic app delay

In the "Delay" step, you can set the counter in hours as shown. Once done, click on "New Step".

azure logic app delay

In the next step, you can scale down the Azure SQL database back to original tier. In our case, it is the "Basic" tier. You can use the similar step like what was performed for scaling up.

azure logic app execute query

Use below script for scaling down.

ALTER DATABASE [mo] MODIFY (SERVICE_OBJECTIVE = 'Basic');

Once this is done, save the workflows you created on the logic app designer.

logic app designer

Once the workflow is saved, you can initiate a run to validate that the design is working as expected. You may reduce the "Delay" time to something like 10 minutes instead of the original 5 hours.

Testing

In order to check the current compute tier, you can use this query

select Edition = DATABASEPROPERTYEX('mo', 'Edition'),
serviceobjective = DATABASEPROPERTYEX('mo', 'serviceobjective')

You can see the compute tier is basic.

query

Once you run the workflows, use the query above to validate that the compute tier is indeed changing. During the running phase, the workflow tasks will progress as shown.

workflow

You can see the tick mark across the workflow tasks. For the "Delay" action, you can see a timer. Also, once you check the status of the compute tier, you will see that it has changed to "Standard S0".

query

Checking status of all the tasks

You can check the status of the triggered jobs by clicking on the overview section of the Logic apps.

status of tasks

You can see that the trigger ran successfully and it completed in 5.01 minutes. You can click on the "Status" to view additional details. Here, you can see that each of the steps completed successfully.

azure run history

You can check the compute tier on the database to validate that the database is back on the original Basic compute tier.

query

With this, you can see how easily we can scale up and scale down an Azure SQL database using the Azure Logic apps.

Next Steps
  • In this tip, you saw how you could scale up an Azure SQL DB using the Azure portal
  • In this tip, you learned about using the Azure logic apps for auto scaling Azure SQL DB
  • In this tip, you saw how easily Azure logic apps can be used for automating routine tasks
  • You could try this demo on a trial version of Azure or any test Azure SQL database
  • To know more about Microsoft Azure, refer to this link on MSSQLTips


Last Updated: 2020-08-27


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Continuous database deployments with Azure DevOps

Process Blob Files Automatically using an Azure Function with Blob Trigger

Using Azure Blueprints to deploy Azure SQL Server and Database with Key Vault Secrets








get free sql tips
agree to terms


Learn more about SQL Server tools