As a Platform as a Service (PaaS) service, Azure SQL Database enables developers to deploy SQL Database in Azure Cloud without managing the infrastructure. We use SQL Server Agent to schedule jobs to run at a specific schedule in an on-prem SQL instance. However, Azure DB does not have agent functionality.
There are multiple ways to schedule job or batch processes in the Cloud. You can explore the Azure automation series for executing scripts using Azure Logic apps and automation runbooks.
This article focuses on the Azure functions for scheduling a job for Azure SQL Database.
Azure Function overview
The azure function allows you to run automation for event-triggered code using fewer coding efforts without maintaining infrastructure in a serverless model. You can start deploying your code in up-to-date Azure cloud infrastructure resources.
- Azure functions support languages like C#, F#, JavaScript, node.js, PowerShell.
- You can use Azure Functions for any configured trigger – HTTPTrigger, TimerTrigger, QueueTrigger.
- It supports deployment options such as tool-based (Visual Studio Code, Visual Studio), App Service managed (CI/CD pipelines, Container), External pipelines (Azure pipelines, GitHub actions).
- It supports cross-platform local development and hosting on both Windows and Linux.
- We can monitor Azure functions using Application Insights and Azure Monitor.
Currently, we have three hosting plans for Azure functions.
- Consumption: The consumption plan is a fully serverless hosting option and supports dynamic scale up and down resources based on the incoming events. Azure bills you for the compute resources as per the number of executions, execution time, and memory used during the function execution. It has a limitation that the function execution gets timed out after a specific period
- Premium: The premium hosting plan offers unlimited execution duration with 60 minutes guarantee, Virtual network connectivity, predictable pricing, Avoid cold starts with perpetually warm instances. It bills as per the number of core seconds and memory allocated across instances
- Dedicated: In this plan, you define a set of computing resources for execution
You can compare these consumption, premium and dedicated hosting in the following table.
Reference: Microsoft docs
Configure Set up Azure Function App
To use the Azure function, we need to deploy the Function app in Azure. Log in to the Azure portal with your credentials and search – Function app.
Click on Create in the function app page.
Basic page configuration
On the basics page, select your subscription and create a new resource group.
Each function app requires a unique name. Therefore, please enter the name. The Azure function name has a suffix as .azurewebsites.net
In the publish section, choose either code or docker container. In the runtime stack, you can choose languages such as .Net, Node.js, Python, Java, PowerShell core.
For this article, we use .Net language with version 3.1. The .Net version 6 is in preview.
Hosting:
The hosting configurations require a general-purpose Azure storage account for blobs, queues, and table storage.
The setup automatically specifies a new name for the storage account. However, you can choose your custom storage account name.
Choose Operating system as Windows and Plan type as Consumption that we discussed earlier in this article.
We can configure the Azure monitor application insights for monitoring the performance of the Azure functions. However, for the demo purpose, we do not require it.
In review + create the page, review the configurations, and starts function app deployment.
Once the deployment of the resources is completed, you can view the resources and their types that are deployed.
Click on the Go to resource button and view the status, URL of the function app.
Create a function
In the function, apps click on functions and create to deploy a new function.
It opens the following page.
Development environment: As discussed earlier, you can use various deployment tools for Azure functions. By default, it uses the option – Develop in a portal. You can choose from any of the following options as per your requirement. However, you need to configure Visual Studio or VS Code for connecting to Azure functions.
Note: You can refer to hyperlink Develop Azure Functions by using Visual Studio Code.
Select a template
The template specifies the type of event that will invoke the function. The supported templates are HTTP trigger, Timer trigger, Azure Queue Storage trigger, Azure Blob storage trigger etc.
For this article, we use the Timer trigger. Once we select it, it opens configurations for the function name and its schedule.
The timer schedule is in the CRON format. By default, it shows every 5 minutes. The format of the schedule is as below.
{second} {minute} {hour} {day} {month} {day-of-week}
For this article, I set it to occur every 30 minutes.
Click on create, deploy the function, and open the following dashboard.
Click on the Code+Test in the Developer options. You get a code window with sample code in the run.csx file.
Before we move forward, open your Azure SQL Database dashboard in the Azure portal and click on show connection strings.
We also created a stored procedure [TestSP] that returns the row count from the view [SalesLT].[vProductAndDescription].
|
1 2 3 4 5 6 7 8 9 10 |
Create Procedure TestSP as begin SELECT count(*) as NumberofRecords FROM [SalesLT].[vProductAndDescription] end Exec TestSP |
Note down the connecting string and enter the password for connecting to Azure SQL Database.
Now, go back to the Azure function code page and replace the code with the following. This code runs the stored procedure [TestSP] and displays the result on the function output console.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
using System; using System.Data.SqlClient; public static void Run(TimerInfo myTimer, ILogger log) { string SqlConnectionString ="place your connection string;"; SqlConnection conn = new SqlConnection(SqlConnectionString); conn.Open(); SqlCommand command = new SqlCommand("TestSP", conn); command.CommandType = System.Data.CommandType.StoredProcedure; using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read()) { log.LogInformation($"Successfully Executed Azure Function at: {DateTime.Now}"); log.LogInformation($"Row count processed: "+ reader.GetInt32(0).ToString()); } } conn.Close(); } |
Click on Save.
To test the Azure function, click on Test/Run. If we have any parameters to test HTTP requests, we can specify them here. In our case, we use a timer trigger. Therefore, click directly on Run at the bottom.
As highlighted below, it returns the row count as 1746, as expected in my sample stored procedure. This time we have done manual execution. However, if you do not disable the function, it is automatically executed every 30 minutes per our CRON schedule.
Azure function execution Monitor
Click on Monitor in the Azure function console, and here, you can see the invocations and logs. The dashboard shows the last 30 days of successful and failure function execution, and you can also note down the execution time in the table.
If you have enabled Application insights, you can also run the query in application insights from here.
Integration
The integration tab gives a graphical workflow of the Azure function execution. For example, in our demo, we have a timer trigger that invokes the Azure function. The timer trigger does not require user input, and we also did not configure any specific output format.
As shown below, We have a Timer trigger named as myTimer that invokes the Azure function AzureSQLTrigger. If you have a complex Azure function with multiple inputs, outputs, or triggers, it is nice to visualize the execution of your function.
You can click on the timer to view the CRON schedule or edit if required.
Azure function app dashboard graphs
The Azure function app gives a graphical view of several metrics for function executions. These graphs show the total execution count, successful execution, and failed execution count for configured Azure functions.
Conclusion
This article explored Azure functions for scheduling query execution at a specific schedule similar to the SQL Server Agent. The Azure functions are event-based serverless options. Therefore, you can configure the events, functions based on your requirement for running queries on Azure SQL Databases.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023