SQL Agent for Azure SQL Database? Not quite

The following post shows my preferred way to automate / schedule some code against my Azure SQL Database. No it is not PowerShell or Azure Runbooks but it is definitely my favourite way.

I have been using Azure Logic apps recently to build some workflows to gather data from external sources ultimately inserting it into a database for reporting, I then got thinking, how can this be useful for “DBA” based tasks? Let’s take a step back for a minute, what are logic apps? It is a technology that helps integrate apps, data, systems, and services across enterprises. Key parts of a logic app solution are connectors, triggers and actions.

I decided that I wanted to execute a stored procedure every 6 hours to capture wait statistics for my Azure SQL Database and log the information in a table.

This is what my workflow looks like.

Let’s discuss each step.

Recurrence is my trigger point, you can customise the time span as you desire, for my example I am telling the logic app to run every 6 hours.

I then want the logic app to connect to my Azure SQL Database (so this is an example of a connector – a SQL Database connector) and execute my stored procedure (action).

I then map the output of the stored procedure into a pre-built table in the form of an insert action.

Now I want to manually start the execution of this workflow. To do this you will need to click Run Trigger as shown below.

When successfully executed you will see the green ticks.

The ultimate test is to query the database for our waits information.

select * from [dbo].[waits]
order by WaitType

Hopefully you have found this blog post useful enough to start using this technique.