Domain 4 of 5 · Chapter 3 of 3

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

QuestionElastic jobsAzure Automation runbooks
What runs: the codeT-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 storedThe job database, an Azure SQL database you supplyThe Automation account, as Draft and Published versions
Where and when: targetsDatabases, logical servers, and elastic pools; servers and pools enumerated at each executionAny resource the runbook's identity and network path can reach
Where and when: scheduleUTC interval schedules or on-demand starts; missed intervals run when enabledLinked schedules, at most hourly, whose start uses a time zone; webhooks start non-Python runbooks on demand
Where and when: private targetsService-managed private endpoint per target or output server, approved on that serverHybrid Runbook Worker inside the network that can reach the server
As whomUser-assigned managed identity (recommended) or database-scoped credentialsAutomation account managed identity; credential assets when no identity fits
How you know: run historyjobs.job_executions in the job database, purged after 45 daysJob statuses and job streams, kept up to 30 days
How you know: alert signalElastic Jobs Executions Failed, Timed Out, and Successful metrics on the agentJobLogs and JobStreams forwarded to Log Analytics for log search alerts

Decision tree

Only T-SQL insidethe databases?YesTargets are AzureSQL Database?NoSQL firewall orprivate endpointon target, or jobover 3 hours?YesElasticjobsNoSQLServerAgentNoAzuresandboxYesHybridRunbookWorkerBoth runbook leaves:publish, sign in withmanaged identity, and alerton JobLogs and JobStreams

Cheat sheet

  • Elastic jobs require a supported job database before the agent is created
  • Elastic job definitions use the jobs schema and inline T-SQL steps
  • Elastic job schedules use UTC and can immediately catch up a missed interval
  • Target groups select databases independently of their resource topology
  • Elastic jobs recommend one user-assigned identity with permissions in every target
  • SQL-authenticated elastic jobs separate database discovery from script execution credentials
  • Elastic jobs use approved service-managed private endpoints to reach private SQL targets
  • An elastic job's network security perimeter checks outbound target access during execution
  • Elastic job retry policies require scripts that tolerate repeated execution
  • Elastic job output collection writes the first result set to a configured SQL table
  • Elastic job scaling distinguishes agent concurrency from pool pressure and metadata performance
  • A runbook must be created in an Automation account and published for normal execution
  • Automation schedules require a runbook link and support a minimum native interval of one hour
  • Runbook Azure authentication should use managed identity and an explicit job context
  • A managed-identity runbook needs a SQL access token and a mapped database user to execute T-SQL
  • Automation credential assets supply stored credentials without an interactive runbook prompt
  • An Automation webhook's token-bearing URL authorizes invocation of its published runbook
  • A Hybrid Runbook Worker supplies private reachability and execution beyond sandbox limits
  • Runbook dependencies must exist in the actual runtime that executes the job
  • Restartable runbooks need durable progress and explicit protection against conflicting jobs
  • Elastic job failures can trigger Azure Monitor notifications or automated responses
  • Elastic job troubleshooting drills from the parent execution into target-level states
  • Automation job-status alerts and content-level error alerts require different diagnostic records
  • A Completed runbook can still contain errors that did not terminate execution

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview
  2. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  3. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview
  4. https://learn.microsoft.com/en-us/azure/azure-sql/database/automation-manage
  5. https://learn.microsoft.com/en-us/azure/automation/automation-runbook-execution
  6. https://learn.microsoft.com/en-us/azure/automation/shared-resources/schedules
  7. https://learn.microsoft.com/en-us/azure/automation/migrate-run-as-accounts-managed-identity
  8. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/jobs-job-executions-elastic-jobs-transact-sql
  9. https://learn.microsoft.com/en-us/azure/automation/automation-manage-send-joblogs-log-analytics
  10. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-elastic-jobs-transact-sql
  11. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tsql-create-manage
  12. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobstep-elastic-jobs-transact-sql
  13. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-job-elastic-jobs-transact-sql
  14. https://learn.microsoft.com/en-us/sql/ssms/agent/create-and-attach-schedules-to-jobs
  15. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-powershell-create
  16. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tutorial
  17. https://learn.microsoft.com/en-us/azure/azure-sql/database/network-security-perimeter
  18. https://learn.microsoft.com/en-us/azure/automation/manage-runbooks
  19. https://learn.microsoft.com/en-us/azure/automation/automation-webhooks
  20. https://learn.microsoft.com/en-us/azure/automation/shared-resources/modules
  21. https://learn.microsoft.com/en-us/azure/automation/manage-sql-server-in-automation
  22. https://learn.microsoft.com/en-us/azure/automation/how-to/private-link-security
  23. https://learn.microsoft.com/en-us/azure/automation/runtime-environment-overview
  24. https://learn.microsoft.com/en-us/azure/automation/automation-hybrid-runbook-worker
  25. https://learn.microsoft.com/en-us/azure/automation/automation-hrw-run-runbooks
  26. https://learn.microsoft.com/en-us/azure/automation/manage-runtime-environment
  27. https://learn.microsoft.com/en-us/azure/automation/enable-managed-identity-for-automation
  28. https://learn.microsoft.com/en-us/azure/automation/shared-resources/credentials
  29. https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-overview
  30. https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-processing-rules
  31. https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/action-groups