Create and manage database tasks in Azure
Unlock the complete study guide + 25 practice exams totaling 1,500 questions.
Bundled into the existing Administering Microsoft Azure SQL Solutions premium course — no separate purchase.
14-day refund guarantee on one-time purchases and your first subscription charge.
Included in this chapter:
- Automate management tasks in Azure SQL Database
- Elastic job agent and job database
- Define jobs and steps with the jobs schema
- Schedule and start elastic jobs
- Target groups and dynamic enumeration
- Authenticate the job agent to its targets
- Elastic job private endpoints
- Network security perimeter rules for elastic jobs
- Retries, timeouts, and idempotent scripts
- Collect job results in an output table
- Scale the agent and limit pressure on elastic pools
- Azure Automation building blocks
- Runbook lifecycle: draft, test, publish
- Link runbooks to schedules
- Start runbooks with webhooks
- Choose the sandbox or a Hybrid Runbook Worker
- Runtime environments and modules
- Authenticate runbooks with managed identity
- Run T-SQL from a runbook with an access token
- Credential assets for non-identity authentication
- Restart-safe and concurrency-safe runbooks
- Alert rules, action groups, and processing rules
- Alert on elastic job executions
- Alert on runbook jobs with Log Analytics
- Troubleshoot elastic jobs with jobs.job_executions
- Troubleshoot runbook jobs and non-terminating errors
- Elastic job and runbook settings by requirement
Elastic jobs vs Azure Automation runbooks, by the four questions
| Question | Elastic jobs | Azure Automation runbooks |
|---|---|---|
| What runs: the code | T-SQL job steps (command type TSql, command source Inline) | PowerShell or Python runbooks that can call Azure cmdlets and issue T-SQL |
| What runs: where it is stored | The job database, an Azure SQL database you supply | The Automation account, as Draft and Published versions |
| Where and when: targets | Databases, logical servers, and elastic pools; servers and pools enumerated at each execution | Any resource the runbook's identity and network path can reach |
| Where and when: schedule | UTC interval schedules or on-demand starts; missed intervals run when enabled | Linked schedules, at most hourly, whose start uses a time zone; webhooks start non-Python runbooks on demand |
| Where and when: private targets | Service-managed private endpoint per target or output server, approved on that server | Hybrid Runbook Worker inside the network that can reach the server |
| As whom | User-assigned managed identity (recommended) or database-scoped credentials | Automation account managed identity; credential assets when no identity fits |
| How you know: run history | jobs.job_executions in the job database, purged after 45 days | Job statuses and job streams, kept up to 30 days |
| How you know: alert signal | Elastic Jobs Executions Failed, Timed Out, and Successful metrics on the agent | JobLogs and JobStreams forwarded to Log Analytics for log search alerts |
Decision tree
Cheat sheet
Unlock with Premium — includes all practice exams and the complete study guide.
References
- https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/automation-manage
- https://learn.microsoft.com/en-us/azure/automation/automation-runbook-execution
- https://learn.microsoft.com/en-us/azure/automation/shared-resources/schedules
- https://learn.microsoft.com/en-us/azure/automation/migrate-run-as-accounts-managed-identity
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/jobs-job-executions-elastic-jobs-transact-sql
- https://learn.microsoft.com/en-us/azure/automation/automation-manage-send-joblogs-log-analytics
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-elastic-jobs-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tsql-create-manage
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobstep-elastic-jobs-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-job-elastic-jobs-transact-sql
- https://learn.microsoft.com/en-us/sql/ssms/agent/create-and-attach-schedules-to-jobs
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-powershell-create
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tutorial
- https://learn.microsoft.com/en-us/azure/azure-sql/database/network-security-perimeter
- https://learn.microsoft.com/en-us/azure/automation/manage-runbooks
- https://learn.microsoft.com/en-us/azure/automation/automation-webhooks
- https://learn.microsoft.com/en-us/azure/automation/shared-resources/modules
- https://learn.microsoft.com/en-us/azure/automation/manage-sql-server-in-automation
- https://learn.microsoft.com/en-us/azure/automation/how-to/private-link-security
- https://learn.microsoft.com/en-us/azure/automation/runtime-environment-overview
- https://learn.microsoft.com/en-us/azure/automation/automation-hybrid-runbook-worker
- https://learn.microsoft.com/en-us/azure/automation/automation-hrw-run-runbooks
- https://learn.microsoft.com/en-us/azure/automation/manage-runtime-environment
- https://learn.microsoft.com/en-us/azure/automation/enable-managed-identity-for-automation
- https://learn.microsoft.com/en-us/azure/automation/shared-resources/credentials
- https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-overview
- https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-processing-rules
- https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/action-groups