SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

 

 

 

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...