Create Azure SQL Database Scheduled Jobs


By:   |   Updated: 2020-06-04   |   Comments   |   Related: More > Azure

Problem

There is no SQL Server Agent for AzureSQL, how can I create scheduled jobs to run on my AzureSQL database?

Solution

Azure has a service named Logic App that let you to create and schedule tasks to perform actions on other Azure resources, including AzureSQL databases.

NOTE: Azure Logic App is replacing Azure Scheduler that has been retired on December 31, 2019.

Azure Logic App

To create a new Azure Logic App resource in the Portal Azure, click on Create Resource option, either from the Menu or from your Portal home page:

azure services

You can then type "Logic App" to select the Logic App service from the list:

microsoft azure

Click on the Create button:

logic app

Select the Subscription and the Resource group for this service and provide a name for it. When done click on the Review + create button:

logic app

When the deployment is completed, click on Go to resource button:

go to resource button

You should be directed to the Logic Apps Designer screen (if not you can click on that option from the Logic App menu). You can see that there are many templates and majority of them not SQL related so let's start with a Blank Logic App (the template with a big plus '+' sign):

logic apps designer

Azure Logic App works with triggers, meaning that we need to define what will be the trigger for our job. We can use SQL events like row insert (item is created) or update (item is modified):

azure sql data

Or we can use a scheduler to trigger our job:

schedule

Since we want to have something similar to SQL Server Agent to schedule jobs, we will choose the Schedule trigger. After clicking on the Schedule icon, you'll see the following 2 options:

sliding window

The difference between the above options, is that in case of the schedule does not trigger for some reason, with the Recurrence option, it will continue on the next recurrence interval. When the Sliding Window option is selected, it will process the missing recurrences when the system is back online.  That's why in the Sliding Window option you'll find a Delay option that is used to set the maximum delayed time to process a recurrence.

Another difference is that with the Sliding Window option you have by default Second, Minute and Hour as Frequency when with the Recurrence option you have these ones plus Day, Week and Month. Good news is that you can customize the Frequency so you are able to add a Day, Week or Month to the Sliding Window Frequency in case you need it.

sliding window

The only required inputs are the Interval (numeric) and Frequency.

If you want to define a Start Time for the 1st run of this job you can also define the respective Time Zone and the amount of Delay if you are using a Sliding Window. Otherwise the recurrence will start immediately after you save the job.

After defining the recurrence for your job, click on the New Step button:

sliding window

For the action, filter by "sql server" so you can see only related SQL actions:

sql server

I'll chose “Execute a SQL query (V2)" for demonstration purposes, but feel free to choose what do you need.

Depending on the Action you have chosen, you might have more or less parameters, but at least the server connection and the database are required for all SQL Server actions.

If you need to change the connection click on the corresponding option:

execute a sql query

This will show all of your defined connections and if you need to define a new one, click on the Add New button:

connections

Select the SQL Server Authentication:

sql server

And provide the necessary connection information:

sliding window

After providing all the necessary information and clicking on the Create button it's possible that you'll find a similar error with the below one:

execute sql query

This means that you'll need to allow your Logic App to access your AzureSQL database. Copy the IP address from the above error message and go to you AzureSQL database resource and click on the Set server firewall option from the Overview menu:

set server firewall

Add the rule for your Logic App and press Save to commit the changes:

firewall settings

Go back to your Logic App resource and let's finalize it. Now that you have the connection configured, fill out on the necessary fields and select any optional parameters that you want to provide values for. In my case I will provide the necessary Query Text to be executed when this job runs:

sliding window

In my example, this job will log the time that the insert has occurred so we can see that the schedule is working as expected:

execute a sql query

You are able to add more steps, like for example run another SQL action, or just send an email reporting the success of the job, or execute another operation in another database, application or virtual machine, just to let you know the flexibility that you have with this service.

For this article purpose, I will save this job at this point so we can see it running:

logic app designer

Back to the Logic App menu if you click on the Logic App designer option, you will see your job and you can click on Run if you want to perform an extra run outside of the defined frequency (or if you want to run it manually for the first time if wasn't automatically triggered before):

logic app designer

You can check for the status of the manual run:

sliding window

You can monitor the history of the runs from the Logic App Overview menu:

my logic app

And to confirm that the query really ran successfully, here are the every minute entries in the table executed by my the Logic App job:

query editor

This is only a snippet of what you can do with this wonderful Azure service. Spend some time to learn more about the available SQL triggers and actions as I'm sure you'll find things that you can use for your AzureSQL database.

Keep in mind that this can be used widely and not only for AzureSQL and take in consideration the service limits and configuration information for Azure Logic Apps.  In the Next Steps section I'm providing links to reference guides that can be useful for your future Logic App tasks.

The pricing model isn't simple as expected because it depends on the workflow usage. You can learn more about it here.

Next Steps


Last Updated: 2020-06-04


get scripts

next tip button



About the author
MSSQLTips author Vitor Montalvao Vitor Montalvão is a SQL Server engineer with 20 years of experience in SQL Server, specializing in performance & tuning, data modelling, migration and security.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Adding Users to Azure SQL Databases

Azure Data Factory vs SSIS vs Azure Databricks

Azure Data Factory Pipeline Email Notification – Part 1

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Continuous database deployments with Azure DevOps





get free sql tips
agree to terms


Learn more about SQL Server tools