I always wanted a way to schedule commands within Azure SQL Database. Personally, for me, the go to standard is the functionality of SQL Server Agent. Obviously, this is not possible out of the box but I have been using an on-premises SQL Server instance (within a specific vnet that is mapped to the logical Azure SQL server) with a linked server connection setup (with dedicated logins) to Azure SQL Database to run some code at a specific time to scale up (and down) my database dependent on peak hours.

Once the linked server is created – test connection.

(use the following as a guide https://www.sqlskills.com/blogs/tim/how-to-create-a-linked-server-to-azure-sql-database-via-sql-server-management-studio/)

Currently I have a database in Azure that is S0.

--checking details
SELECT Edition = DATABASEPROPERTYEX('DB1', 'Edition'),
       ServiceObjective = DATABASEPROPERTYEX('DB1', 'ServiceObjective')

I have created a SQL agent job that calls a stored procedure in Azure SQL DB to scale up. The point of this job, is that so I can schedule it.

All The code is:

ALTER PROCEDURE dbo.ScaleUp

AS

ALTER DATABASE [] MODIFY (SERVICE_OBJECTIVE = 'S3');

This is the result.

So, I know after 8pm I do not need this tier, so I schedule a scale down from S3 to S0 @ 8pm. The scale down stored proc is  as simple as:

ALTER PROCEDURE dbo.Scaledown

AS

ALTER DATABASE [yourdb]  MODIFY (SERVICE_OBJECTIVE = 'S0');

Executed successfully.

There are more “modern ways” to achieve a similar goal, Azure functions, Logic apps etc but I wanted to share this classic approach.