Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database in PowerShell

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:

UPDATE STATISTICS T(tStats)

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:

IF
(CASE
WHEN
    (SELECT STATE_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)

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