Schedule Azure SQL DB Processes using Azure Functions

Problem

Azure SQL database is a fully managed Platform as a Service (PaaS) solution, that offers the ability to create, build and migrate native cloud applications at an affordable price. But what if there is a need to schedule a batch process, like using a SQL Server Agent Job for an on-premises instance? There are multiple options to achieve scheduling in the cloud, with each having pros and cons. In this tutorial we will look at the easiest way to achieve scheduling using a Basic Tier database?

Solution

If you are using a basic tier Azure SQL DB, one of the easiest ways to schedule a process is using an Azure Function with a time trigger. An Azure Function is a serverless option to run code in multiple languages, to create cloud native functions without worrying about the underlying infrastructure. Azure functions can be executing by choosing multiple triggers, but for our needs we will use a time trigger, so the function can be executed at set times.

How does this work?

The basic idea is quite simple, we can create a Stored Procedure and then using code of our preference to call the stored procedure from an Azure Function. The function can be scheduled to run at certain times and any code we have in the stored procedure will be executed.

What is needed?

We will use the latest available Azure function runtime (at the time of writing this tutorial) which is Azure function runtime 3.x. Also, it is requires having an Azure SQL Database, you can check this tip on how to create one. For simplicity we will using the Azure portal to create the function and will be using C# (.Net), but you can use any language you want for your function.

Azure Function Example

I already have an Azure SQL Database in my Azure account which I use for testing as shown below.

I will create a simple stored procedure that will "simulate" a batch process, it will just return a random number that we will be use as our "processed rows."

CREATE PROCEDURE SimulateBatchProcess
AS
BEGIN    
    SET NOCOUNT ON
    SELECT cast(RAND()*1024 as int
END
GO

That is everything we need from Azure SQL Database, so now let’s setup the Azure Function.

Set up Azure Function App

The first step is to go to the Azure Portal and search for Function, which will return the following. Then select Function App.

In the new form, select Create to create a new function.

In the new window, enter the following:

1. The resource group to use.
2. Put a meaningful App name.
3. For Publish, we will use the Code option, and then for Runtime stack, select the language you want to use, for our example, we will use .NET (C#) since this can be programmed in the portal.

Then, click Next : Hosting >.

In the next window, enter the following information:

1. The Storage account to use, if you do not have any, you can select a new one.
2. For Operating system, select the one you want, I will choose Windows.
3. For Plan type, and since we want the cheapest one, we will select the Serverless option. You can check the pricing options here.

Then click on Next : Monitoring >.

I do not need application insights, so I select No. At this point, you can click on the Review + Create button.

Review that everything is OK and click Create.

The service will be created in a couple of minutes:

Once done, click on Go to resource:

This is the main page for your App service, you can see that you can stop or restart the service if you need to. You can see that you have the function’s URL as well, but for this type of function we will not need it, it is just for reference.

The next step is to create the actual function that will run the stored procedure.

Create the Time Trigger Function

In the main page go to Functions.

In the new page, select the Add option.

In the new window, select the following:

1. For our example, we will use the Develop in portal option, so we can add the code directly using Azure portal. This option is only available for .NET runtime.
2. Select the Time trigger option. This is the most important option as this will allow the function to run on a scheduled basis.

After you select the option in step 2, scroll down on the window.

In the Template details, put the following information:

1. Put a descriptive name for the function.
2. Schedule is set using CRON format, the default when you create a function is 5 minutes, but for our example I have set it to 1 minute. You can read more about CRON here.

Then click Add.

After the function is created, we must enter the function code, so go to Code + Test option.

Note: you can only modify code if the Function App is running.

You will see a template code in the run.csx file, remove the contents.

Using the System.Data.SQLClient assembly, we will use the following code. Just remember to change your connection string and your stored procedure name. This code will run the stored procedure we created earlier and print in the console the execution time and number of rows processed (which is a result set from the stored procedure).

using System
using System.Data.SqlClient
 
public static void Run(TimerInfo myTimer, ILogger log)
{
    string SqlConnectionString ="Server=tcp:<Myserver>,1433;Initial Catalog=<myDB>;Persist Security Info=False;User ID=<Username>;Password=<pwd>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
 
    SqlConnection conn = new SqlConnection(SqlConnectionString
    conn.Open
 
    SqlCommand command = new SqlCommand("SimulateBatchProcess", conn
    command.CommandType = System.Data.CommandType.StoredProcedure
    using (SqlDataReader reader = command.ExecuteReader())
    {
    if (reader.Read())
    {
        log.LogInformation($"Successfully processed at: {DateTime.Now}"
        log.LogInformation($"Rows processed: "+ reader.GetInt32(0).ToString());
    }
    }
 
    conn.Close
        
}

Replace the run.csx contents with the code above (after making any adjustments) and click Save:

Testing it!

If everything is OK, the function will execute at the scheduled time, but if you want to manually execute it, select the Test/Run option, with all the defaults and then click Run.

There might be some warnings (in yellow), but you can ignore those, if the procedure executes OK. You will see the output in blue as shown below:

For the scheduled executions, you will see the messages in the console as shown below:

With this we have successfully scheduled a stored procedure using an Azure Function.

Changing the Schedule

If you want to modify the schedule of the job, go to the Code + Test section.

Then, in the Drop-down menu, select the function.json file:

In there, you can change the schedule following the same CRON format we discussed before:

Once you are done, click Save and the changes will be applied automatically.

Next Steps

• You can read up on Azure Functions best practices here.
• You can read more about Time Trigger Function best practices here.
• Access more Azure tips here.