Auto-scaling your Azure SQL database with Logic Apps

I think that you know that I’m a huge fan of using Azure SQL database for your workloads in the cloud. With Azure SQL you can choose how many CPU you want to use (DTU), how much storage you need, and then you can dynamically change these parameters any time when needed.

Obviously, in the cloud world an infinite power is also directly related to costs. As an example, imagine to have a workload that for 3 hours/day it needs a terrible power, while on the other 6 hours/day it’s poorly used. We’ll use this database for 5 days per week and for 4 weeks every month. For handling that, you provision a Premium P15 Azure SQL database (4000 DTUs, 4096 GB storage per DB) and this database will be used for 180 hours a month (about 9 working hours a day or so). This will cost you about more than 3200€/month (obviously, I think that you will never use the terrible power of a P15 database, but let’s imagine that).

As said before, do you need this power on all these hours? Or maybe you can scale up and down your database accordingly? In this example we could have a Premium P15 for 3 hours/day and maybe a Standard S2 for the other 6 hours/day. In this case, you will pay about 1088€ + 10€ = 1098€. This is about 1/3 of the previous price without scaling!

There are different ways to do “tricks” for auto-scaling your database accordingly to your users needs (for example like creating a Runbook and running a Powershell script from it) but here I want to suggest a quick and easy way for implementing a scaling like what I’ve described here: using an Azure Logic App that connects to your Azure SQL Database and modifies the tier accordingly.

With Azure SQL database you can use the following query for modifying the database tier:

ALTER DATABASE NameOfYourDatabase MODIFY (EDITION = 'Standard', SERVICE_OBJECTIVE = 'S1');

This query scales the Azure SQL database to a Standard S1 instance. You can then check if the scaling is performed by using the following query (here sddemodb is the name of my database):

SELECT Edition = DATABASEPROPERTYEX('sddemodb', 'Edition'),
ServiceObjective = DATABASEPROPERTYEX('sddemodb', 'ServiceObjective')

If you want to scale up your database to the Premium P15 instance, just execute the following query:

ALTER DATABASE sddemodb MODIFY (EDITION = 'Premium', SERVICE_OBJECTIVE = 'P15');

and after a bit of time (scaling up requires time) your database will run on the new tier:

For automating this, let’s create a Logic App that performs the scale up of our database. The Logic App will start from a recurrence trigger (timer-based) and the steps will be as follows:

At 9 on every working day, our Azure SQL database is scaled up to a Premium P15 instance and an email is sent to the administrator:

At the same way, we have another Azure Logic App that handles the scale down (in this sample, it will occour every day at 13) to a Standard S2 instance:

That’s all. Very easy to implement, with a very flexible low-code approach and your money will be saved 🙂