Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database using T-SQL

Published Feb 27 2020 09:19 AM 4,976 Views
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 part 4 of my blog series on Elastic Jobs for Azure SQL Database, I will show how to create the Elastic Job Agent and associated credentials and jobs using T-SQL.  For more extensive documentation on this subject, see here.


Creating the Job Agent

There is no way to create the Elastic Job Agent in T-SQL. I have already shown how to do this in PowerShell. To do this in the Azure Portal, go to Home, click the box that says “+ Create a Resource”, then search in the box for Elastic Job Agent. Select that, and then follow the steps in the portal to create the agent.








Creating the Credentials

Independent of creating the Elastic Job Agent (meaning, this step and the prior one can be done in any order), you need to connect to the database you have hosting the Elastic Job Agent  and create the refresh and job credentials there. Again, I assume that these credentials have already been created on the target servers and databases and show only the portions specific to the elastic job here. For a refresher on credentials, refer to this previous post.


Executing in the database hosting the Elastic Job Agent:



WITH IDENTITY = 'refreshcredential',
SECRET = 'password1'

WITH IDENTITY = 'jobcredential',
SECRET = 'password2'


You can query the credentials to make sure they are created via the following:


SELECT * FROM sys.database_scoped_credentials


Creating the Target Group 

The final step before creating the job is to specify the group that is the target of the job. Remeber, if I specify a target server, the job will target every database on that server automatically.  If you wish to exclude a database, please see the documentation here.  Again, I follow through creating the demo group just like I did with PowerShell:


EXEC jobs.sp_add_target_group 'DemoGroup'

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_type = 'SqlServer',
@refresh_credential_name='refreshcredential', --credential required to refresh the databases in server

--View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name='DemoGroup';
SELECT * FROM jobs.target_group_members WHERE target_group_name='DemoGroup';


Creating and Defining the Job 

Now fully equipped with an Elastic Job Agent, the appropriate credentials, and a defined target group, we can now create and define the Elastic Job:


--Add job for create table
EXEC jobs.sp_add_job @job_name='demo123', @description='Demo job'

-- Add job step for update statistics
EXEC jobs.sp_add_jobstep @job_name='demo123',
@command= 'I
WHEN (SELECT STATS_DATE([object_id], [stats_id]) FROM sys.stats WHERE name = 'tStats') < DATEADD(day,-1, sysdatetime())
END) = 1


At this point, the job is created and ready to run! You can verify this by querying the database:


SELECT * FROM WHERE job_name = ‘demo123’


Another way to verify the job 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 screen), and then select your agent. From there, you can drill down into the jobs, target groups, and even browse the credentials.




Next, I will show the basics of running, scheduling, and verifying the schedule of an Elastic Job using both PowerShell and T-SQL.


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
Senior Member

Excellent and much needed feature and guide, thank you!


I think you have a small mistake in this part:


EXEC jobs.sp_add_target_group_member

I'm guessing "ServerGroup1" is supposed to be "DemoGroup"?

Occasional Visitor

Where do you define the database that the script should run? I saw the servername being set in jobs.sp_add_target_group_member but not the database where the update stats will run.


@leopasta The elastic jobs will target every database on the server.  You can exclude databases if needed.   Complete documentation here:  

Occasional Visitor



I noticed small typo in 


@target_type = 'SqlServe

is it supposed to be

@target_type = 'SqlServer',


Occasional Visitor


can we use Identity Management (IM) as Credentials ?




Occasional Visitor

Hello @Kate_Smith ,  I have been following ur all of blogs which are very useful. Have one question on elastic job agents and Azure SQL data sync.

Can we use Elastic Job agents functionality to get the alerts on failure of Azure SQL data sync?
please send me any references if any to get alerts on failure of sql data sync

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