Study guide

12.2 Elastic Jobs for Cross-Database Automation

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

12.2 Elastic Jobs for Cross-Database Automation

Key Takeaways Explain This Topic AI

Elastic Jobs run the same T-SQL across many Azure SQL Databases at once - the canonical solution for SaaS multi-tenant tasks like schema changes or nightly reindexing

The architecture is a job agent backed by a control database storing definitions, credentials, and history; targets are servers, elastic pools, databases, or shard maps in a target group

Elastic Jobs are Azure SQL Database only - not SQL Managed Instance or SQL VM, and not a replacement for SQL Server Agent, which is unavailable on single databases

Each job has steps, a target group, and a credential that connects to targets; every add or change to a job step increments the job version, and the next execution uses the new version while an in-flight execution keeps the old one

Execution history lives in the job database table jobs.job_executions, which holds job-level, step-level, and per-target rows and is purged automatically after 45 days; bound concurrency with the job step's @max_parallelism setting

Last updated: August 2026

Why Elastic Jobs Exist

A common SaaS pattern is one Azure SQL Database per tenant - hundreds or thousands of small databases, each with the same schema. The moment you need to change that schema, rebuild an index, or update a lookup table, you face a problem: EXEC against one database does not touch the others. Elastic Jobs solve that by running one T-SQL script against a target group of databases in a single managed operation, with retries, logging, and history.

Elastic Jobs are specifically the right answer when the scenario says: "run this T-SQL across all databases in pool P", "apply a schema migration to every tenant database", or "rebuild indexes nightly on all shards". They are not the right answer for a single database (use SQL Server Agent on MI or a scheduled runbook), and they do not run against SQL Managed Instance or SQL Server on Azure VMs at all - the target must be Azure SQL Database (single, pooled, or sharded via shard map).

Architecture: Job Agent, Control Database, Targets

An Elastic Job has three components, and the exam expects you to know them by name:

Component Role Notes

Job agent The Azure resource that runs jobs Must be created in the same region as its control database; has its own SKU

Control database (job database) Stores job definitions, credentials, target groups, and execution history A new, empty Azure SQL Database; Microsoft's recommended service objective is DTU S1 or higher . The jobs schema and its objects are installed into it. A Hyperscale database is not supported as the job database

Target group The set of databases a job runs against Membership is a server, elastic pool, shard map, or individual database; refreshable

A target group can include or exclude databases by server, pool, shard map, or explicit database name. When you add a server or pool as a target, the agent enumerates the databases in it at execution time, so databases created later are picked up automatically. This is a frequent exam point: the target group is dynamic , not a fixed snapshot.

Each job contains one or more job steps . A step has: a T-SQL script (or a pointer to a script stored in a database), a target group , a credential (a database-scoped credential stored in the control database whose secret is the password used to connect to each target database), and options like step timeout ( @step_timeout_seconds , default 43,200 seconds / 12 hours), retry attempts ( @retry_attempts , default 10), and @max_parallelism . @max_parallelism is the maximum level of parallelism per elastic pool : when set, the step runs against at most that many databases in each elastic pool at a time, so a 500-database pool with @max_parallelism = 50 runs in chunks of 50 rather than hitting all 500 at once. It is set through jobs.sp_add_jobstep in T-SQL, -MaxParallelism in PowerShell, or --max-parallelism in the Azure CLI.

Credentials and Security Model

The job connects to each target database using a credential you create in the control database and reference from the job step. The usual pattern:

Create a database-scoped credential in the control database whose identity is a SQL login that exists on every target database with the rights the script needs (e.g., db_owner on each tenant database).

Reference that credential name in the job step. The agent uses the credential's user/password to authenticate to each target.

Alternatively, you can use a managed identity assigned to the job agent and grant it access to the target databases - the modern, secret-free pattern. Whichever you choose, the credential or identity must exist and be permissioned on every target; a job that succeeds on 99 databases and fails on one usually means the missing permission on that one database, and the per-target execution row will show that error.

Job Versions, Steps, and Scheduling

Every time you add or modify a job step, the job's version number is incremented. The next execution of the job uses the new version; a job that is already executing finishes on the version it started with and does not pick up the new step mid-run. Each execution row records the version it ran, which is what makes the history auditable. A job has at least one step , and steps run in sequence by step number; a step can target a different group than another step, so a single job can touch, say, the config database and then all tenant databases in a second step.

Scheduling: a job can be started on demand or scheduled to run once or recurring on an interval (minutes, hours, days, weeks, months). Schedule lives with the job and can be enabled or disabled without deleting it. A common trap: the job must be explicitly enabled for the schedule to fire - creating a scheduled job does not auto-enable it.

Creating Elastic Jobs: Portal, PowerShell, CLI, T-SQL

You can create and manage elastic jobs through four surfaces, all of which are valid and use the same underlying control database:

Azure portal : walks through job agent, control database, credentials, target groups, jobs, and steps in a UI; good for first-time learning and small one-off jobs.

PowerShell : the Az.Sql module has an *AzSqlElasticJob* family - New-AzSqlElasticJobAgent , New-AzSqlElasticJob , New-AzSqlElasticJobTargetGroup , Add-AzSqlElasticJobTarget , New-AzSqlElasticJobStep .

Azure CLI : az sql elastic-job create , az sql elastic-job target-group create , az sql elastic-job target add , az sql elastic-job step create .

T-SQL against the control database: stored procedures in the jobs schema (e.g., jobs.sp_add_job , jobs.sp_add_jobstep , jobs.sp_add_target_group_member , jobs.sp_start_job ). This is the lowest-level surface and is what the other tools ultimately call.

For automation, the PowerShell or CLI surface is the right answer because it is scriptable, idempotent, and source-controllable. The portal is fine for ad-hoc.

Limitations You Must Remember

Elastic jobs have hard boundaries the exam tests:

Azure SQL Database only . Targets cannot be SQL Managed Instance or SQL Server VMs.

T-SQL only . A job step runs a T-SQL batch; you cannot invoke PowerShell, run a script file, or call an external executable.

One target group per step ; if you need to hit two groups, add a second step.

The control database is not free - it is a real Azure SQL Database you pay for, and the job agent has its own pricing.

No cross-region target membership historically - a target group can include databases in other regions, but be aware of latency and the fact the agent runs in one region.

Not a replacement for SQL Server Agent : SQL Server Agent is available on SQL Managed Instance and SQL Server on VMs but is not available for single databases or elastic pools in Azure SQL Database . That gap is exactly what Elastic Jobs fill.