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

This post has been republished via RSS; it originally appeared at: Azure SQL Database articles.

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. 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

'ServerGroup1',

@ 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= 'IF

(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.

  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

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.