SQL Server Agent for Azure SQL DB?

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/)

cloudlinks

Currently I have a database in Azure that is S0.

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

S0database

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.

agentjob1

All The code is:

ALTER PROCEDURE dbo.ScaleUp

AS

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

This is the result.

newversion

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

8pm
Executed successfully.

s0version1

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

 

 

1 thought on “SQL Server Agent for Azure SQL DB?

  1. Pingback: Dew Drop – March 3, 2020 (#3145) | Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s