Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database using T-SQL
Published Feb 27 2020 09:19 AM 11.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.

 

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.

 

Kate_Smith_0-1582046909140.png

 

 

Kate_Smith_1-1582046909144.png

 

 

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.

 

MonitoringElasticJobAgentInPortal.png

 

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
8 Comments
Co-Authors
Version history
Last update:
‎Mar 07 2022 09:00 AM
Updated by: