Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database in PowerShell
Published Feb 25 2020 12:00 PM 8,207 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.


Having laid the conceptual groundwork for Elastic Jobs in two previous postings (1, 2), I am now going to create an elastic job and associated credentials using PowerShell.  For this scenario, I have one or more databases with a table ‘T’ and statistics ‘tStats’. I want to enforce an update for these statistics every day. To do this, I need to check that my stats have been updated in the past day, and if not, update them. The T-SQL to update statistics on a table “T” with stats named “tStats” is simple:




However, it is also important to make this operation idempotent. To do this, I first check to see if the stats have been updated in the past day.  If they have, I do nothing.  If they have not I update the statistics:


(SELECT STATE_DATE([object_id], [stats_id])
FROM sys.stats 
WHERE name = 'tStats')
DATEADD(day,-1, sysdatetime())
END ) = 1


Now that I have the basic T-SQL script, I can test it out on a sample database.  Once I ensure that it works as expected (even with multiple executions occurring in close proximity to one another), I am ready to create an elastic job that executes this script on the target database(s).


Creating the Job Agent

Assume I have a server already, called elasticjobsagenthostserver. This server and the database on it can be an S0 or higher database as the Elastic Job Agent does not require a lot of resources. It is considered good practice to host the Elastic Job Agent on a different server/database than the targets of the jobs. Thus, I have created a database called ElasticJobAgentHostDB, which will host my Elastic Job agent.   I also have a target server that contains two databases, a General Purpose database (GPDB) and a Hyperscale database (HSDB).


Since I am going to be using Azure Cloud Shell to create my agent and create and run my jobs, I will go to the Azure portal and open the Azure Cloud Shell to work with PowerShell commands. If you want to use regular PowerShell on your local machine, this is fine, as long as you are using a version of PowerShell that is updated to a recent Azure PowerShell version. To open Azure Cloud Shell, click this icon on the top middle/left of the screen: 







Once the Azure Cloud Shell is open, I will bind the database I want to use to host the Elastic Jobs Agent (job database):


:\> $db = Get-azsqldatabase -ServerName elasticjobsagenthostserver -DatabaseName ElasticJobsAgentHostDB 


Now I create the Elastic Jobs agent inside the jobs database, and bind it to the variable $jobAgent, for future use. 


:\> $jobAgent = $db | New-AzSqlElasticJobAgent -Name demoagent 


Creating the Credentials

Next, I create and inspect a SQL elastic job credential. I discussed the why of this in my previous post about fundamentals, so I will just create the credentials with no explanation here. Note: I also assume these credentials also exist in the TARGET database as appropriate, but it is important not to forget that step! I will use PowerShell to create both the job and refresh credentials using the agent.


:\> $loginPasswordSecure1 = (ConvertTo-SecureString -String "strongPWD" -AsPlainText -Force)
:\> $loginPasswordSecure2 = (ConvertTo-SecureString -String "strongPWD2" -AsPlainText -Force)
:\> $refreshCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "refreshcredential", $loginPasswordSecure1
:\> $refreshCred = $jobAgent | New-AzSqlElasticJobCredential -Name "refreshcredential " -Credential $refreshCred
:\> $jobCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "jobcredential", $loginPasswordSecure2
:\> $jobCred = $jobAgent | New-AzSqlElasticJobCredential -Name "jobcredential" -Credential $jobCred


Creating the Target Group

At this point, I am ready to create a target group for my job and bind that to the variable $tg. In this example, the target group contains a single server, that hosts both a Hyperscale database and a General Purpose database, but it could also contain an arbitrary set of databases listed individually or by server. More documentation on this here.


:\> $jobAgent | New-AzSqlElasticJobTargetGroup -TargetGroupName DemoGroup 
:\> $tg = $jobAgent | Get-AzSqlElasticJobTargetGroup -TargetGroupName DemoGroup 


I add a server to the target group, using my refresh credential. 


:\> $tg | Add-AzSqlElasticJobTarget -ServerName elasticjobstargetserver -RefreshCredentialName refreshcredential 


With this setup, my elastic job will target all user databases (everything except master and tempdb) on this server, in other words, BOTH my Hyperscale and General Purpose databases. 


Creating and Defining the Job

Next, we will create an elastic job against the target, with name demo123.


:\> $job = $ jobAgent | New-AzSqlElasticJob -Name demo123 


Bind the T-SQL text to a variable to make it less cumbersome for defining the job:  


:\> $sqlText1 = "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)" 

Then I add a job step, which will execute the SQL we have specified. 


:\> $job | Add-AzSqlElasticJobStep -Name "step1" -TargetGroupName DemoGroup -CredentialName jobcredential -CommandText $sqlText1 


In my next post, I will show how to do these same tasks using T-SQL, and then we will get into the process of executing, scheduling, and troubleshooting our Elastic Jobs. 


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 in T-SQL
  5. Running, Scheduling and Monitoring Elastic Jobs in Azure SQL Database
  6. Troubleshooting Common issues with Elastic Jobs in Azure SQL Database
Version history
Last update:
‎May 29 2020 12:48 PM
Updated by: