Blog Post

Azure SQL Blog
3 MIN READ

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

Kate_Smith's avatar
Kate_Smith
Icon for Microsoft rankMicrosoft
Feb 27, 2020
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:

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE DATABASE SCOPED CREDENTIAL refreshcredential
WITH IDENTITY = 'refreshcredential',
SECRET = 'password1'

CREATE DATABASE SCOPED CREDENTIAL jobcredential
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
'DemoGroup',
@target_type = 'SqlServer',
@refresh_credential_name='refreshcredential', --credential required to refresh the databases in server
@server_name='elasticjobstargetserver.database.windows.net'

--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
(CASE
WHEN (SELECT STATS_DATE([object_id], [stats_id]) FROM sys.stats WHERE name = 'tStats') < DATEADD(day,-1, sysdatetime())
THEN 1
ELSE 0
END) = 1
UPDATE STATISTICS T(tStats);',
@credential_name='jobcredential'
@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 jobs.jobs 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
Updated Mar 07, 2022
Version 10.0
  • EitanBlumin's avatar
    EitanBlumin
    Brass Contributor

    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
    'ServerGroup1',

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

  • leopasta's avatar
    leopasta
    Copper Contributor

    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.

  • Umit_Tiric's avatar
    Umit_Tiric
    Copper Contributor

    Hi,

     

    I noticed small typo in 

     

    @target_type = 'SqlServe

    is it supposed to be

    @target_type = 'SqlServer',

     

  • erkan77's avatar
    erkan77
    Copper Contributor

    Hi,

    can we use Identity Management (IM) as Credentials ?

     

    BR,

    Erkan

  • VidyaSrinivas's avatar
    VidyaSrinivas
    Copper Contributor

    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

  • Hi,

    When creating a target group and one or more databases are to be excluded, how can they be added, individually, to the target group at a later date?

    It might make more sense to have the parameter that excludes a database be named exclude_database_name, and one to explicitly include, for example, just one database on the server, as I'm actually doing in order to test this out on our test environment, e.g. include_database_name. It would be even more useful if these parameters could accept multiple database names rather than have to run EXEC jobs.sp_add_target_group_member multiple times.

     

    Regards,

    Michael MacGregor