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

 

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: 

  

 

Kate_Smith_0-1582043743023.png

 

  

  

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
5 Comments
Copper Contributor

Why is it suggested to host the Elastic Job Agent on a different server/database as a best practice?

 

Thanks,

Eric

Copper Contributor

Can an Azure AD account be used for the credential or does the credential need to be a local SQL authenticated login/user on each target?

Microsoft

Currently AAD is not supported for authentication, and there is no workaround at this time.

 

Copper Contributor

@Kate_Smith getting a weird error although all my details are correct in the first step Get-AzSqlDatabase: The Resource 'XXX' under resource group 'xxx' was not found.
For more details please go to https://aka.ms/ARMResourceNotFoundFix, Database does exist, can you pls assist?

Copper Contributor

@Kate_Smith When creating credentials using T-SQL, it's first necessary to create a master key. I don't see that step when creating credentials using PoSh. Does it still need to be done, and if so is there a PoSh cmdlet for that?

Regards,

Michael MacGregor

Version history
Last update:
‎May 29 2020 12:48 PM
Updated by: