Azure: Changing Azure SQL Database Service Objective from T-SQL Commands

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)

-- 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;

2021-03-02