Databases Azure SQL

SQL elastic job

A SQL elastic job is a way to run the same database work across many Azure SQL databases without logging into each one.

Source: Microsoft Learn - Elastic Jobs overview for Azure SQL Database Reviewed 2026-05-24

Exam trap: Assuming every tenant database is included in a target group without comparing against actual Azure inventory.
Production check: List databases on each target server and compare the count with the expected tenant inventory.

Practice this topic Microsoft Learn

Article details and learning context

Aliases: Azure SQL elastic job, Elastic Jobs, SQL job agent, database fleet job
Difficulty: intermediate
CLI mappings: 5
Last verified: 2026-05-24
Review level: template-specs-five-use-cases
Article depth: template-specs-five-use-cases-three-case-studies

Related context No curated learning path mapped yet; use the category trail.

Compare related concepts Search related resources Browse the glossary

Understand the concept

In plain English

A SQL elastic job is a way to run the same database work across many Azure SQL databases without logging into each one. It is especially useful for SaaS platforms where every customer has a separate database, but operations still need shared maintenance, schema changes, tenant checks, or cleanup jobs. An elastic job uses a job agent, target groups, job steps, credentials, and execution history. It is not a general-purpose scheduler for any Azure task; it is a database automation pattern for repeated T-SQL work.

Why it matters

SQL elastic job matters because one manual database script does not scale when an estate has dozens, hundreds, or thousands of tenant databases. Without a coordinated job mechanism, teams end up with brittle loops, inconsistent credentials, missed tenants, unclear execution history, and emergency scripts that are hard to audit. Elastic jobs give platform teams a controlled way to apply routine T-SQL at scale, with defined target groups and execution records. That becomes important for SaaS onboarding, schema maintenance, tenant health checks, data correction, and security cleanup. It also separates the job orchestration problem from application code, so operational database work can be reviewed, scheduled, retried, and monitored independently.

Official wording and source

Microsoft Learn describes Elastic Jobs for Azure SQL Database as automation that runs one or more T-SQL job steps against target groups of databases. A job agent stores metadata, executes scheduled or one-time jobs, applies credentials, handles retry behavior, and records execution status for each target.

Open Microsoft Learn

Technical context

Technically, SQL elastic jobs live beside Azure SQL Database rather than inside one tenant database. The job agent uses a database for job metadata, target groups define which databases or servers receive work, and job steps contain the T-SQL to execute. Credentials decide how the agent connects to targets, while retries and timeouts shape execution behavior. The concept touches multitenant data architecture, secrets, contained users, schema governance, monitoring, and release automation. Azure CLI is useful for inventorying databases, pools, servers, identities, and firewall state around the job estate.

Exam context

Compare with

Elastic job Scheduled job Pipeline in Azure DevOps SQL Agent on Managed Instance

Where it is used

Where you see it

Elastic job metadata appears in the job agent database through jobs schema objects, including target groups, job definitions, steps, credentials, executions, and per-target status records.
Azure portal and automation runbooks reference the job agent, schedule, target group, T-SQL step, and approval owners when teams coordinate repeated work across tenant databases.
Azure CLI inventory output for SQL servers, databases, pools, identities, and firewall rules helps confirm whether expected targets and counts exist before an elastic job runs.

Common situations

Apply a tenant-safe schema cleanup query across hundreds of database-per-customer instances without logging into each one.
Run nightly validation that finds tenant databases missing a required table, role, or configuration row.
Collect operational health summaries from many databases into a central reporting table after an incident.
Stagger maintenance commands across elastic pool tenants to avoid a single manual script overwhelming the pool.
Remove a deprecated feature flag or security role from every customer database with execution history for auditors.

Illustrative Azure scenarios

These examples show how the concept can affect design and operations. They are illustrative scenarios, not customer claims.

Scenario 01 Education SaaS applies tenant schema repairs safely Scenario, objectives, solution, measured impact, and takeaway.

Scenario

An education SaaS provider discovered that 640 school databases were missing a new attendance-summary table after a deployment pipeline skipped one migration step.

Goals

Repair every affected tenant database without giving engineers direct manual access.
Keep classroom portal latency under the normal morning threshold.
Record which tenants succeeded, failed, or needed a retry.
Avoid giving the job credential broad database owner rights.

Approach using SQL elastic job

The platform team used SQL elastic job to turn the repair into a controlled database operation. They created a target group from the current tenant inventory, excluded schools already fixed, and used a least-privilege credential that could create the missing table and seed reference rows only. The job step was written to be idempotent so reruns would not duplicate objects. Operators staged the job against twenty low-risk tenants, reviewed execution history, then scheduled the broader run in waves by geography. Azure CLI inventory was used before and after execution to confirm that the target list matched active school databases on each logical server.

Potential outcomes

All 640 affected databases were repaired within three hours instead of three nights of manual scripting.
Morning portal latency stayed within 6 percent of baseline because execution was staggered.
Execution records identified nine failed tenants, all fixed by a targeted retry.
The limited credential passed security review without emergency owner grants.

What to learn

SQL elastic jobs make large tenant repairs safer when target selection, permissions, and execution evidence are treated as first-class design choices.

Scenario 02 Energy telemetry platform collects outage diagnostics Scenario, objectives, solution, measured impact, and takeaway.

Scenario

An energy analytics platform needed to collect diagnostic rows from hundreds of utility customer databases after a data ingestion bug affected only some regions.

Goals

Query every active customer database for the affected meter interval.
Store diagnostic output centrally without changing application code.
Complete triage before the next market settlement deadline.
Avoid rerunning the same diagnostic manually for each customer.

Approach using SQL elastic job

The operations team configured a SQL elastic job with a read-only diagnostic credential and a target group built from active utility databases. The job step queried ingestion status, late-arriving event counts, and suspicious meter batches, then wrote summarized output to a central results table. The first run targeted only the impacted region, and a second run covered the full estate to prove scope. Operators used pool metrics to stagger execution and Azure CLI to confirm that decommissioned customer databases were not included. Failed targets were retried after firewall and credential fixes rather than hidden inside a spreadsheet.

Potential outcomes

Triage time dropped from an estimated 22 manual hours to 95 minutes.
The team identified 37 affected customer databases and cleared 412 unaffected databases with evidence.
Settlement reporting met the deadline with 4 hours of buffer.
Repeated diagnostic scripts fell by 80 percent during the next incident review.

What to learn

Elastic jobs are powerful when an incident requires the same trusted question to be asked across many isolated databases.

Scenario 03 Travel marketplace removes stale promotion flags Scenario, objectives, solution, measured impact, and takeaway.

Scenario

A travel marketplace found that expired promotion flags were still enabled in a subset of hotel partner databases, causing incorrect discounts during peak booking season.

Goals

Disable stale flags across partner databases without touching unrelated settings.
Prove which partner databases were changed for finance reconciliation.
Avoid overloading the elastic pools during booking-hour traffic.
Keep a rollback query available for partners with active disputes.

Approach using SQL elastic job

The database team created a SQL elastic job with a target group based on partner databases that still contained the old promotion code. The T-SQL step checked current flag state, wrote a before-and-after row to an audit table, and updated only records that matched the expired campaign identifier. The job ran in small waves outside each market's peak booking window. Azure CLI inventory helped compare partner tags, pool membership, and logical server names before execution. When two partners disputed the removal, execution history and audit rows showed the exact update time and previous flag value.

Potential outcomes

Incorrect promotional discounts dropped by 92 percent within one business day.
Finance reconciliation used job evidence instead of partner-by-partner email threads.
Pool CPU stayed below 58 percent because the job was staggered by market.
Two disputed changes were reviewed and resolved in under 30 minutes each.

What to learn

A SQL elastic job is not just automation; it is a repeatable evidence trail for operational database changes across tenants.

Azure CLI

After ten years of Azure engineering, I use Azure CLI around SQL elastic jobs for discovery and guardrails even when the job definition itself is managed with T-SQL, portal, PowerShell, or automation code. CLI gives me fast inventory of servers, databases, elastic pools, identities, firewall rules, and tags before I touch target groups. It also lets pipelines export the current estate, compare expected tenant databases against actual resources, and capture evidence after a job run. That matters because most elastic job incidents are not caused by the T-SQL alone; they come from missed targets, wrong credentials, disabled databases, stale firewall paths, or untagged tenant resources.

Useful for

Inventory servers, databases, and elastic pools before building or changing elastic job target groups.
Export tenant database lists as JSON and compare them with the expected job target inventory.
Check firewall and private endpoint settings when job steps fail to connect to target databases.
Review tags and naming conventions so new tenant databases are not silently skipped by job automation.
Capture post-run evidence by combining job execution output with Azure resource inventory and monitoring data.

Before you run a command

Confirm the tenant, subscription, resource groups, logical servers, pool names, and database naming pattern used by target groups.
Verify who owns job credentials, where secrets live, and whether least-privilege database roles are in place.
Check whether the job touches production, regulated, disabled, deleted, or newly onboarded tenant databases.
Understand that Azure CLI may inventory the estate while T-SQL or PowerShell manages the elastic job definition.
Plan for partial success, retry behavior, output collection, and rollback before running a broad job step.

What the output tells you

Server and database lists show whether the expected tenants exist before target groups or scripts are updated.
Elastic pool details reveal whether job timing could overload a shared pool during maintenance windows.
Firewall and private endpoint output helps explain connection failures that are not caused by the job step itself.
Tags and resource IDs provide durable evidence that the job estate matches the approved tenant inventory.
Monitoring and operation timestamps help correlate job failures with pool saturation, deployment changes, or regional events.

Mapped commands

Azure SQL operational commands

direct

az sql server list --resource-group <resource-group>

az sql serverdiscoverDatabases

az sql db list --resource-group <resource-group> --server <server>

az sql dbdiscoverDatabases

az sql elastic-pool list --resource-group <resource-group> --server <server>

az sql elastic-pooldiscoverDatabases

az sql server firewall-rule list --resource-group <resource-group> --server <server>

az sql server firewall-rulediscoverDatabases

az sql db show --resource-group <resource-group> --server <server> --name <database>

az sql dbdiscoverDatabases

Architecture context

Architecturally, SQL elastic job is the operational automation layer for Azure SQL Database estates that cannot be treated as one database. I usually see it in database-per-tenant SaaS, regulated reporting platforms, and distributed operational stores where a central team owns shared maintenance but business data remains isolated. The architecture decision is about boundaries: which database hosts the job agent, which identities can connect, how target groups are built, and what happens when one tenant fails while others succeed. Elastic jobs pair well with elastic pools, naming conventions, tags, deployment pipelines, and monitoring. They should not replace controlled schema migration tooling for every scenario, but they are very strong for repeated T-SQL operations across known targets.

Security: Security impact is direct because the job agent needs credentials that can connect to every target database in a group. Overprivileged job credentials turn a maintenance feature into a broad attack path. Use least-privilege contained users or managed identities where supported by the surrounding workflow, protect any secrets, and separate job administration from database ownership. Target groups should avoid accidental inclusion of production or regulated databases, and job scripts should be reviewed like release code. Network access still matters: firewall rules, private endpoints, and public network access can block or expose job connectivity. Execution history is also security evidence because it shows what ran, where, and when.
Cost: Cost impact is mostly indirect, but it can become obvious in large estates. Elastic jobs can reduce labor cost by replacing manual per-database scripting, but they can also create workload spikes when too many databases run heavy T-SQL at once. Jobs that rebuild indexes, update statistics, or scan tenant data can push elastic pools into higher DTU or vCore usage, trigger scaling discussions, and extend maintenance windows. A job agent database and related monitoring also have cost. FinOps should treat job cadence and workload type as cost drivers, especially for SaaS platforms where the same maintenance task runs across hundreds of small databases.
Reliability: Reliability impact is significant because elastic jobs deliberately fan out work. One bad step can fail across many databases, while one missing credential or target exclusion can leave part of the estate untouched. Reliable design uses small, idempotent steps, clear retries, controlled schedules, and target groups that are tested before production execution. Operators should understand whether failures stop the job, retry, or leave some targets successful and others failed. Long-running jobs can collide with application load, failover, pool scaling, or maintenance windows. A rollback plan needs to account for partial completion, not just all-or-nothing deployment. Good reliability practice includes dry runs, status checks, tenant sampling, and post-job validation queries.
Performance: Performance impact depends on what the job runs and how many targets run in parallel. A lightweight metadata query may barely register, while a schema change, index rebuild, or data correction can consume CPU, log I/O, locks, and worker sessions across an elastic pool. Poorly scheduled jobs create noisy-neighbor pressure for tenant workloads. Good performance design staggers jobs, limits scope, tunes T-SQL, sets sensible retries, and measures database and pool metrics during execution. Operators should watch waits, blocking, duration, DTU or vCore utilization, log usage, and failed targets. The job system provides orchestration, but it does not make an expensive query cheap.
Operations: Operators use SQL elastic jobs to define target groups, create or update job steps, schedule work, review execution history, and investigate failed targets. Day-to-day work includes checking that new tenant databases are included, old tenants are removed, credentials are still valid, and job results match the expected database count. During incidents, operators compare job failures with server availability, firewall changes, pool pressure, blocking, and application releases. Documentation should include target group rules, job owner, script repository, approval path, retry policy, and rollback query. The most useful dashboards show run duration, failure rate, skipped targets, and databases with repeated errors.

Common mistakes

Assuming every tenant database is included in a target group without comparing against actual Azure inventory.
Using a job credential with broad owner permissions when a narrow maintenance role would have worked.
Running a heavy step across all databases at once and saturating the elastic pool.
Treating partial success as full success because the summary count was not reconciled with target count.
Letting old tenant databases remain in target groups after offboarding or legal hold changes.