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.

logicappmain

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

getwaits

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

insertrow

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

runtrig

When successfully executed you will see the green ticks.

allok

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

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

datawaits

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

 

 

 

2 thoughts on “SQL Agent for Azure SQL Database? Not quite

  1. Pingback: Dew Drop - July 10, 2018 (#2762) - Morning Dew

  2. Pingback: Using Azure Logic Apps For Database Tasks – Curated SQL

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 )

w

Connecting to %s