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:
You can then type "Logic App" to select the Logic App service from the list:
Click on the Create button:
Select the Subscription and the Resource group for this service and provide a name for it. When done click on the Review + create button:
When the deployment is completed, click on 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):
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):
Or we can use a scheduler to trigger our job:
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:
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.
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:
For the action, filter by "sql server" so you can see only related SQL actions:
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:
This will show all of your defined connections and if you need to define a new one, click on the Add New button:
Select the SQL Server Authentication:
And provide the necessary connection information:
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:
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:
Add the rule for your Logic App and press Save to commit the changes:
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:
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:
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:
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):
You can check for the status of the manual run:
You can monitor the history of the runs from the Logic App Overview menu:
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:
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
- Learn about using functions in expressions for Azure Logic Apps
- Guide for trigger and action types in Azure Logic Apps
- Check out all of the Azure tips on MSSQLTips.com
