When I'm building Azure Data Factory pipelines, I often want to rescale, i.e. change the Service Level Objective (SLO) of Azure SQL Databases before and after processing. For example: I might have a database that sits at S0 or S1 all day long when not being processed (to allows for the odd adhoc query), but I want it at S6 to do overnight ingestion of data and loading of analytic data models. Then I want it to go back to what it was before.
This can make a huge difference to the cost of these databases, when they are used intensely but intermittently.
While it's possible to do this from PowerShell, etc. I've found that the easiest (by far) is to just execute the required commands from T-SQL.
Stored Procedure Approach?
What I really wanted to do, was to put a stored procedure in each database, that can be used to scale that same database. I could have then included that procedure in our SDU Tools.
But you can't do that. While the scaling would work, the stored procedure would always return an error because it would be terminated when the rescaling occurs.
The way to avoid being terminated, is to query the master database instead. The annoying part there, is that with Azure SQL Database, you aren't allowed to put any user objects in the master database. So while it would be a perfect location to hold a stored procedure for changing the SLO of a database.
Just a SQL Batch
In the end, the easiest is to just send the required SQL batch to the master database. In ADF, I can do that with a single Lookup activity. (That's the way you just execute a SQL statement).
It's annoying that the Lookup activity only supports parameters when you're calling a stored procedure. It doesn't support parameters for SQL query batches. So I have to just put the values right into the code.
Regardless, if you want to do this, below is an example of T-SQL code that will do that. You'll need to execute it against the master database, and as a server administrator.
(Note: code might wrap)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
-- Change service level objective for a database -- Must be connected to the master database or disconnection will occur -- Must be a server administrator DECLARE @DatabaseName sysname = 'beanperfectiondatawarehouse'; DECLARE @ServiceLevelObjective sysname = 'S0'; DECLARE @DelaySecondsPerCheck int = 10; DECLARE @MaximumDelaySeconds int = 600; SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @SQL nvarchar(max); DECLARE @QUOTE nchar(1) = NCHAR(39); DECLARE @DelaySoFar int = 0; DECLARE @Duration varchar(8) = CASE WHEN @DelaySecondsPerCheck BETWEEN 0 AND 86399 THEN LEFT(CONVERT(varchar(20), DATEADD(second, @DelaySecondsPerCheck, CAST(CAST(SYSDATETIME() AS date) AS datetime)), 14), 8) END; IF @Duration IS NULL BEGIN RAISERROR('Invalid duration value', 16, 1); END; DECLARE @CurrentServiceLevelObjective sysname = ( SELECT [service_objective] FROM sys.databases AS d INNER JOIN sys.database_service_objectives AS dso ON dso.database_id = d.database_id WHERE d.[name] = @DatabaseName ); IF @CurrentServiceLevelObjective IS NULL BEGIN RAISERROR('Cannot obtain current service level objective. Was the database name correct ?', 16, 1); END ELSE BEGIN IF @CurrentServiceLevelObjective = @ServiceLevelObjective BEGIN PRINT 'Already at the required service level objective'; END ELSE BEGIN SET @SQL = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' MODIFY (SERVICE_OBJECTIVE = ' + @QUOTE + @ServiceLevelObjective + @QUOTE + N');'; EXEC sp_executesql @SQL; WAITFOR DELAY @Duration; SET @DelaySoFar = @DelaySecondsPerCheck; WHILE (SELECT [service_objective] FROM sys.databases AS d INNER JOIN sys.database_service_objectives AS dso ON dso.database_id = d.database_id WHERE d.[name] = @DatabaseName) <> @ServiceLevelObjective AND @DelaySoFar <= @MaximumDelaySeconds BEGIN WAITFOR DELAY @Duration; SET @DelaySoFar += @DelaySecondsPerCheck; IF @DelaySoFar > @MaximumDelaySeconds BEGIN RAISERROR('Maximum delay for changing service level objective exceeded.', 16, 1); END; END; END; END; |
Greg, this is really well written T-SQL code thanks for sharing. One thing to point out to those implementing in ADF lookup activity is that currently, a lookup activity needs to return a result set or it will error out. I just added this at the bottom of code to make it work from ADF:
SELECT Edition = DATABASEPROPERTYEX('DW', 'EDITION'),
ServiceObjective = DATABASEPROPERTYEX('DW', 'ServiceObjective'),
MaxSizeInBytes = DATABASEPROPERTYEX('DW', 'MaxSizeInBytes');
Glad it's helpful. Yes, that's a good addition. I really wish we had the ability to either create an object in master to hold this type of code as a proc, or have some way to execute a proc in the DB, where the proc survives SLO changes.
Also worth noting that the Script Activity is now probably a better choice than the Lookup for this one, as it supports parameters.