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.
Pingback: Automating Azure SQL Database Scaling – Curated SQL
Pingback: Dew Drop - October 11, 2018 (#2821) - Morning Dew
Hope you don’t mind but I used your SP’s on an Article for my blog https://www.theazurenoob.com/post/azure-sql-db-automated-scheduled-resize
LikeLike
What if you want to change the plan, and not just the tier?
LikeLike
hello, when you say plan do you mean the edition? i.e. from standard to premium? If so, code is similar.
ALTER DATABASE [test] MODIFY
(EDITION = ‘Premium’, MAXSIZE = 500 GB, SERVICE_OBJECTIVE = ‘P1’);
GO
LikeLike
Can we resize edtu in elastic pool based on edtu usage % using above method?
LikeLike
no I dont think so because I used TSQL. Elastic pools you can only use the portal, Powershell or Azure CLI to scale up
LikeLike