info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Use Azure Function with Timer Trigger to call an Azure SQL Database Stored Procedure
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-10-19








Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. You can write just the code you need for the problem at hand, without worrying about a whole application or the infrastructure to run it. Functions can make development even more productive, and you can use your development language of choice, such as C#, F#, Node.js, Java, or PHP.

Azure Functions lets you develop serverless applications that scale to meet demand and pay only for the resources you consume. Use the programming language of your choice, and don’t worry about servers or infrastructure. Azure Functions provides a fully managed compute platform with high reliability and security.

Function App
- Write any function in minutes – whether to run a simple job that cleans up a database or build a more complex architecture. Creating functions is easier than ever before, whatever your chosen OS, platform, or development method.

TimerTrigger
- Execute cleanup or other batch tasks on a predefined schedule. For an example, see Create a function triggered by a timer.

Functions is a great solution for processing data, integrating systems, working with the internet-of-things (IoT), and building simple APIs and microservices. Consider Functions for tasks like image or order processing, file maintenance, or for any tasks that you want to run on a schedule.

Source: docs.microsoft.com




Solution: Azure Function - Timer-Based Processing







run.csx code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public static void Run(TimerInfo myTimer, ILogger log)
{
log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");


using (var con = new SqlConnection("Server=tcp:sql01nycdv.database.windows.net,1433;Initial Catalog=Logs;Persist Security Info=False;User ID=;Password=;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))

{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "TestSP";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
log.LogInformation(ex.Message);

}

}

}


function.json code:

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


project.json code:

{
"frameworks": {
"net46": {
"dependencies": {
"System.Data.SqlClient": "4.3.0",
"Microsoft.WindowsAzure.ConfigurationManager": "3.2.3"
}
}
}
}


Azure Portal:





Resources:

Azure Functions

Use Azure Functions to connect to an Azure SQL Database