top of page
    • Apr 29
    • 6 min read

Azure Elastic Jobs - The NonPlastic, Elastic Fantasic

After a lengthy period in Public Preview it seems, the boffins at Microsoft have finally pushed Elastic Jobs for SQL Azure DB to general availability. Hooray!


But what are Elastic Jobs? And why would I want to use them in SQL Azure DB?


Well, how about that SQL Agent alternative that you've been crying for since day one?

Yes, runbooks can provide this functionality, and are great but still require a little bit of knowledge for the type of script that you are using. Also, for SQL Databases, we may want to run the same queries across multiple DBs, or a pool of DBs, or just a solitary database. That could be something as simple as Index/Statistics Maintenance, which can lead to a more complex runbook when we were forced to take that route.


...Also, you may be a SQL DBA and TSQL is your weapon of choice.


One of the major excuses not to use Azure SQL DB has now been addressed.

Cool, so what makes up Elastic Jobs?


  • The Azure Elastic Job Agent - This is the Azure component required.

  • A Job database - This stores the config of your jobs (think of this at a very basic level like an MSDB for Azure)

  • One or More Jobs - Your TSQL that you want to run.

  • Job Targets - Where you want to run the jobs.



Job Database


Before we get started, you'll need a SQL Database to use as your 'super MSDB'.


Enter in the relevent details, you will need to have a SQL Server available, or can create one as you go. However, you must use a minimum of S1 for the database, so the DTU model, with a sizing reminder as below.





Be careful with the GUI, as if you try and sneak a Serverless DB through, it will still show lovely and green with 'Database is elegible for agent creation'... but on Deployment, you will receive the below, even when auto pause is off.

The service level objective 'SQLDB_GP_S_Gen5_1' does not support auto pause delay value '0' (Code: ProvisioningDisabled)

For the powershell crowd...something like this will work nicely.

$parameters = @{
    ResourceGroupName = "ResourceGroupRod"
    ServerName =  "sqlserverrod2"
    DatabaseName = "Jobdb1"
    RequestedServiceObjectiveName = "S1"
}
New-AzSqlDatabase @parameters

Elastic Job Agent



You can install this through the Azure Marketplace as normal.












Creating the Job Agent

Nothing nasty here, just populate as required.





Service Tier

As with most Azure elements, you have pay for more to get more, in this case the Job Agent service tiers are based on how many concurrent targets you want to have, ranging from JA 100 (100 targets) up to JA 800, which, you guessed it, can support up to 800 concurrent targets.


If you breach the concurrency, ie... 101 running trying to execute on a JA100 service tier, then job number 101 will wait until one of the previous 100 have completed, giving a slot free, then start to execute.



Identity

We can set an identity for our Elastic Job Agent service, which allows Azure integration in relation for the context in which we will connect to our SQL Azure Targets. You don't have to do this, but highly recommended to do so by Microsoft.


..not just recommended, but highly recommended. Ooo.


For the purposes of this blog, i'll do that, and Assign a User Managed Identity. You do need to make sure you have created one in advance however. Navigate to the Identity tab and add it, simples. I'll use one I have already - RodUserManagedIdentity1.



Now select Review and Create, then Create.


And again, for the Powershell massive.... we can use the New-AzSQLElaasticJobAgent cmdlet instead.

$umi = Get-AzUserAssignedIdentity -resourcegroupname ResourceGroupRod -Name RodUserManagedIdentity1
$parameters = @{
    ResourceGroupName = "ResourceGroupRod"
    ServerName = "sqltesting1again"
    DatabaseName = "Jobdb1" 
    Name = "ElasticJobsRod"
    IdentityType = "UserAssigned" 
    UserAssignedIdentityId = $umi.Id
}

New-AzSqlElasticJobAgent @parameters

Super, smashing, great... we now have our Elastic Agent created. So lets get using it.


Elastic Jobs


These are our workloads that we want to run, so open up your shiny new Elastic Job Agent, and under the Jobs section, you will see:




  • Job Definitions - what our jobs are.

  • Job Executions - History and details of jobs running (Think Job activity Monitor).

  • Target Groups - this is where we define where we want our Jobs to run. Lets start with that, Target Groups. + Create



Next we want to create some Target Group Members. + Create


Now we select what we want to be in this group, whether it be a single database, a complete server, or a Pool of databases. Also, whether this is to be included or excluded fro this group.



I've just created a group with 2 included Databases only, but you can configure however you need.

Single DB? - No problem

All DBs on a Single server, except one or two specifics? - No problem

All servers? - No problem.


Also, i've done this with the GUI for simplicity, but TSQL and PS commands can be used for the same purpose.



With TSQL

-- Add a target group containing server(s) 
EXEC jobs.sp_add_target_group '{GroupName}'; 

-- Add a server target member (entire server)
 EXEC jobs.sp_add_target_group_member @target_group_name = 'ElasticJobAgent_SQLGroup1', @target_type = 'SqlServer', @server_name = 'sqlserverrod2.database.windows.net';

Or with Powershell 


New-AzSqlElasticJobTargetGroup -ResourceGroupName ResourceGroupRod -ServerName sqltesting1again -AgentName ElasticJobsRod -Name ElasticJobAgent_SQLGroup1

Add-AzSqlElasticJobTarget -ResourceGroupName ResourceGroupRod -AgentServerName sqltesting1again -AgentName ElasticJobsRod -TargetGroupName TargetGroup -ServerName sqlserverrod2 -DatabaseName RodDB2 # -Exclude

Access to SQL DBs

So we've set up our Elastic Job Agent, and defined where we want the job to run. Our target Servers/DB still need to allow this access though. Earlier on, we created our Elastic Job Agent to run with a User Managed Identity (UMI), I used RodUserManagedIdentity1 for this purpose, so we'll permission our Databases with this.


Standard stuff, in each DB in the Target group:


In this case, I have a table called Logging in each DB, and just want to read from it. 

--Create a contained DB user.
CREATE USER [RodUserManagedIdentity1] FROM EXTERNAL PROVIDER; 
GO

--And permission as requried.
GRANT SELECT ON [dbo].[Logging]TO [RodUserManagedIdentity1];


Job Definitions

Aha, the meat in the sandwich. Like in Box SQL, the job definitions are what we want to execute on our Azure SQL DBs.


In your Azure Elastic Agent, select Job Definitions and + Create. You can add any schedule required here also.


Now when you open the job, you will see:


As like the normal SQL Agent, Jobs have steps, so + Add Job Step


Note, the GUI fails to validate if there is a space in the Step Name.


Add the TSQL that you wish to run. I'm just adding the below as an example.



If you want to, you can save the output to a separate database too. This is useful history retention for troubleshooting.



The same Job creation with TSQL

--Connect to the job database specified when creating the job agent

--Add job for create table
EXEC jobs.sp_add_job @job_name = 'ReadLoggingTable', @description = 'Reads from Logging table';

-- Add job step for create table
EXEC jobs.sp_add_jobstep @job_name = 'ReadLoggingTable',
@step_name = 'ReadLoggingTable',
@command = N'SELECT FROM [dbo].[Logging];',
@target_group_name = 'ElasticJobAgent_SQLGroup1';

And again, Powershell

New-AzSqlElasticJob -ResourceGroupName ResourceGroupRod -ServerName sqltesting1again -AgentName ElasticJobsRod -Name ReadLoggingTable -Description 'Reads from Logging table' -Enable


Add-AzSqlElasticJobStep  -ResourceGroupName ResourceGroupRod -ServerName sqltesting1again -AgentName ElasticJobsRod -JobName ReadLoggingTable -Name "ReadLoggingTable2" -TargetGroupName ElasticJobAgent_SQLGroup1 -CommandText "SELECT * FROM [dbo].[Logging];"


Simply choose Start Job to run the job. For scheduled jobs, ensure that they are enabled.


Job Executions

If we now look back to the Elastic Job Agent panel, and Job Executions, you will see our Job runs, one for each target within our group.



To see the output, click on the Job Execution ID



And then the Step Name, which shows us the last execution message. So if you want history, configure the job output to save to a separate database as above.




Alerts

Naturally, you will want to recieve Alerts on failed jobs like any good DBA should, and again, its standard Azure stuff.


In the Azure Elastic Job screen, Monitoring blade. Alerts


  • + Create - Alert Rule


For the conditions, we want to know when Jobs Fail, so choose the signal name as below, and set the Total count to be Greater than 0 as per the below screen.


Next, the Actions tab. Here, i've just used the Preview Quick Actions to create an action group that emails me. Nothing fancy here, but i'll get notified if any of my Azure Elastic Jobs fail.








And those are the basics of Azure Elastic Jobs, another great feature for Azure SQL DB ticked off the list.


As ever, thanks for reading.

Roooooooooooood.

bottom of page