Elastic Jobs in Azure SQL Database – What and Why
Published Feb 18 2020 12:00 PM 24.4K 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.

 

Elastic Jobs is a feature of Azure SQL Database that lets you run a set of T-SQL scripts against a collection of servers or databases, as a one-time job, in an ad-hoc manner, or according to a pre-defined schedule.  This feature allows regular maintenance and any action that must be repeated – either across a set of databases or over time, much simpler. Set it up one time, and it will continue to run on any cadence you decide, across any scope you decide, for as long as you wish.

 

I am releasing a series of posts that will demonstrate and explain the basics of setting up Elastic Jobs to work with a set of databases, including both a General Purpose database and a Hyperscale database. You will understand fundamental concepts needed to properly create Elastic Jobs, how to create, run, and schedule them using PowerShell or T-SQL, as well as some troubleshooting basics. 

 

What are Elastic Jobs?

Elastic Jobs are a powerful tool that can be used to do a variety of automated tasks on a set of Azure SQL servers or Azure SQL databases.   The tasks themselves must be things that can be achieved in T-SQL.  A sampling of the types of tasks one might choose to achieve includes: 

  • Schema updates (e.g. add a column) 
  • Partition maintenance (e.g. for a rolling-window partitioned table scenario) 
  • Statistics updates 
  • Index maintenance 
  • Data maintenance 

When should I use Elastic Jobs?

You should consider using Elastic Jobs whenever: 

  • You have a task that needs to be run regularly on a schedule, targeting one or more databases. 
  • You have a task that needs to be run once, but across multiple databases. 

What database types can I target with Elastic Jobs?

You can target most of the offerings available on Azure SQL Database with Elastic Jobs, this includes:

  • Basic, Standard, and Premium tier database offerings
  • Hyperscale databases
  • Serverless databases
  • Elastic Pools

 

Managed Instance will still use a limited version of SQL Server Agent.  For more information about Elastic Jobs and SQL Agent, there is more documentation here.

 

In my next post, I will cover fundamental concepts that you should understand before beginning to create Elastic Jobs.

 

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 in T-SQL
  5. Running, Scheduling and Monitoring Elastic Jobs in Azure SQL Database
  6. Troubleshooting Common issues with Elastic Jobs in Azure SQL Database
11 Comments
Brass Contributor

I am eagerly awaiting Part 4.  I am currently in the middle of setting up an elastic job.  The Microsoft documentation on the subject don't have many  practical examples on how to define an elastic job and the credentials.  

Copper Contributor

I'm curious as to when you'd use elastic jobs vs. runbooks.

Copper Contributor

Hi Kate, thanks for the article. I was wondering if you know this is still currently in preview mode, or ready for production use? Will be great if you can share an official article that mentions that this has been moved out of preview. Thanks

Copper Contributor

Hi @Kate_Smith.  It looks like you said a couple months ago that "we are preparing to make it officially generally available", but I don't know how long that process typically takes.  Any idea when it might be available to all?  I ask because I don't control our company's Azure subscription, and it's configured such that we can't use things that are only in preview.  I'd love to take advantage of elastic jobs to schedule several db jobs that we've got.

 

Thanks,

Brad 

Microsoft

@b-rad-G :

Elastic jobs is currently functionally complete and we are getting it ready for GA. But some security related work is being done as part of Azure wide effort and it is likely to delay the GA timeline for Elastic Jobs till the second half of this calendar year (CY 2020). Exact timeline is still being worked out. But the current public preview version is fully supported and any incidents will be treated similar to a GA’d product by the support team and the product team. We have large number of customers who are actively using the current public preview version of Elastic jobs.

Copper Contributor

Administrating the jobs via the various stored procs, views, and tables has been painful and problematic versus the relatively seamless experience we'd all come to expect from Sql Server Agent. This is the first even semi-official movement I've heard on this product in years, and comes just as I've abandoned our implementation of Elastic Jobs in favor of other means.  MS has been relatively poor on communication relative to this key piece of functionality. I appreciate your blog post(s), but feel like MS abandoned us too long.

 

Microsoft

Hi All,

 

I created elastic job that store data of system table sys.syscomments. I am facing issue while inserting the output on the table by using elastic Job. i can see the job showing success but output data is not inserting on the table. I am executing the script select * from  sys.syscomments for each databases on the server.

I am using the below script:-

 


EXEC jobs.sp_add_job @job_name ='job_1', @description='get data';
exec JOBS.sp_update_jobstep
@job_name= 'job_1',
@step_name ='JobStep',
@command= N'SELECT
ROW_NUMBER() OVER(PARTITION BY DB_NAME(o.parent_object_id) ORDER BY ID) AS Rownumber,
@@servername AS ServerName,
DB_Name() AS DatabaseName,
s.name AS SchemaName,
o.name AS ObjectName,
sc.ID AS ObjectID,
o.type_desc,
CONVERT(VARCHAR(max), text) AS Code,
getdate() as created_Date,
FROM sys.syscomments sc
JOIN sys.objects o ON sc.id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id',
@credential_name='ElasticJobUserCredential',
@target_group_name='ShardServerGroup1',
@output_type='SqlDatabase',
@output_credential_name='ElasticJobMasterCredential',
@output_server_name='sharepool1.database.windows.net',
@output_database_name='output database',
@output_schema_name='dbo',
@output_table_name='output table',
@retry_attempts = 1;

Could you help me how is there any solution?  Data is not inserting on the output table. table is showing blank after job succeeded.

Copper Contributor

Kate_Smith I was wondering if you know when it's getting ready for production use? Seems still in preview mode only.

Can we start using in Production as permanent solution for jobs?

Copper Contributor

It has been three years since the last comment on this blog. Although Azure Elastic Jobs service is still in preview, is there a plan to make it GA??

Copper Contributor

Just assume it's been abandoned.  We've long since written our own C# agent jobs or used Azure Functions to fill in where Elastic Jobs failed to solidify. No need to wait on vapor ware and broken promises.

Copper Contributor

Yes they are not giving priority to this work , Not safe to enable in production . We are using this feature in lower environments.

Version history
Last update:
‎May 29 2020 12:48 PM
Updated by: