I’ve covered how to create Elastic Jobs in the portal (this one is important to read if you aren’t familiar with elastic jobs already), with Terraform, and with Bicep. Now, I’ll cover how to create them and their associated objects with PowerShell. Don’t do this in prod to start. Always test in a lower environment first.

TL;DR, you can get my PS script up in GitHub repo.

Prerequisites

Once authenticated, you can use the Az module to run scripts, including the one for setting up Elastic Jobs in Azure SQL Database. This script assumes you have the necessary permissions and Azure PowerShell module installed (Az module). You may need to adjust the variables to match your environment. I’m doing this all with a Mac, so you may have to change this for Windows, if that’s your OS.

1. Install PowerShell
2. Install the Az module: Install-Module -Name Az -AllowClobber -Scope CurrentUser
3. Install these modules, as well:
   • Install-Module -Name Az.Sql
   • Install-Module -Name SqlServer
4. Ola scripts added to each user db on the Azure SQL Server. We do this via Flyway where I work, so I’m not using PowerShell for this.
   • Index optimize: https://ola.hallengren.com/scripts/IndexOptimize.sql
   • Command execute: https://ola.hallengren.com/scripts/CommandExecute.sql
   • Command log: https://ola.hallengren.com/scripts/CommandLog.sql

I’m going to put my elastic job db on an existing server, so I won’t need to create a resource group or server for it. If you need these, then you can get the PowerShell for that here.

To run all the scripts, you must be in a PowerShell prompt and connected to Azure.

So for my future purposes, since I’m on Mac, I will do pwsh at command line, then Connect-AzAccount.

Creating Managed Identity

This managed identity will be used by the Elastic Job agent to access your databases. Make sure to capture the ID output, as you will use it later when creating the job agent.

# SETUP MANAGED IDENTITY 
$resourceGroupName = "rgname"
$userManagedIdentityName = "ElasticAgentJobsManagedID"
$location = "eastus2"
New-AzUserAssignedIdentity -ResourceGroupName $resourceGroupName -Name $userManagedIdentityName -Location $location
Write-Host "User-Managed Identity $userManagedIdentityName created successfully."
Write-Host "Managed Identity ID: $($managedIdentity.Id)"

Creating Elastic Job Agent Database

If you want your elastic job agent db to be on a separate db server, then you can use this PowerShell to set that up. I will put mine on an existing db server. It doesn’t need a separate server.

# SETUP ELASTIC JOB SQL SERVER 
$resourceGroupName = "rgname"
$serverName = "sqlservername"
$location = "eastus2"
$adminUser = "sqladmin" # Set your admin username
$adminPassword = "YourStrongPassword!" # Set your admin password
# Create SQL Server
New-AzSqlServer -ResourceGroupName $resourceGroupName `
                -ServerName $serverName `
                -Location $location `
                -SqlAdministratorCredentials (New-Object -TypeName System.Management.Automation.PSCredential `
                                             -ArgumentList $adminUser, (ConvertTo-SecureString $adminPassword -AsPlainText -Force))
Write-Host "SQL Server $serverName created successfully."

You need the agent db to be S1 or higher database to accommodate the elastic agent setup later. Apparently, this can’t be in an elastic pool, so note that in your setup. I put it on an Azure SQL Server with databases in an elastic pool, though, but this needed it’s own resources separate from the pool.

# SETUP ELASTIC JOB DATABASE 
$resourceGroupName = "rgname"
$serverName = "sqlservername"
$databaseName = "ElasticJobsDB"
#$location = "eastus2" 
New-AzSqlDatabase -ResourceGroupName $resourceGroupName `
                  -ServerName $serverName `
                  -DatabaseName $databaseName `
                  -Edition "Standard" `
                  -RequestedServiceObjectiveName "S1"
Write-Host "Database $databaseName created successfully."

Add Managed ID to the target databases and the agent db – I haven’t figured out this PS part yet with PowerShell. It’s not authenticating correctly because you can’t add an AD account without being logged in as an AD account. I’m adding this manually to the databases. Create on all user databases:

/* Run in each user db */CREATE USER ElasticAgentJobsManagedID FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER ElasticAgentJobsManagedID;
/* Run in master */CREATE USER ElasticAgentJobsManagedID FROM EXTERNAL PROVIDER;

Creating Elastic Job Agent

This agent PowerShell will setup your elastic jobs with a managed identity.

# SETUP ELASTIC JOB AGENT
$UserManagedID = Get-AzUserAssignedIdentity -ResourceGroupName "rgname" -Name "ElasticJobsMIDev" 
New-AzSqlElasticJobAgent -ResourceGroupName "rgname" `
                         -ServerName "sqlservername" `
                         -DatabaseName "ElasticJobsDB" `
                         -Name "ElasticJobsAgentDev" `
                         -IdentityType "UserAssigned" `
                         -UserAssignedIdentityId $UserManagedID.Id `
                         -SkuName "JA100" `
                         -WorkerCount 100
Write-Host "Elastic Job Agent created successfully."

Change the worker count based on your needs. Worker count will depend on how many concurrent targets you need. Options range from 100 to 800. Exceeding the job agent’s concurrency capacity tier with job targets will create queuing delays for some target databases/servers. For example, if you start a job with 110 targets in the JA100 tier, 10 targets will wait to start until others finish.

Setup targets on the agent

Next, we need to set up a target group. There is a way to refine with includes and excludes as well. You can see a MSFT example here. I want all the databases on this server, though, to have stats updates.

# SETUP TARGETS ON THE AGENT WITHOUT ELASTIC POOL
# Get the job agent
$agent = Get-AzSqlElasticJobAgent -ResourceGroupName "rgname" -ServerName "sqlservername" -Name "ElasticJobAgent" 
# Create target group
$targetGroup = $agent | New-AzSqlElasticJobTargetGroup -Name "AzureSQLDBs"
# Add the server to the target group
$targetGroup | Add-AzSqlElasticJobTarget -ServerName "sqlservername"

Also, if your db is in an elastic pool, you have to do this differently.

# SETUP TARGETS ON THE AGENT WITH ELASTIC POOL 
# Get the job agent
$agent = Get-AzSqlElasticJobAgent -ResourceGroupName "rgname" -ServerName "sqlservername" -Name "ElasticJobAgent" 
# Create target group
$targetGroup = $agent | New-AzSqlElasticJobTargetGroup -Name "AzureSQLDBs"
# Add the server to the target group
$targetGroup | Add-AzSqlElasticJobTarget -ServerName "sqlservername" -ElasticPoolName "YourPoolName"

Define elastic job and steps

First, create a job. And enabled it and schedule it to run once daily. This is in UTC, so set it accordingly.

# SETUP JOB ON THE AGENT
# Get the job agent
$agent = Get-AzSqlElasticJobAgent -ResourceGroupName "rgname" -ServerName "sqlservername" -Name "ElasticJobsAgentDev"
# Create job
$agentJob = $agent | New-AzSqlElasticJob -Name "OlaStatsUpdateJob"
# Set daily schedule and enable the job 
$startTimeUTC = [datetime]::ParseExact("2025-02-7 04:00:00", "yyyy-MM-dd HH:mm:ss", $null)
$agentJob | Set-AzSqlElasticJob -IntervalType Day -IntervalCount 1 -StartTime $startTimeUTC -Enable

Then create a step or two. I have my Ola stored procs in a separate schema named dba to keep it separate from app objects.

# SETUP JOB STEP ON THE JOB
$job = Get-AzSqlElasticJob -ResourceGroupName "rgname" `
    -ServerName "sqlservername" `
    -AgentName "ElasticJobsAgentDev" `
    -Name "OlaStatsUpdateJob"
$commandText = @"
EXECUTE [dba].[IndexOptimize] 
    @Databases = 'USER_DATABASES', 
    @FragmentationLow = NULL, 
    @FragmentationMedium = NULL, 
    @FragmentationHigh = NULL, 
    @UpdateStatistics = 'ALL', 
    @LogToTable = 'Y';
"@
$job | Add-AzSqlElasticJobStep -Name "OlaStatsUpdateStep" `
    -TargetGroupName "AzureSQLDBs" `
    -CommandText $commandText

Also, you need to factor in how many concurrent jobs you want running with your steps. You might only want it running one at a time, especially if you are doing index maintenance on an Azure SQL DBs sharing a pool. With stats updates, maybe you can do more concurrently, but not all of them at the same time. I left this alone, but might need it in the future.

$updateQuery = @"
EXEC jobs.sp_update_jobstep  
    @job_name = 'OlaStatsUpdateJob',  
    @step_name = 'OlaStatsUpdateStep',  
    @max_parallelism = 3;
"@
Invoke-Sqlcmd -ServerInstance "sqlservername.database.windows.net" `
              -Database "ElasticJobsDB" `
              -Query $updateQuery `
              -Username "yourAdminUser" `
              -Password "yourAdminPassword"

I will also add another step that will clean out the command log if the rows are older than 90 days. To facilitate this, I will add a non-clustered index on date.

CREATE NONCLUSTERED INDEX IX_CommandLog_StartTime  
ON dba.CommandLog (StartTime);

Then add it to the job with this.

# ADD ANOTHER STEP TO CLEANUP COMMAND LOG
# Variables
$resourceGroupName = "rgname"
$serverName = "sqlservername"
$agentName = "ElasticJobsAgent"
$jobName = "OlaStatsUpdateJob"
$targetGroupName = "AzureSQLDBs"
# Get the job
$job = Get-AzSqlElasticJob -ResourceGroupName $resourceGroupName -ServerName $serverName -AgentName $agentName -Name $jobName
# Add a new job step for deleting old records
$job | Add-AzSqlElasticJobStep -Name "DeleteOldCommandLogsStep" `
                               -TargetGroupName $targetGroupName `
                               -CommandText "DELETE FROM dba.CommandLog WHERE StartTime < DATEADD(DAY, -90, GETDATE());" `
                               -TimeoutSeconds 43200 `
                               -RetryAttempts 10 `
                               -InitialRetryIntervalSeconds 1 `
                               -MaximumRetryIntervalSeconds 120 `
                               -RetryIntervalBackoffMultiplier 2
                               
Write-Host "Job step 'DeleteOldCommandLogsStep' added successfully."

Executing and monitoring job execution

Let’s execute the job and see what errors we got because we probably missed something that needs to be setup.

# START JOB 
$job = Get-AzSqlElasticJob -ResourceGroupName "rgname" -ServerName "sqlservername" -AgentName "ElasticJobsAgent" -Name "OlaStatsUpdateJob"
$jobExecution = $job | Start-AzSqlElasticJob
$jobExecution

At this point, I switch over to T-SQL or use the portal. I favor T-SQL because it’s a lot easier to see the job info. Watch the job exec with this script.

SELECT job_name, step_name, target_server_name, target_database_name, target_type, 
        last_message, start_time, end_time, is_active, lifecycle, current_attempts
        FROM jobs.job_executions 
WHERE is_active = 1
ORDER BY start_time DESC;

I see errors because I didn’t add the managed ID to the agent DB, and I didn’t add the Ola stored procs to the databases, yet.

Alerting on failed jobs

You need to know when this thing fails. Not have to go query a table or look in the portal. You will need to create an action group, if you don’t have one already, and an alert rule. The following code will create the action group and alert rule. The action group is configured to send an email, and the alert rule will alert on a failed agent job. It will check every 5 mins. Probably don’t need that often when the job only runs daily, but that’s was the example code, and I’m trying to get something in place as quickly as I can. For the window size and frequency, it’s hours:minutes. For example, with frequency, 0:5 is every 5 minutes, 1:0 is every hour. There are specific values allowed. For frequency, you can use: 1, 5, 15, 30 minutes or 1 hour. For window size, you can use 1, 5, 15, 30 minutes, 1, 6, 12, or 24 hours.

# SETUP ACTION GROUP AND ALERT RULE 
$subscriptionId = (Get-AzContext).Subscription.Id
$resourceGroupName = "rgname"
$serverName = "sqlservername"
$jobAgentName = "ElasticJobAgent"
$actionGroupName = "your-action-group-name"
$actionGroupShortName = "ElasticJobFailureActionGroup"
$emailreceiver = New-AzActionGroupEmailReceiverObject -EmailAddress email@email.com -Name DataOps
$actionGroup = New-AzActionGroup -ActionGroupName $actionGroupName `
                    -ResourceGroupName $resourceGroupName `
                    -Location "Global" `
                    -ShortName $actionGroupShortName `
                    -EmailReceiver $emailreceiver 
    
# Create the Metric Alert Rule Criteria (with TimeAggregation)
$alertCriteria = New-AzMetricAlertRuleV2Criteria -MetricName "elastic_jobs_failed" `
    -Operator GreaterThan `
    -Threshold 0 `
    -TimeAggregation Total  # Set TimeAggregation to Average
# Get the Action Group again if needed by its name and resource group
$actionGroup = Get-AzActionGroup -ResourceGroupName $resourceGroupName -ActionGroupName $actionGroupName
# Create the Metric Alert Rule
Add-AzMetricAlertRuleV2 -Name "ElasticJobFailureAlert" `
    -ResourceGroupName $resourceGroupName `
    -TargetResourceId "/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/jobAgents/$jobAgentName" `
    -WindowSize 24:0 `
    -Frequency 24:0 `
    -Condition $alertCriteria `
    -ActionGroupId $actionGroup.Id `
    -Severity 1

Now, you have an elastic agent setup with a job and 2 steps. You will also have alerting in case it fails. I will add the index maintenance at a future time, and will add that script as I have it.

The post PowerShell (and T-SQL) and Elastic Jobs appeared first on sqlkitty.