Configure and manage automation of tasks

Where each automation tool runs

SQL Server Agent jobs

An Agent job is made of steps, one or more schedules and optional notifications. Stored in msdb (sysjobs, sysjobsteps, sysjobhistory, sysschedules).

Job anatomy

• Step types: T-SQL, PowerShell, CmdExec, SSIS package, Analysis Services, replication agents (VM). On Managed Instance T-SQL, SSIS and replication steps are supported; CmdExec, PowerShell, and Analysis Services steps are not.
• Step flow: each step has “on success” and “on failure” actions: go to next step, quit with success, quit with failure, go to step N. Retry attempts and interval apply to a step. Run as a proxy (a credential) when a step needs other permissions (VM only).
• Ownership: a job runs under its owner’s context for T-SQL; sysadmin owner means no permission check. Prefer an owner such as a dedicated login rather than a person.
• Permissions in msdb: SQLAgentUserRole (own jobs), SQLAgentReaderRole (view all jobs), SQLAgentOperatorRole (run, stop any job).

Schedules

Types: one time, recurring (daily, weekly, monthly, with frequency inside a day), start automatically when SQL Server Agent starts, start when CPU is idle. One schedule can serve many jobs, and one job can have many schedules. Plan maintenance in low-activity windows, avoid overlaps (backups vs index rebuild vs CHECKDB), and use multiple schedules for different cadences (for example full backup weekly, differential daily, log every 15 minutes). Consider time zones: on Managed Instance the time zone is set at creation.

Typical maintenance jobs: backups (VM; PaaS has automatic backups), index rebuild/reorganize, statistics update, DBCC CHECKDB, history cleanup, data purging, and ETL/refresh.

Alerts and notifications

• Operators define who is notified (email, pager, net send is gone). Database Mail must be configured (available on MI and VM; not SQL Database). Enable the Agent mail profile so job notifications work.
• Notifications per job: when the job succeeds, fails or completes, notify an operator, write to the event log, or auto-delete.
• Alerts fire on SQL Server events (error number, severity such as 17-25), performance conditions (counters, e.g. log used percent), or WMI events, and can notify an operator or run a job. On MI, alerts are supported but some event types differ.
• Alerts for Azure Monitor: for PaaS, use Azure Monitor alert rules (metrics and log alerts) and action groups to send email/SMS/webhook, call a runbook or Logic App.

Troubleshooting Agent jobs

Useful procs: sp_start_job, sp_stop_job, sp_help_job, sp_update_job, sp_add_jobstep, sp_add_schedule, sp_add_jobserver.

Automating deployment of database resources

Why template-based deployment

Infrastructure as code gives repeatable, reviewable, version-controlled deployments with idempotency (same template, same result). Use when questions ask for “consistent”, “repeatable”, “pipeline”, “multiple environments”.

ARM templates and Bicep

• ARM template: JSON with parameters, variables, resources, outputs. Declarative. Deploy with New-AzResourceGroupDeployment, az deployment group create, or the portal.
• Bicep: a domain-specific language that compiles to ARM. Cleaner syntax, modules, automatic dependency detection. az bicep build converts to ARM; az bicep decompile converts the other way.
• Resource types for SQL: Microsoft.Sql/servers, Microsoft.Sql/servers/databases, Microsoft.Sql/servers/elasticPools, Microsoft.Sql/servers/firewallRules, Microsoft.Sql/managedInstances, Microsoft.SqlVirtualMachine/sqlVirtualMachines.
• Deployment modes: Incremental (default, adds/updates, leaves other resources) and Complete (deletes resources not in the template, risky). What-if previews changes (az deployment group what-if). Template specs store versioned templates. Secure parameters (@secure() in Bicep, securestring in ARM) or Key Vault references protect passwords. Use dependsOn in ARM (implicit in Bicep through references).
• Deploy from a CI/CD pipeline: GitHub Actions or Azure Pipelines with a service connection or workload identity.

Azure PowerShell and Azure CLI

PowerShell is imperative and object-based. CLI is concise and cross-platform (Cloud Shell has both). Both can be scripted in pipelines. Use managed identity or a service principal for non-interactive login (Connect-AzAccount -Identity, az login --identity).

Monitor and troubleshoot deployments

• Resource group > Deployments shows status, duration, operations and error details. Activity log records failures (az monitor activity-log list). Use -Debug/--debug for verbose logs.
• Common failures: name conflict (SQL server names are globally unique), quota/capacity for a region or SKU, invalid password complexity, subnet not delegated or too small for Managed Instance, missing provider registration (Microsoft.Sql), permission (Contributor/SQL DB Contributor needed), dependency ordering, Azure Policy deny.
• Run what-if and Test-AzResourceGroupDeployment or az deployment group validate before deploying.
• Managed Instance creation is long-running; check the operation status instead of retrying.

Database tasks in Azure

Elastic jobs

Elastic jobs run T-SQL scripts across one or many Azure SQL databases on a schedule or on demand. They are the Agent substitute for SQL Database (targets are SQL Database databases, elastic pools and servers; Managed Instance uses SQL Server Agent instead).

Architecture:

• An elastic job agent (an Azure resource) backed by a job database (an Azure SQL Database, minimum S0/General Purpose with 1 vCore, that stores job definitions and history).
• Target groups: servers, elastic pools, databases, shard maps; include or exclude members. Server and pool targets are re-enumerated at each run, so new databases are picked up.
• Credentials: a database-scoped credential for the job to connect to targets (master credential to enumerate server/pool members and a job credential for running), or a managed identity on the agent. Each target needs a login/user for that credential with required permissions.
• Jobs have steps (T-SQL only), schedules, retry policy, and output (can write results to a destination table). jobs.sp_add_job, jobs.sp_add_jobstep, jobs.sp_start_job, jobs.sp_add_target_group.
• History in jobs.job_executions view; retention is limited.

Use cases: rebuild indexes and update stats across a fleet, deploy schema changes to all tenant databases, collect data into a central table, purge old data.

Automation of database tasks with other services

Example: a metric alert on CPU > 90% triggers an action group that runs an Automation runbook which scales the database one step up; another schedule scales it back down at night to save money.

Alerts and notifications on database tasks

• Azure Monitor alerts on elastic job failures use Log Analytics or custom queries; Automation has job status alerts (failed jobs metric and runbook job streams) and Logic Apps have run history alerts.
• Use action groups: email, SMS, push, voice, webhook, Logic App, Function, Automation runbook, ITSM, Event Hub.
• Alerts for SQL events in MI: SQL Agent alerts plus Azure Monitor diagnostic logs.
• Service Health and Resource Health alerts cover platform incidents.

Troubleshoot automated tasks