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

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 , I will show 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 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. 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= ‘IF


WHEN (SELECT STATS_DATE([object_id], [stats_id]) FROM sys.stats WHERE name = ‘tStats') < DATEADD(day,-1, sysdatetime())



END) = 1



@ target_group_name='DemoGroup'

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 .

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


This article was originally published by Microsoft’s Server Storage at Microsoft Blog. You can find the original article here.