Running, Scheduling and Monitoring Elastic Jobs in Azure SQL Database
Published Mar 03 2020 12:00 PM 14.2K Views
Microsoft
Elastic Jobs are currently in public preview.  The feature is ready and we are preparing to make it officially generally available.  No extra steps are required to get started.

 

In previous posts, I have demonstrated how to create an Elastic Jobs Agent, setup credentials for Elastic Jobs, create a target group of servers/databases for the agent, and how to create and define an elastic job using both PowerShell and T-SQL.

 

In this post, I drill down into how to run an Elastic Job both in an ad-hoc fashion and how to schedule a job to run regularly. I do this both for PowerShell and for T-SQL.

 

Starting and Monitoring the Job Using PowerShell

Prerequisites: Azure Cloud Shell or PowerShell equivalent, and an Elastic Job Agent with at least one job defined.

 

The first step at this point is to get and bind the $jobAgent variable (to get the job).  First, bind the server and the agent:

 

:\>$server = Get-AzSqlServer -ServerName yourservername
:\>$jobAgent = $server | Get-AzSqlElasticJobAgent
:\>$job = $jobAgent | Get-AzSqlElasticJob -Name demo1233

  

Now, we can start the job:

 

:\> $job | Start-AzSqlElasticJob 

 

and get the executions of it:  

 

:\> $je = $ jobAgent | Get-AzSqlElasticJobExecution -Count 10 

 

Assuming our job succeeds, we can schedule it to run every single day, starting from now.

 

:\> $job | Set-AzSqlElasticJob -IntervalType Day -IntervalCount 1 -StartTime (Get-Date) -Enable

 

The interval types allowed are: Day, Hour, Minute, Month, and Week.

 

To verify the schedule, you may need to re-bind the job to refresh it, then just re-examine $job, at which time you should see that the ScheduleType is Recurring and the Schedule is Enabled.  The interval is for 1 day. 

RecurringVerifyPowerShell.png

 

Starting and Monitoring the Job Using T-SQL

Prerequisites: SQL Server Management Studio, and an Elastic Job Agent with at least one job defined.

 

We can explicitly start the job in an ad-hoc fashion by simply passing the job name as an argument to a the stored procedure jobs.sp_start_job:

 

EXEC jobs.sp_start_job 'demo123'

 

To schedule the job to run every day, use jobs.sp_update_job:

 

EXEC jobs.sp_update_job
@job_name = 'demo123',
@enabled=1,
@schedule_interval_type='day',
@schedule_interval_cout=1

 

If you want to verify that the job is scheduled, query the table jobs.jobs in the database where the Elastic Job Agent is hosted:

 

SELECT * FROM jobs.jobs WHERE job_name = 'demo123'

 

QueryJobRecurrenceInSSMS.png

 

Another way to verify the job and schedule is to examine it through the Azure Portal. To do this, find all of your Elastic Job Agents (you can search for the Elastic Job Agents service in the search bar at the top of the home page on the Azure Portal), and then select your agent. From there, you can drill down into the jobs, find the target job, and view its schedule.  Below you will see a job with an enabled daily schedule.

 

JobScheduledAzurePortal.png

 

This blog is part of a series about Elastic Jobs on Azure SQL Database.

Code samples are also available on GitHub.  

  1. Elastic Jobs in Azure SQL Database – What and Why
  2. Fundamental Concepts for Elastic Jobs in Azure SQL Database
  3. Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database in PowerShell
  4. Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database using T-SQL
  5. Running, Scheduling and Monitoring Elastic Jobs in Azure SQL Database
  6. Troubleshooting Common issues with Elastic Jobs in Azure SQL Database
2 Comments
Version history
Last update:
‎May 29 2020 12:49 PM
Updated by: