When I was presenting my Azure SQL Database session at DataRelay (used to be SQLRelay) I was asked (over coffee) about auto scaling capabilities. Quite simply there is nothing out of the box to achieve this. The idea of auto scaling would be good where you would need a burst to fulfill higher demand in terms of workload for a time duration, you know, something like “end of the day, Friday night sale” for your database.

Classically you would probably go down the PowerShell route via a runbook, but I am different.

Enter – Azure Logic Apps.

You would set the recurrence as your trigger point, below shows at 1130pm for a Friday every 4 weeks start this workflow. (This is just an example)

The main workflow is shown below.

Once the trigger point (first step) is passed it executes the first stored procedure where it is one line of code to scale up my database from S0 to S3.

CREATE PROCEDURE dbo.ScaleUp
AS
ALTER DATABASE [FastDB] MODIFY (SERVICE_OBJECTIVE = 'S3');

About 30 seconds later, I check the performance level using the code below, clearly it moves to S3.

 --checking details
SELECT Edition = DATABASEPROPERTYEX('FastDB', 'Edition'),
       ServiceObjective = DATABASEPROPERTYEX('FastDB', 'ServiceObjective')

I want this performance tier for x amount of time, so define x as your delay, for my example I just did a couple of minutes but it can be hours.

Then once the delay expires the logic app will issue my scale down request because I want to save money now and have no need for the higher levels.

FYI – the scale down code:

CREATE PROCEDURE dbo.ScaleDown
AS
ALTER DATABASE [FastDB] MODIFY (SERVICE_OBJECTIVE = 'S0');

--checking details
SELECT Edition = DATABASEPROPERTYEX('FastDB', 'Edition'),
       ServiceObjective = DATABASEPROPERTYEX('FastDB', 'ServiceObjective')

Cool right? Again with a scaling operation it creates a replica of the original database at the new level and then switches connections over to the replica. No data is lost during this process. Connections to the database are disabled, so some transactions in flight may be rolled back.