Schedule Up/Downscale Azure SQL Database (TSQL)

Case
You recently showed how to up- or downscale my Azure SQL Database with Powershell code in an Automation Runbook, but is there a way to do this without Powershell?

Change the Tier of your SQL Azure DB

Solution
Changing the database Pricing Tier can also be done with some TSQL script. First we will show you how to do this in Sql Server Mangement Studio (SSMS) and then how you could also do this in Azure Data Factory (ADF).

A) SSMS
To change the Pricing Tier in SSMS you first need to connect SSMS to your Azure SQL Database and make sure to select the right database (not master) in your query window.

A1) Retrieve pricing tier
First a query to retrieve the Pricing Tier (Edition, Server Objective and Max Size). This can be done with a hardcode name or by using DB_NAME() within the DATABASEPROPERTYEX() command. However since you cannot retrieve the data from database X when connected to database Y (it will return NULL) the second options is the easiest.

-- Hardcoded Database Name
SELECT DATABASEPROPERTYEX('bitools2', 'Edition') as Edition
,  DATABASEPROPERTYEX('bitools2', 'ServiceObjective') as ServiceObjective
,  DATABASEPROPERTYEX('bitools2', 'MaxSizeInBytes') as MaxSizeInBytes

-- Get current Database Name
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Edition') as Edition
,  DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') as ServiceObjective
,  DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') as MaxSizeInBytes

Retrieve Database Pricing Tier

A2) Change pricing tier
The next step is to change the Pricing Tier with an ALTER DATABASE command in SSMS. If you go to the portal right after executing the ALTER statement you will see a message Updating database pricing tier. When executing the command in SSMS it immediately shows that it is completed, however it takes a few moments to actually finish the command.

-- Change Princing Tier to Basic (only when size < 2GB)
ALTER DATABASE [bitools2] MODIFY (EDITION ='Basic');

-- Change Pricing Tier to Standard S1 with size set to 250GB
ALTER DATABASE [bitools2] MODIFY (EDITION ='Standard', SERVICE_OBJECTIVE = 'S1', MAXSIZE = 250 GB);

-- Change Pricing Tier to Standard S1 (withoud mentioning edition and size)
ALTER DATABASE [bitools2] MODIFY (SERVICE_OBJECTIVE = 'S1');

-- Change Pricing Tier to lowest Standard (S0)
ALTER DATABASE [bitools2] MODIFY (EDITION ='Standard');

Pricing Tier changes after execution the alter script

Note: According to the documentation you could also use CURRENT (instead of the database name) to alter the current database. However that failed on my laptop (SSMS 18.4). Please write your suggestions in the comments.

The source database '' does not exist.

B) Azure Data Factory
You could also execute this script in ADF to upscale your Azure SQL Database before the ETL (or ELT) starts and then downscale it afterwards. For this example we will add these ADF activities in our existing stage pipeline from a previous blog post to upscale the sink database (our stage database).

Stage multiple tables in a foreach loop

B1) Upscale DB
First we will add a Stored Procedure activity which executes the ALTER script from above to upscale the stage database.

• Add the stored procedure activity in front of the existing Lookup activity
• Give it a descriptive name: Upscale Stage DB
• Make sure to use the same Linked service connection as your sink (Stage database)
• Use 'sp_execute' for the Stored Procedure name
• Add a new string parameter named 'stmt' and add the ALTER query as value (change DB name)

-- Change Pricing Tier to Standard S1 with size set to 250GB
ALTER DATABASE [Stage] MODIFY (EDITION ='Standard', SERVICE_OBJECTIVE = 'S1', MAXSIZE = 250 GB);

Upscale DB in ADF

B2) Wait for upscale to be finished
Now you want to wait until the database has been upscaled before you start the ETL because during the upscale some queries will be cancelled. For this we will execute a query on the MASTER database (because queries will be cancelled on the Stage DB). This query will check whether the view sys.dm_operation_status still has an active ALTER query on our Stage DB.

• Add a Stored Procedure Activity between the Upscale and the Lookup
• Give it a descriptive name: Wait for upscale
• Use a connection to the master database as Linked Service
• Use 'sp_execute' for the Stored Procedure name
• Add a new string parameter named 'stmt' and add the query below as value (change DB name)
• Connect all activities in the pipeline: Upscale => Wait => Lookup

-- Wait until pricing tier has changed (retry ever 30 second)
WHILE EXISTS (
    SELECT  * 
    FROM    sys.dm_operation_status
    WHERE   operation = 'ALTER DATABASE' -- Only look for ALTER operations
    AND     state = 1                    -- which are still in progress
    AND     major_resource_id = 'Stage'  -- on our database
    )
BEGIN
    WAITFOR DELAY '00:00:30';
END;

Bijschrift toevoegen

B3) Downscale DB
After the foreach loop is done we can add an other Stored Procedure activity to downscale the Stage database.

• Add the stored procedure activity after the existing ForEach activity
• Give it a descriptive name: Downscale Stage DB
• Make sure to use the same Linked service connection as your sink (Stage database)
• Use 'sp_execute' for the Stored Procedure name
• Add a new string parameter named 'stmt' and add the ALTER query as value (change DB name)
• Connect the ForEach activity to the Downscale Acticity

-- Change Princing Tier to Basic (only when size < 2GB)
ALTER DATABASE [Stage] MODIFY (EDITION ='Basic');

Upscale, Wait, ETL and Downscale

Note: A big downside of using this method in Azure Data Factory is that you need a lot of permissions on your database. Which in most cases you don't want do give to your ETL account. In a next blog post we will show you an other alternative: Rest API.

Conclusion
In this post you learned how to change the database Pricing Tier with some simple TSQL script instead of a Powershell. Probably most suitable during development to temporarily upscale your DB to make the queries faster. But it could also be very useful to integrate the scaling in your ETL (or ELT) proces to minimize the time for a more expensive/faster database. In a next blog post we will introduce you to the Rest API which is very suitable for ADF.