Azure SQL elastic job - Azure Glossary

Understand the concept

In plain English

Azure SQL elastic job is an Azure SQL Database job automation capability for running scheduled or on-demand T-SQL work across one database or many databases. It gives teams centralized execution of maintenance, reference-data updates, schema tasks, and collection jobs across large database fleets. You usually see it when SaaS platforms, pooled databases, or multi-server environments need repeatable SQL operations without manually connecting to every database. It still needs ownership, monitoring, access review, and cost control. Operators must inspect live state, explain dependencies, and prove workload fit.

Why it matters

Azure SQL elastic job matters because it turns fleet-wide SQL automation, tenant maintenance, scheduled data collection, and operational consistency across many databases into an operating model teams can review and improve. Without clarity, teams often make weak assumptions about which databases are targeted, which credential is used, whether jobs retry safely, and how failed steps are detected and remediated. Used well, it gives architects boundaries, operators signals, and security and finance teams reviewable evidence. The value is the repeatable decision process around it. For database fleets where manual maintenance creates missed tenants, inconsistent schema, or untracked operational work across subscriptions and pools, that process reduces surprises during releases, audits, and incidents. That clarity keeps small design choices from becoming hidden production risks.

Official wording and source

Azure SQL elastic jobs schedule and run Transact-SQL scripts across one or many Azure SQL databases, including target groups across servers, pools, and subscriptions. Microsoft Learn places it in Elastic jobs overview - Azure SQL Database; operators confirm scope, configuration, dependencies, and production impact.

Open Microsoft Learn

Technical context

Technically, Azure SQL elastic job is managed through elastic job agent, job database, jobs. Operators verify it with job execution history, step status, target group membership and review integration points such as Azure SQL Database, T-SQL stored procedures, PowerShell. Key settings usually include job database, target group, job step script. Keep desired state, live Azure state, release evidence, and incident notes together so teams can trace what changed, who approved it, which dependency was affected, and whether the configuration still matches production design. Keep naming and tags consistent.

Exam context

Compare with

Azure SQL Database SQL server

Where it is used

Where you see it

You see Azure SQL elastic job in portal blades and resource settings, where engineers confirm ownership, health, networking, quotas, current state, and release readiness before production changes.
You see Azure SQL elastic job in runbooks and release gates, where operators connect metrics, identity, network, quota, and deployment evidence during incidents, escalation, and final remediation.
You see Azure SQL elastic job in architecture reviews, where security, operations, finance, and application teams record scope, dependencies, risks, and approved decisions for audit and compliance use.

Common situations

SaaS platforms, pooled databases, or multi-server environments need repeatable SQL operations without manually connecting to every database
database fleets where manual maintenance creates missed tenants, inconsistent schema, or untracked operational work across subscriptions and pools
fleet-wide SQL automation, tenant maintenance, scheduled data collection, and operational consistency across many databases

Illustrative Azure scenarios

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

Scenario 01 SaaS schema maintenance Scenario, objectives, solution, measured impact, and takeaway.

Scenario

Avion Ledger managed hundreds of tenant databases and needed schema updates without manually connecting to each customer database.

Goals

Apply a tax-table schema change to 680 databases.
Run changes outside regional business hours.
Detect failures within 15 minutes.
Avoid reapplying completed steps.

Approach using Azure SQL elastic job

Architects configured Azure SQL elastic job by creating an elastic job agent, defining target groups by server and pool, and using idempotent T-SQL job steps. They integrated it with tenant catalogs, Azure Monitor, deployment pipelines, Log Analytics, and support tickets, then documented the approved resource names, regions, identities, and monitoring signals. Operators used job execution history, failed step output, and target group inventory to validate live state during releases and incidents. Security added least-privilege job credentials and reviewed T-SQL scripts, while the rollout included pilot jobs, retry testing, and customer-support readiness drills. A final readiness check compared design assumptions, measured service behavior, and support evidence before handoff to the operations team. The team also recorded owner approval, rollback notes, evidence retention, and support handoff details for every production milestone.

Potential outcomes

The schema change reached 680 databases overnight.
Failures were detected in 7 minutes.
Completed databases were not reprocessed during retries.
Manual tenant update work fell by 86 percent.

What to learn

Elastic jobs make fleet-wide SQL maintenance practical when target groups and scripts are governed.

Scenario 02 Reference-data refresh Scenario, objectives, solution, measured impact, and takeaway.

Scenario

Lighthouse Travel needed the same airport tax reference data refreshed across tenant databases every morning.

Goals

Update reference data before 6 a.m. UTC.
Keep tenant query impact below 5 percent.
Record per-database completion evidence.
Allow safe reruns after partial failure.

Approach using Azure SQL elastic job

Architects configured Azure SQL elastic job by scheduling an elastic job with batched T-SQL steps and output rows showing completion for every tenant database. They integrated it with Blob Storage source files, job database tables, Azure Monitor, SaaS admin portal, and incident runbooks, then documented the approved resource names, regions, identities, and monitoring signals. Operators used job output tables, retry counts, and database CPU metrics to validate live state during releases and incidents. Security added controlled data-source access and scoped job credentials, while the rollout included load tests, partial failure simulations, and operator rerun training. A final readiness check compared design assumptions, measured service behavior, and support evidence before handoff to the operations team. The team also recorded owner approval, rollback notes, evidence retention, and support handoff details for every production milestone.

Potential outcomes

Reference updates completed by 5:12 a.m. UTC.
Tenant CPU impact stayed below 3 percent.
Completion evidence was available per database.
A simulated partial failure was rerun safely.

What to learn

Elastic jobs are valuable when repeatable tenant updates need schedule control and per-target evidence.

Scenario 03 Tenant telemetry collection Scenario, objectives, solution, measured impact, and takeaway.

Scenario

Pine Harbor Analytics wanted daily performance snapshots from customer databases without granting analysts direct access to each tenant.

Goals

Collect top-query metrics from 300 databases.
Centralize results in one reporting table.
Prevent analysts from accessing tenant databases directly.
Reduce manual collection to zero.

Approach using Azure SQL elastic job

Architects configured Azure SQL elastic job by using elastic jobs to query approved telemetry views and write summarized results to the job database. They integrated it with Query Store, Log Analytics, Power BI, tenant catalogs, and DBA review workflows, then documented the approved resource names, regions, identities, and monitoring signals. Operators used job history, result table counts, and target enumeration output to validate live state during releases and incidents. Security added read-only collection credentials and restricted analytics access, while the rollout included pilot telemetry jobs, result validation, and privacy review. A final readiness check compared design assumptions, measured service behavior, and support evidence before handoff to the operations team. The team also recorded owner approval, rollback notes, evidence retention, and support handoff details for every production milestone.

Potential outcomes

Metrics were collected from 300 databases daily.
Analysts used only central summarized data.
Manual collection scripts were retired.
Performance review preparation time dropped from 2 days to 3 hours.

What to learn

Elastic jobs help SaaS operators collect database evidence without opening every tenant database to analysts.

Azure CLI

Use command-line tooling for Azure SQL elastic job when you need repeatable inventory, governed changes, deployment checks, incident evidence, or audit proof. Command output makes scope, identity, configuration, and timing explicit, which is safer than relying on screenshots or memory during reviews.

Useful for

Inventory the current configuration across subscriptions, tenants, resource groups, and production environments before a design review.
Capture repeatable evidence for incidents, audits, migrations, release readiness checks, and post-deployment verification.
Create or update supported settings through reviewed scripts instead of relying on portal-only manual changes.
Compare expected state with live Azure state after deployment, rollback, migration, quota change, or platform upgrade work.

Before you run a command

Confirm the active tenant, subscription, resource group, workspace, project, or region before running any command.
Check whether the command is read-only, mutating, cost-impacting, security-impacting, or destructive before production use.
Use least-privilege identity and store sensitive command output only in approved evidence or ticketing locations.
Have rollback notes, owner contacts, and change records ready before changing production configuration.

What the output tells you

The output identifies the current resource, setting, relationship, identity, deployment, or runtime state being inspected.
IDs, regions, SKUs, tags, endpoints, identities, and scopes show whether deployment matches the approved design.
Empty or missing fields often reveal an incomplete configuration, wrong scope, unsupported feature, or stale deployment.
Metric, quota, and state values help separate Azure configuration issues from application behavior problems.

Mapped commands

Azure SQL elastic job operations

direct

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

az sql dbdiscoverDatabases

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

az sql dbdiscoverDatabases

az rest --method get --url <elastic-job-agent-resource-url>

az restdiscoverDatabases

az monitor metrics list --resource <database-resource-id> --metric cpu_percent

az monitor metricsdiscoverDatabases

az sql db query --server <server> --name <job-database> --query "SELECT * FROM jobs.job_executions"

az sql dbdiscoverDatabases

Architecture context

Azure SQL elastic job matters because it turns fleet-wide SQL automation, tenant maintenance, scheduled data collection, and operational consistency across many databases into an operating model teams can review and improve. Without clarity, teams often make weak assumptions about which databases are targeted, which credential is used, whether jobs retry safely, and how failed steps are detected and remediated. Used well, it gives architects boundaries, operators signals, and security and finance teams reviewable evidence. The value is the repeatable decision process around it. For database fleets where manual maintenance creates missed tenants, inconsistent schema, or untracked operational work across subscriptions and pools, that process reduces surprises during releases, audits, and incidents. That clarity keeps small design choices from becoming hidden production risks.

Security: Security for Azure SQL elastic job starts with knowing who can configure it, who can use it, and what data, identity, or network path it can influence. The main risk is job credentials with excessive privileges, unsafe T-SQL applied to every tenant, exposed job output, or target groups that include the wrong databases. Review RBAC assignments, identities, keys or credentials, network exposure, diagnostic logs, and linked resources before production use. Prefer least privilege, private connectivity where appropriate, audited changes, and secret storage outside application code. Also confirm that support teams can prove the current configuration during an incident without relying on screenshots or memory. Document approved evidence before high-risk changes and review it during access recertification.
Cost: Cost impact for Azure SQL elastic job comes from elastic job agent tier, job database size, query runtime, output storage, monitoring ingestion, and operational impact from jobs running too often or too broadly. The common waste pattern is enabling the capability for a pilot, then leaving resources, capacity, logs, or supporting infrastructure running after the original need changes. Estimate costs before rollout, tag resources to a clear owner, and compare steady-state usage with the design assumption. During reviews, look for unused resources, overbuilt tiers, avoidable data movement, and duplicated environments. Cost control works best when finance data is tied back to operational intent. Tie each optimization to an owner, forecast, and retirement date.
Reliability: Reliability depends on whether Azure SQL elastic job is designed for the workload's real failure modes. Focus on retry behavior, idempotent scripts, target enumeration, schedule windows, job database availability, failure alerts, and rollback steps for bad job scripts. A reliable design documents what should happen during scale-out, regional disruption, credential failure, deployment rollback, and operator error. Monitoring should show both the Azure resource state and the symptoms users actually feel. Test the runbook before an outage, capture evidence from CLI or portal checks, and decide which failures require manual intervention versus automated recovery. Include dependency maps and health signals so responders know whether the platform, network, or application failed during triage.
Performance: Performance depends on how Azure SQL elastic job affects latency, throughput, scale behavior, or operator decision time. Focus on parallel execution, target database load, job step timeout, retry storms, off-peak scheduling, and how quickly operators identify failed or slow targets. Do not assume the default setting is fast enough for production or that a faster tier fixes design problems. Measure before and after important changes, watch for throttling or slow control-plane calls, and test with realistic scale. Performance evidence should include user-facing symptoms, resource metrics, and configuration details so the team can distinguish service limits from application defects. Include baseline measurements so later tuning work has a defensible comparison point.
Operations: Operationally, Azure SQL elastic job should appear in runbooks, dashboards, release gates, and ownership records. Focus on job ownership, target group review, schedule calendars, failure triage, output validation, credential rotation, and deployment approval before new job steps. The team should know which commands are safe for inventory, which changes are mutating, and which outputs prove compliance or readiness. Keep naming, tags, environments, and documentation consistent so support engineers can find the right resource quickly. Review the configuration after releases, incident retrospectives, platform upgrades, and cost reviews rather than treating it as a one-time setup. Assign a named owner, keep an escalation path, and review stale automation before quarterly platform reviews.

Common mistakes

Running commands against the wrong subscription, tenant, region, project, workspace, or resource group because context was not checked.
Treating a successful create command as proof that security, monitoring, networking, and operations are complete.
Copying examples into production without adjusting regions, names, identities, SKUs, quotas, and network rules.
Ignoring service-specific limits, preview behavior, retirement status, private DNS, or required extensions before automation rollout.