SQL Server Agent for Azure SQL Database, Azure Elastic Database Pools & Azure Managed Instance

As you all know, the SQL Server agent is not available in the Azure SQL Database & Elastic database pool. When Microsoft introduced the Azure managed instance, the agent was also included in the Managed instance.
Now most vendors I work with, when they want to deploy their database, they also request a few jobs to be created which will execute various tasks at specific moments during the day. Today I will show you what option you have when you have to do those tasks with Azure SQL database, Azure Elastic Database Pools & Azure Managed Instance.

The main objective I would like to achieve is execute following query on a regular interval of 15 minutes.

declare @orderid int (select top 1 OrderID from dbo.Sales where payed = 0);
update dbo.Sales
set payed = 1
where OrderID = @orderid;

Scenario 1: Azure SQL Database & Elastic Database Pools

As you know, the SQL server agent is not available in Azure SQL Database & Elastic Database Pools, so we will have to use other components inside the azure stack to get this to work.

The option I choose and will show you is executing this command using Azure Data Factory

I start by creating an Azure data factory in my azure Portal, this is quite straightforward you can find a tutorial at https://docs.microsoft.com/en-us/azure/data-factory/quickstart-create-data-factory-portal

The thing I will need to create is called a pipeline in ADF, in the GUI on the Azure portal you can click on create pipeline.

After clicking this we get the following screen

When looking at the possible activities, we don’t see a SQLCMD, but under general we can find stored procedure.

As we are SQL people, turning this SQL command into a Stored Procedure is not the hardest thing to do.

CREATE PROCEDURE dbo.UpdateSales
AS
BEGIN
declare @orderid int (select top 1 OrderID from dbo.Sales where payed = 0);
update dbo.Sales
set payed = 1
where OrderID = @orderid;
END

With the stored procedure created, we can now start configuring the pipeline & stored procedure component.

We start by naming the pipeline to something more usefull then pipeline1, let’s call it Update Sales

We then drag the Stored Procedure into the pipeline to the right

We then give the stored procedure component a name, let’s call it exec UpdateSales, and then go to the SQL Account tab

We click on the +new button and we get following screen

We fill in the name, select the type, our Azure SQL Database & the Server name where the database is hosted on.

We choose the DBName & SQL Authentication and give in the SQL user which is going to execute the stored procedure. We test and click finish

After creating the Linked Service, we go to the tab of Stored Procedure and select the stored procedure we want to execute, which is dbo.UpdateSales in our case.

After this our Pipeline is ready, we press validate & publish all.

Now the thing we need to do is schedule this task to run every 15 minutes, in ADF this can be done using a trigger. We open up the pipeline and click on trigger.

And choose new/ edit, we click on new in the following screen.

We fill in the name & fill in the start date in UTC , recurrence every minute and then say every 15 minutes. We want this to run indefinitely so we choose no end date.

We click next to continue

We then click finish & publish the changes

We then wait for the trigger to finish in its schedule & check the Monitor tab on the left, as we can see the stored procedure executed successfully and ran in 8 seconds.

So it is possible to create recurring tasks like in an agent using Azure data factory.

But when presenting the solution to our vendor, he says we will deploy multiple new jobs every month & we only know how to do it to a SQL Server agent & we do not want to do double coding just because you want to use azure SQL databases. Is there another option you can present me where I do not have to change a thing & still be in the cloud? Yes, there is: Azure Managed Instance

Scenario 2: Azure Managed Instance

I start off by connecting to my Azure Managed instance

As you can see we have an agent available here, so we can just create the job with the SQL Command in there

We then schedule the job for every 15 minutes & this all works like an on premise SQL Server.

This means our vendor does not have to change a thing, which means the vendor is now happy & comfortable to move to the Cloud!

Conclusion

We can set up a sort of Agent using Azure Data Factory for Azure SQL Databases & Azure Elastic Database Pools. We can do this by using a pipeline & a trigger with the Stored Procedure Component. This is a workaround but works fine, however it will require some redevelopment from the vendor.
Using managed instances, we do not have to change anything to the code of the vendor and are able to run the jobs on the Managed instance in the cloud.

Next post will be on SSIS packaged and how to handle them with Azure SQL Databases, Azure Elastic Database Pools & Azure Managed instances.

Stay Tuned!

Advertisements
Tagged , , , , , ,

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

%d bloggers like this: