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

Watch used for timing

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)

 

3 thoughts on “Azure: Changing Azure SQL Database Service Objective from T-SQL Commands”

  1. 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');

    1. 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.

    2. Also worth noting that the Script Activity is now probably a better choice than the Lookup for this one, as it supports parameters.

Leave a Reply

Your email address will not be published. Required fields are marked *