Elastic Jobs in Azure SQL Database – What and Why

Elastic Jobs is a feature of Azure that lets you run a set of TSQL 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, create, run, and schedule them using PowerShell or T-SQL, as well as some 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 SQL servers or 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 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 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 .

  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.   Common issues with Elastic Jobs in Azure SQL Database

 

This article was originally published by Microsoft's Premier Field Engineering Blog. You can find the original article here.