Azure cloud cost optimisation: Autoscaling Azure SQL database by Logic Apps

Azure cloud gives you a lot of possibilities of adjusting used services to your needs. It means that you are able to adjust the costs of used resources. In the cloud, you are able to use a lot of auto-tuning options that are working right out of the box. Together with that, there are plenty of possibilities to do in the way that you will have the whole control over the process.

Today I will focus on Azure SQL database.

It is very other situation that you see at the following graph:

This chart is something typical for an application used in one time-zone when most of users activity are during a day. It means that you can reduce the power of your database during the night. In some cases, you could try to turn it off totally.

It is very important that in Azure cloud you are paying for real usage of service. In the case of Azure SQL DTU-based purchasing model is used. DTU is Database Transaction Unit – a virtual measure of CPU, memory, data I/O and transaction log I/O combined into one value. For people that see it for the first time, it is a bit difficult to move from classic SQL database sizing into DTU. In the beginning, it can be a bit difficult to select the correct size of DTU for your application scale. In case of any issues with performance, you can very easily adjust the size of DTU assigned to your databases later.

And today I would like to show you how you can automate this process to support changes to the mentioned workload pattern. I will use Azure Logic Apps in this example. You can use different Azure services or even add auto-adjustment to your application.

I assume that you already have provisioned Azure SQL Database in your Azure environment by using DTU tier. Then you can use ALTER DATABASE statement for updating your database pricing tier. For Azure SQL the syntax looks like this:

-- Azure SQL Database Syntax
ALTER DATABASE { database_name }
{
  MODIFY NAME =new_database_name
  | MODIFY ( <edition_options> [, ... n] )
  | SET { <option_spec> [ ,... n ] }
  | ADD SECONDARY ON SERVER <partner_server_name>
    [WITH (\<add-secondary-option>::= [, ... n] ) ]
  | REMOVE SECONDARY ON SERVER <partner_server_name>
  | FAILOVER
  | FORCE_FAILOVER_ALLOW_DATA_LOSS
}

<edition_options> ::=
{
  MAXSIZE = { 100 MB | 250 MB | 500 MB | 1 … 1024 … 4096 GB }
  | EDITION = { 'basic' | 'standard' | 'premium' | 'premiumrs' }
  | SERVICE_OBJECTIVE =
  {  'S0' | 'S1' | 'S2' | 'S3'| 'S4'| 'S6'| 'S7'| 'S9'| 'S12' |
    | 'P1' | 'P2' | 'P4'| 'P6' | 'P11'  | 'P15' |
    | 'PRS1' | 'PRS2' | 'PRS4' | 'PRS6' |
    | { ELASTIC_POOL (name = <elastic_pool_name>) }
  }
}

<add-secondary-option> ::=
{
  ALLOW_CONNECTIONS = { ALL | NO }
  | SERVICE_OBJECTIVE =
    {  'S0' | 'S1' | 'S2' | 'S3' | 'S4'| 'S6'| 'S7'| 'S9'| 'S12' |
    | 'P1' | 'P2' | 'P4'| 'P6' | 'P11' | 'P15' |
    | 'PRS1' | 'PRS2' | 'PRS4' | 'PRS6' |
    | { ELASTIC_POOL ( name = <elastic_pool_name>) }
  }
}

[;]

And for example, if I would like to set the size of the database to 200 DTUs then I need to run the following SQL statement:


ALTER DATABASE [DBName] MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S4')

When we look at the usage graph we can see that our application is being used between 7:00 and 20:00. Moreover, you can see that our resource is overprovisioned. But this topic for another article.

In this case, I want to divide the day into two periods of time and adjust database performance to usage:

between 7:00 and 20:00 – in those hours our application should work on the full power – let’s assume 200 DTUs
between 20:00 and 7:00 – here our application should be operational – let’s reduce price tier to 10 DTUs

For that kind of automation, we can use Logic Apps and create a flow that will start ant 7:00 (using Recurrence block):

And then execute the SQL query at the database that you want to adjust:

The whole flow has only two steps:

Of course, we need to create the second flow and adjust is parameters to decrease the size of the SQL database.

And that is all. With those two very simple Logic Apps you can dynamically scale up and down your SQL database and adjust it to real usage. In inactive usage periods reduce the size as much as it is possible and when the application is being used – increase its size.

In this case, when we are adjusting the size between 200 DTUs and 10 DTUs, we can decrease the cost of the database from 294,37 USD/month to 166,01 USD/month. It is over 43% cost reduction!

Please remember that in your case the parameters will be definitely different. I also didn’t include in the article information about the storage. Sometimes the lowest size of the database is limited due to the size of the data. And also I didn’t mention other options cost optimization like elastic pools, serverless databases, reserved capacity or Azure Advisor.