Elastic Jobs and Azure Automation

Multi-database automation

Simple explanation

Elastic jobs are like sending a memo to every office branch — one command, executed across many databases. “Run this index maintenance on all 30 tenant databases.” Done.

Azure Automation is like a virtual assistant that manages your Azure resources — “Scale up the database at 8 AM, scale down at 8 PM, email me if anything fails.” It runs PowerShell scripts on a schedule.

Elastic jobs

Architecture

Component	Purpose
Job agent	Azure resource that hosts and executes jobs
Job database	Azure SQL DB that stores job definitions, history, and state
Target group	Set of databases where the job runs (individual DBs, elastic pool, server, custom list)
Job	Collection of steps (T-SQL) to execute
Job execution	A single run of the job against the target group

Setting up elastic jobs

# 1. Create a job agent (Azure CLI)
az sql elastic-job agent create \
  --resource-group ScaleWaveRG \
  --server scalewave-sql \
  --name scalewave-job-agent \
  --database-name JobDB

# 2. Create credentials for target databases
az sql elastic-job credential create \
  --resource-group ScaleWaveRG \
  --server scalewave-sql \
  --agent-name scalewave-job-agent \
  --name job-cred \
  --username jobuser \
  --password <secure>

Creating and running a job

-- Connect to the job database
-- Create a target group (all databases in the elastic pool)
EXEC jobs.sp_add_target_group 'TenantDatabases';
EXEC jobs.sp_add_target_group_member 'TenantDatabases',
    @target_type = 'SqlElasticPool',
    @server_name = 'scalewave-sql.database.windows.net',
    @elastic_pool_name = 'TenantPool';

-- Create a job
EXEC jobs.sp_add_job @job_name = 'NightlyIndexMaintenance';

-- Add a step
EXEC jobs.sp_add_jobstep @job_name = 'NightlyIndexMaintenance',
    @step_name = 'RebuildFragmentedIndexes',
    @command = N'ALTER INDEX ALL ON dbo.Orders REBUILD WITH (ONLINE = ON);',
    @credential_name = 'job-cred',
    @target_group_name = 'TenantDatabases';

-- Execute immediately
EXEC jobs.sp_start_job 'NightlyIndexMaintenance';

-- Check execution status
SELECT * FROM jobs.job_executions
WHERE job_name = 'NightlyIndexMaintenance'
ORDER BY start_time DESC;

Scheduling elastic jobs

Elastic jobs support cron-style scheduling or one-time execution. Schedule via the Azure portal, PowerShell, or T-SQL stored procedures.

Azure Automation

Runbooks for database management

# Example: Scale database based on time of day
$resourceGroup = "ScaleWaveRG"
$serverName = "scalewave-sql"
$databaseName = "ProductionDB"

$hour = (Get-Date).Hour

if ($hour -ge 8 -and $hour -lt 18) {
    # Business hours: scale up
    Set-AzSqlDatabase -ResourceGroupName $resourceGroup `
        -ServerName $serverName -DatabaseName $databaseName `
        -Edition "GeneralPurpose" -Vcore 8 -ComputeGeneration "Gen5"
    Write-Output "Scaled up to 8 vCores"
} else {
    # Off-hours: scale down
    Set-AzSqlDatabase -ResourceGroupName $resourceGroup `
        -ServerName $serverName -DatabaseName $databaseName `
        -Edition "GeneralPurpose" -Vcore 2 -ComputeGeneration "Gen5"
    Write-Output "Scaled down to 2 vCores"
}

Azure Automation components

Component	Purpose
Automation account	Container for runbooks, schedules, credentials
Runbook	PowerShell or Python script
Schedule	When to run (recurring or one-time)
Credential	Stored credentials for authentication
Managed identity	Recommended auth method (no secrets)
Webhook	HTTP endpoint to trigger a runbook externally

Alerts and notifications for automated tasks

Tool	Alert Method	How
Elastic jobs	Query job_executions table; set Azure Monitor alert on failures	Monitor the job database for failed executions
Azure Automation	Built-in job status tracking + Azure Monitor alerts	Alert on runbook failure status
SQL Agent	Operators + Database Mail	Configure notifications on job success/failure
Azure Monitor	Alert rules on metrics and logs	Email, SMS, webhook, Logic App actions

Troubleshooting

Issue	Tool	Where to Look
Elastic job fails on specific databases	Job database	`SELECT * FROM jobs.job_executions WHERE lifecycle = 'Failed'`
Job credential rejected	Target database	Check the login/user exists on the target with needed permissions
Runbook times out	Azure Automation	Check runbook job output; increase Fair Share timeout or split runbook
Runbook can’t authenticate	Automation account	Verify managed identity is enabled and has RBAC on target resources

Question

What is an elastic job agent?

Click or press Enter to reveal answer

Answer

An Azure resource that hosts and executes elastic jobs. It requires a dedicated Azure SQL Database as its job database (stores definitions, history, and state).

Click to flip back

Question

How does Azure Automation authenticate to Azure SQL resources?

Click or press Enter to reveal answer

Answer

Recommended: managed identity assigned to the Automation account with appropriate RBAC roles. Alternative: stored credentials (service principal) in the Automation account.

Click to flip back

Question

Where do you check elastic job execution results?

Click or press Enter to reveal answer

Answer

Query the jobs.job_executions view in the job database. It shows status (Succeeded, Failed, InProgress), start/end times, and per-database results.

Click to flip back

Knowledge Check

Priya's elastic job fails on 3 out of 30 target databases with 'Login failed for user jobuser.' The other 27 succeed. What should she check?

You’ve completed Domain 4! You can now automate database tasks across all Azure SQL platforms.

Next up: HA/DR Strategy: RPO, RTO, and Architecture — plan high availability and disaster recovery for Azure SQL solutions.