Auto-Scaling Azure SQL DB

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)

trigpoint

The main workflow is shown below.

workflow

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')

s3ob

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.

finsed

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')

s0done

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.

 

 

2 thoughts on “Auto-Scaling Azure SQL DB

  1. Pingback: Automating Azure SQL Database Scaling – Curated SQL

  2. Pingback: Dew Drop - October 11, 2018 (#2821) - Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s