Rajendra Gupta
Set up Azure Function App

Azure Functions for Azure SQL Database

March 10, 2022 by

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.

consumption, premium and dedicated hosting

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.

Set up Azure Function App

Click on Create in the function app page.

Function app

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

Subscription and instance details

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.

Runtime stack, versiom

For this article, we use .Net language with version 3.1. The .Net version 6 is in preview. Choose .net for deployment

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.

Choose plan type

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.

Enable application insights

In review + create the page, review the configurations, and starts function app deployment. Deployment progress Once the deployment of the resources is completed, you can view the resources and their types that are deployed.

View deployed resources

Click on the Go to resource button and view the status, URL of the function app. Function app URL

Create a function

In the function, apps click on functions and create to deploy a new function.

Create a function

It opens the following page.

Development environment, template

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.

Development environment

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.

Select a template or trigger

For this article, we use the Timer trigger. Once we select it, it opens configurations for the function name and its schedule. Timer trigger

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.

CRON schedule

Click on create, deploy the function, and open the following dashboard.

deploys the function

Click on the Code+Test in the Developer options. You get a code window with sample code in the run.csx file.

Click on Code + Test

Before we move forward, open your Azure SQL Database dashboard in the Azure portal and click on show connection strings. Connection string

We also created a stored procedure [TestSP] that returns the row count from the view [SalesLT].[vProductAndDescription].

Note down the connecting string and enter the password for connecting to Azure SQL Database.

ADO.net connection string 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.

Replace code

Click on Save.

Save code

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.

Click on Run

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.

View expected output

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.

Azure function execution Monitor

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.

Integration

You can click on the timer to view the CRON schedule or edit if required.

view the CRON schedule

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.

Azure function app dashboard graphs

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.

Rajendra Gupta
2,498 Views