Configure and Manage Automation of Tasks
What runs where
- SQL Agent exists on Managed Instance and SQL Server on Azure VMs - not in Azure SQL Database.
- Elastic Database Jobs are the Azure SQL Database equivalent: a job agent database, a target group (databases across one or more servers), and T-SQL job steps.
- Azure Automation runbooks with a managed identity handle scheduled PowerShell; Logic Apps handle event-driven orchestration; Azure Functions handle custom code and webhooks.
- Deployment is code: ARM templates or Bicep, Terraform, deployment scripts, and Azure DevOps or GitHub Actions pipelines publishing a dacpac with SqlPackage, which is incremental and idempotent.
Guardrails
Use Azure Policy to audit or deny non-compliant resources, resource locks (CanNotDelete) on production servers, tags for cost attribution, managed identities with RBAC instead of stored credentials, and a maintenance window on Managed Instance so patching stays out of business hours.
Traps
- Do not answer "SQL Agent job" for an Azure SQL Database requirement.
- Elastic job credentials must exist in the job agent's own database and cannot reach across tenants.
- There is no built-in autoscale knob for provisioned Azure SQL Database; you get serverless, or you build a scheduler that issues a scale operation.
- Deployment order and repeatability matter: schema before data, scripts must be re-runnable.
Rule of thumb
Recurring T-SQL touching many databases → elastic job. Agent-like logic on one database → Automation or Logic App calling a stored procedure.