Azure Functions to Schedule SQL Azure operations

One of the things that I miss a lot when working on SQL Azure is the ability to schedule jobs, something that one normally does via SQL Server Agent when running on premises.

To execute scheduled task, on Azure, Microsoft recommends to use Azure Automation. While this is surely one way of solving the problem, I find it a little bit too complex for my needs. First of all I’m not a PowerShell fan, and Azure Automation is all about PowerShell. Secondly, I just need to schedule some SQL statements to be executed and I don’t really need all the other nice features that comes with Azure Automation. With Azure Automation you can automate pretty much *all* the resources available on Azure but my interest, for now, is only on SQL Azure. I need something simple. As much as simple as possible.

Azure Functions + Dapper are the answer. Azure Functions can be triggered via CRON settings, which means that a job scheduler can be easily built. 
Here’s an example of a CRON trigger (in function.json)

{
"bindings": [
{
"name": "myTimer",
"type": "timerTrigger",
"direction": "in",
"schedule": "0 30 4 * * *"
}
],
"disabled": false
}

CRON format is detailed here: Azure Function Timer Trigger. As a simple guideline, the format is:

{second} {minute} {hour} {day} {month} {day of the week}

In the sample above, it tells to Azure Function to be executed every day at 04.30. To turn such expression in something that can be more easily read, tools like

https://crontranslator.appspot.com/

are available online. If you use such tools, just keep in mind that many doesn’t support seconds, and then you have the remove them before using the tool.

Dapper is useful because make executing a query really a breeze:

using (var conn = new SqlConnection(_connectionString))
{
conn.Execute("<your query here>");
}

To use Dapper in Azure Function, a reference to its NuGet package has to be put in the project.json file

{
"frameworks": {
"net46": {
"dependencies": {
"Dapper": "1.50.2"
}
}
}
}

It’s also worth mentioning that Azure Functions can be called via HTTP or Web Hook and thus also via Azure Logic Apps or Slack. This means that complex workflows that automatically responds to certain events can be put in place very quickly.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.