Databases Azure SQL automation

Elastic job

An elastic job in Azure SQL Database runs Transact-SQL tasks across one or many target databases on demand or on a schedule by using an elastic job agent.

Source: Microsoft Learn - Elastic jobs in Azure SQL Database Reviewed 2026-05-14

Exam trap: Changing production before checking the exact Azure scope, owner, dependency, identity, approval record, and rollback or recovery procedure.
Production check: Can you name the Azure scope, owner, resource ID, identity, dependency, and rollback or recovery path without guessing?

Practice this topic Microsoft Learn

Article details and learning context

Aliases: Azure SQL elastic job, SQL elastic job
Difficulty: intermediate
CLI mappings: 4
Last verified: 2026-05-14

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

Elastic job is an Azure SQL Database automation capability that runs Transact-SQL job steps across one or many databases through an elastic job agent. In practical Azure work, it helps teams schedule maintenance, deploy reference data, collect tenant results, and run recurring database operations across SaaS or multi-database environments. Plainly, it is the shared label operators use when they need to find the setting, resource, identity, or workflow that controls that behavior. A useful entry connects Elastic job to owners, dependencies, telemetry, change control, graph relationships, and the CLI or portal evidence that proves current state.

Why it matters

Elastic job matters because a small Azure design choice can shape customer experience, security posture, operational visibility, and incident recovery. When it is shallowly documented, teams may troubleshoot the wrong SQL Agent, deployment script, elastic pool, Data Factory activity, and manual maintenance script while the real dependency remains hidden. In enterprise Azure work, the value is shared language: application, platform, security, data, finance, and operations teams can discuss the same object without guessing. That reduces incident time, improves audit quality, clarifies ownership, and makes production changes safer because failure modes and graph relationships are visible before change. Treat Elastic job as production owned when customer traffic, regulated data, privileged access, automation, or release governance depends on it.

Technical context

Technically, Elastic job appears in elastic job agents, job databases, job steps, target groups, credentials, schedules, and Azure SQL Database execution history and interacts with Azure SQL Database, Elastic pool, and Azure Monitor. Configuration is reviewed through job agent, job database, and target group, while operators validate live state through job execution history, target group membership, and job database metrics. Scope determines which permissions, logs, commands, network paths, and dependencies matter. Document the exact production boundary before changing behavior.

Exam context

Compare with

Deployment script Elastic pool Data Factory activity SQL database copy

Where it is used

Where you see it

In Azure SQL automation, an elastic job appears as a job agent with job definitions, schedules, steps, and target groups during production review when operators need repeatable evidence.
In SaaS platforms, an elastic job appears when one task must run across many tenant databases or elastic pools during production review when operators need repeatable evidence.
In monitoring, an elastic job appears as job database activity, target execution results, retries, and schedule history during production review when operators need repeatable evidence.

Common situations

Run T-SQL maintenance across many Azure SQL databases.
Collect tenant database results into a central table.
Schedule recurring reference data or cleanup work.

Illustrative Azure scenarios

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

Scenario 01 Elastic job in action for software as a service Scenario, objectives, solution, measured impact, and takeaway.

Scenario

Lucerne SaaS, a software as a service organization, needed to address tenant databases needing a weekly reference data update across hundreds of small customers. The architecture team used Elastic job as the Azure control point for a measurable production improvement.

Goals

Stabilize database performance during peak workload periods
Reduce over-provisioning while keeping response times within target
Give DBAs repeatable evidence for scale decisions
Document rollback and owner responsibility for capacity changes

Approach using Elastic job

The database team configured an elastic job agent with a dedicated job database and target groups for regional tenant servers. Each job step ran reviewed T-SQL to update reference tables, while schedules avoided customer peak hours. Execution history and job database metrics were monitored so failed tenants could be retried without rerunning the full fleet. The team validated Elastic job in a lower environment, captured before-and-after evidence, and promoted the change through a controlled release with rollback ownership. Runbooks were updated so support engineers could identify the correct scope, identity, dependency, telemetry signal, and approval record without asking the original implementer.

Potential outcomes

Reference data updates completed in ninety minutes instead of two days
Failed tenant updates were isolated and retried individually
Manual DBA effort dropped by sixty percent
Customers received consistent pricing rules by region

What to learn

Elastic jobs help SaaS teams operate many Azure SQL databases as a governed fleet.

Scenario 02 Elastic job in action for healthcare analytics Scenario, objectives, solution, measured impact, and takeaway.

Scenario

VanArsdel Health, a healthcare analytics organization, needed to address nightly aggregation queries needing to run across departmental databases without centralizing sensitive raw data. The architecture team used Elastic job as the Azure control point for a measurable production improvement.

Goals

Stabilize database performance during peak workload periods
Reduce over-provisioning while keeping response times within target
Give DBAs repeatable evidence for scale decisions
Document rollback and owner responsibility for capacity changes

Approach using Elastic job

The architecture team used elastic jobs to execute approved aggregation procedures in each department database and write summarized results to a reporting table. Job credentials were scoped to the required procedures, and target groups excluded databases under maintenance. Azure Monitor tracked the job database during the batch window. The team validated Elastic job in a lower environment, captured before-and-after evidence, and promoted the change through a controlled release with rollback ownership. Runbooks were updated so support engineers could identify the correct scope, identity, dependency, telemetry signal, and approval record without asking the original implementer. The final design connected governance with day-to-day engineering work, making the change understandable to security, operations, finance, and application stakeholders.

Potential outcomes

Nightly reporting finished before the six AM operations meeting
Raw patient data stayed in departmental databases
Credential scope passed security review
Failed target databases were retried without blocking successful departments

What to learn

Elastic jobs can collect governed results while respecting distributed database boundaries.

Scenario 03 Elastic job in action for municipal government Scenario, objectives, solution, measured impact, and takeaway.

Scenario

CityNorth Permits, a municipal government organization, needed to address permit databases needing recurring cleanup and index maintenance across multiple agencies. The architecture team used Elastic job as the Azure control point for a measurable production improvement.

Goals

Stabilize database performance during peak workload periods
Reduce over-provisioning while keeping response times within target
Give DBAs repeatable evidence for scale decisions
Document rollback and owner responsibility for capacity changes

Approach using Elastic job

The cloud DBA team created an elastic job to run approved maintenance steps against agency permit databases. Target groups were organized by agency, and the schedule ran after public counter hours. Execution status was reviewed each morning, while skipped databases were routed to the agency owner with job history evidence. The team validated Elastic job in a lower environment, captured before-and-after evidence, and promoted the change through a controlled release with rollback ownership. Runbooks were updated so support engineers could identify the correct scope, identity, dependency, telemetry signal, and approval record without asking the original implementer. The final design connected governance with day-to-day engineering work, making the change understandable to security, operations, finance, and application stakeholders.

Potential outcomes

Maintenance windows shortened by forty-eight percent
Permit application latency improved after index cleanup
Agency owners received clear failed-target reports
The job database provided audit evidence for each maintenance run

What to learn

Elastic jobs provide repeatable Azure SQL maintenance without hand-running scripts against every database.

Azure CLI

CLI checks for Elastic job are useful because they turn portal assumptions into repeatable evidence. Start with read-only commands that show scope, service objective, queue settings, function deployment, authentication state, database target, role schedule, metrics, or operational history. Run mutating, security-impacting, or cost-impacting commands only after approval, because the wrong scope can affect production availability, spend, access, workflows, or database schema.

Useful for

Run T-SQL maintenance across many Azure SQL databases.
Collect tenant database results into a central table.
Schedule recurring reference data or cleanup work.

Before you run a command

Run az account show, confirm tenant and subscription, and verify the signed-in operator has approved read access for the exact Azure scope.
Confirm resource group, service name, identity, region, owner, environment, and change record before collecting evidence or changing production configuration.
Prefer read-only commands first; review any command that changes scale, access, runtime settings, database schema, message behavior, or role eligibility before running it.

What the output tells you

Whether the target database, pool, queue, topic, function app, web app, role schedule, or application resource exists at the expected Azure scope.
Which SKU, service objective, endpoint, identity, configuration version, message setting, orchestration host, or role assignment is visible to the operator.
Whether the issue is wrong scope, stale configuration, missing access, exhausted capacity, failed schema migration, duplicate message handling, or unsafe privilege design.

Mapped commands

Elastic job operational checks

direct

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

az sql dbdiscoverDatabases

az sql elastic-pool show --name <pool> --server <sql-server> --resource-group <resource-group>

az sql elastic-pooldiscoverDatabases

az monitor metrics list --resource <job-database-resource-id> --metric cpu_percent,dtu_consumption_percent --interval PT1H

az monitor metricsdiscoverDatabases

az rest --method get --url "https://management.azure.com/subscriptions/<subscription-id>/resourceGroups/<resource-group>/providers/Microsoft.Sql/servers/<sql-server>/jobAgents?api-version=2023-08-01"

az restdiscoverDatabases

Architecture context

Elastic job belongs to Databases architecture decisions where identity, monitoring, cost ownership, reliability, and production support need shared evidence.

Security: Security for Elastic job starts with least privilege, trusted configuration, and evidence that access matches workload risk. Review job credential permissions, target database access, job database protection, least-privilege T-SQL, secret handling, and audit logs before approving production use. A common failure is assuming that a working feature, successful deployment, visible resource, or populated dashboard proves the configuration is safe. Use Microsoft Entra groups, managed identities, RBAC, private connectivity, diagnostic logging, source-controlled definitions, and approval records where applicable. Keep exceptions ticketed, time-bounded, and owned. For regulated workloads, align the term with classification, retention, break-glass, and incident-response procedures. Remove broad access, stale secrets, unreviewed contributors, public exposure, and undocumented exception paths before Elastic job becomes an incident path.
Cost: Cost for Elastic job appears through job database SKU, target database workload, off-peak scheduling, log growth, monitoring retention, and manual maintenance time, and the human effort required to recover from mistakes. Some costs are direct, such as provisioned capacity, paid tiers, message operations, function execution, storage, logs, or database compute. Others are indirect, such as failed releases, emergency scaling, duplicated troubleshooting, excess review queues, and support escalation. Tag related resources, monitor usage, and separate exploratory work from production. A cost review should connect spend to a real owner, service objective, and measurable business value. When spend changes, inspect Elastic job dependencies before blaming only the service SKU or adding capacity.
Reliability: Reliability for Elastic job depends on repeatable configuration, tested dependencies, and clear failure signals. Watch target enumeration, retry policy, job database health, schedule timing, step timeout, and target exclusion because drift often appears later as failed releases, broken workflows, low throughput, throttling, duplicate processing, lost recovery evidence, or missing audit data. Use lower environments, source-controlled definitions where possible, deployment validation, monitoring, and recovery notes before changing production. Operators should know which endpoint, database, queue, function, web app, role, or downstream application fails first and which metric or log proves the failure. The goal is predictable recovery: detect Elastic job drift, preserve service, restore safely, and explain the incident without guessing.
Performance: Performance for Elastic job depends on parallel target execution, job database capacity, target query duration, cross-region latency, schedule overlap, and pool headroom, and the monitoring path used to confirm success. Review workload shape, concurrency, retry behavior, network path, service limits, database waits, runtime settings, and identity checks before increasing capacity or retrying blindly. The better fix might be correcting configuration, reducing log noise, tuning query or message design, changing scale boundaries, or repairing drift in source-controlled infrastructure. Measure under representative production conditions. Operators should connect symptoms to evidence: latency, throttling, backlog, failed operations, stale state, or high utilization. Good performance work ties Elastic job measurements to user impact and avoids hiding design issues behind larger resources.
Operations: Operations for Elastic job should focus on ownership, observability, and safe repeatability. Standardize names, tags, owner groups, environment labels, diagnostic destinations, runbook links, approval records, and change windows so support teams do not reverse-engineer the platform during incidents. Use read-only CLI, API, diagnostic, or portal checks first, then compare live state with intended configuration. For production, connect alerts, audit events, cost records, graph links, and release notes to the same term. The support question should be simple: who owns it, what changed, and what proves the current state?. Capture owner, scope, evidence, and recovery procedure before changing Elastic job in a production environment.

Common mistakes

Changing production before checking the exact Azure scope, owner, dependency, identity, approval record, and rollback or recovery procedure.
Treating a portal screenshot as sufficient evidence when CLI output, Activity Logs, metrics, and source-controlled configuration are repeatable.
Assuming a name match proves the correct resource when subscriptions, SQL servers, function apps, web apps, queues, topics, and roles can look similar.