12.2 Elastic Jobs for Cross-Database Automation

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:

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:

1. 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).
2. 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.

Monitoring Job Execution and History

Elastic jobs write execution records into the control database in the jobs schema. There is one history table - jobs.job_executions - and it records three levels of rows, which is the distinction the exam expects you to recognize:

A typical troubleshooting query returns every target that failed in the last run:

SELECT job_name, step_name, target_server_name, target_database_name,
       lifecycle, last_message, start_time, end_time
FROM jobs.job_executions
WHERE job_name = 'NightlyReindex'
  AND lifecycle = 'Failed'
  AND target_database_name IS NOT NULL   -- per-target rows only
ORDER BY start_time DESC;

Filtering on target_database_name IS NOT NULL isolates the per-target rows; dropping that filter also returns the job- and step-level rollups. A system cleanup job purges execution history older than 45 days; to remove newer history manually, run jobs.sp_purge_jobhistory in the job database. The portal and the Azure CLI wrap the same table, so you can read history from the UI without writing T-SQL - but querying it directly lets you filter and aggregate in ways the UI does not.

Output to a Database Table

A job step can write its output to a table in a database you specify - the output database - by setting output_database_name, output_server_name, and output_schema_name/output_table_name on the step. The output of every target execution lands as one row per target, which is extremely useful for capturing the result of a diagnostic query run across all tenant databases (for example, capturing row counts or index fragmentation into one central table for reporting).

Elastic Jobs vs SQL Server Agent vs Azure Automation

A common DP-300 scenario asks you to choose the right tool. Use this comparison:

The decision tree: if the targets are many Azure SQL Databases and the action is T-SQL, Elastic Jobs wins. If the action is anything but T-SQL, or the target is MI/VM, reach for SQL Server Agent or Azure Automation instead. The next section covers Azure Automation in depth.

Scope and Parallelism

Each job step can specify @max_parallelism - the maximum number of databases per elastic pool the step runs against at the same time. It applies to each elastic pool that is directly in the target group and to elastic pools inside a server that is in the target group. When it is not set, the step runs against the pool's databases without that cap, which is how a large job overwhelms a shared pool. Setting @max_parallelism = 50 against a 500-database pool keeps concurrency at 50 and lets the remainder queue behind it; raising it shortens elapsed time but multiplies the concurrent load on both the target databases and the job database. Tune with the workload in mind - schema changes that take an exclusive lock should run with low concurrency, while a lightweight UPDATE STATISTICS can run with higher concurrency. The exam frames this as a tradeoff between completion time and concurrency-induced contention.