Azure Job Scheduling Options for Azure SQL DB

Article showing/comparing a list of Azure services we can use for scheduling jobs required for Azure SQL database

Posted by thebernardlim on February 28, 2020

We recently were faced with a request to run SQL scripts (stored procedures) for an Azure SQL Database on schedule.

Naturally, SQL Job Agents came into my mind. However, Azure SQL Single Databases do not support them, and the Microsoft docs recommends Azure Elastic Jobs. This was not an issue for me until I realized it is currently in preview mode!

As we only considered Generally Available services, we further explored and compared all possible services offered on Azure.

Below is a list of the Azure services that can perform scheduled jobs on Azure SQL DB and its pros and cons to each.


Option #1: Elastic Jobs

Elastic Job Logo

Click here for my detailed writeup on how to setup Azure Elastic Jobs.

Pros

Cons

  • More work to setup. Requires a Jobs database, custom coding (T-SQL / Azure CLI), credentials setup.
  • Not as easily maintainable as custom coding required, when compared to visual designers.
  • Still in Preview Mode at time of writing.
  • Monitoring not as detailed in Azure Portal. It only shows if a job succeeds/fails, however, does not describe what the job does / what is failing. Will require T-SQL statements for detailed description of job execution.
    Latest 100 job executions Screenshot showing the ‘Overview’ tab of Elastic Job Agent

Other Notes

  • Pricing includes cost of a new database, i.e. the Jobs Database. The Jobs Database requires a minimum of S0 Service Tier and beyond. Current cost for S0 Tier: HK$0.1565/hour.

Option #2: WebJobs

WebJobs logo

Pros

  • Easy to setup. Just requires upload of script and setting of schedule.

Cons

  • Expensive option as it requires AppService plan to be Standard or Premium because of Always On feature requirement.
  • Not as easily maintainable as custom coding required, when compared to visual designers.
  • Monitoring requires custom coding (to output log lines) to be more useful for diagnosis in the event of failures.
    List of web jobs that ranIn the Kudu dashboard, you can see a list of job runs executed along with its status
    WebJob run detailsOn click of any job, you can see the console logs generated when running the job

Option #3: Functions

Function Apps logo

Pros

  • Easy to setup. Requires some custom coding (C#, or any supported language)
  • Cheaper option of “WebJobs” as there is no need of being Always On. It only charges once it is used. It also has a monthly free grant allowance, for a certain number of executions and execution time.

Cons

  • Not as easily maintainable as custom coding required, when compared to visual designers.
  • Monitoring requires custom coding (to output log lines) to be more useful for diagnosis in the event of failures.
    Recent Jobs window List of recent function runs under ‘Monitor’ tab
    Recent Jobs window Here you can view the invocation details of the specific job run entry

Other Notes

  • Default execution timeout is from 5 - 30 mins depending on AppService plan. For Dedicated plans, there is no overall limit.

Option #4: Automation Accounts

Automation Accounts logo

Pros

  • Easy to setup. Requires some custom coding (Powershell / Python).

Cons

  • Not as easily maintainable as custom coding required, when compared to visual designers. Note: There is a Graphical Runbook option available, however will require knowledge such as the correct Powershell CMDlets to use.
  • Monitoring requires custom coding (to output log lines) to be more useful for diagnosis in the event of failures.
    Recent Jobs window Under the ‘Jobs’ tab, it will show you a list of recent runbook runs
    Job Detail window On click of any runbook run, you can get more details

Other Notes

  • Maximum execution timeout is 3 hours per runbook

Option #5: Logic Apps

Logic App Logo

Pros

  • Visual design of job. Does not require coding. Can code if required.
  • Easily maintainable because of visual designing ability.
  • Easy to debug & monitor. Each step in a run, can be clicked on to see details on what went wrong, which is very useful. No custom logging required to be specifically made.
    Logic App Runs History On the Overview page, there is a list of Runs history window showing results of recent runs
    Logic App Run Window On click of each run entry, here you can visually view which part of the run failed. On click of any action, you can view further details

Cons


Option #6: Data Factory

Data Factory Logo

Pros

  • Visual design of pipeline / activities. Does not require coding.
  • Easily maintainable because of visual designing ability.
  • Long timeout execution duration of 7 days per pipeline activity.
  • Easy to debug & monitor. Each activity in a pipeline run, can be clicked on to see details on what went wrong, which is very useful. No custom logging required to be specifically made.
    Monitor Window In the ADF UI, there is a ‘Pipeline runs’ and ‘Trigger runs’ windows which shows a list of recent runs.
    Pipeline Run Detail Window On click of any run entry, you can visually view the pipeline to see each activity in detail

Cons

N/A


Conclusion: What service should I choose?

All the 6 options above allows scheduled jobs to run against Azure SQL databases. In my case, my requirement was just to execute stored procs on a scheduled basis hence all were possible options.

Each has its pros and cons and would really depend on what we have or what we prioritize. Some scenarios below:

  • If we require only Generally Available options, then Elastic Jobs cannot be considered.
  • If we have an existing AppService Plan (that is Standard or Premium) that we would like to utilize further, we can consider WebJobs
  • If we only run short execution jobs a few times per month, we can consider Functions or Logic Apps, as both are cheap and easy / quick to setup.
  • If we want only strictly Database level coding (T-SQL), then Elastic Jobs can be the option.
  • If we want to be able to allow admins to easily maintain (monitor, diagnose, debug or even make changes) to our scheduled jobs, then we can consider visual designer options such as Logic Apps or Data Factory

Do let me know if there are any more options I can explore or pros/cons to add to the above list. Would love to hear them out and further explore!