Azure Batch for a DBA: running scheduled PowerShell/SQL jobs
Azure Batch architecture as a DBA job platform: pool, job, task, package, managed identity, Key Vault, logs, and why Batch rather than SQL Agent, Automation or Functions.
For years my scheduled DBA tasks lived in SQL Server Agent: one job, one server, one script. With Azure SQL Database and elastic pools there is no Agent, and the same duties (exports, maintenance, reports) must run against hundreds of databases. I ended up choosing Azure Batch as the execution platform. Here is the architecture and why.
The building blocks
| Batch component | Role in my platform |
|---|---|
| Batch account | The container: quotas, diagnostic logs, packages |
| Pool | The nodes (VMs) that run tasks; carries the managed identity |
| Start task | Prepares each node when it joins the pool (modules, tools) |
| Application package | The versioned code (ZIP) deployed to the nodes |
| Job | A group of tasks; caps parallelism |
| Task | One execution: a pwsh command line for one database or one duty |
Around it: a user-assigned managed identity (UAMI) attached to the pool, Key Vault for secrets, App Configuration or a config file for settings, a storage account for task outputs, and a Log Analytics workspace for diagnostics.
flowchart LR
A["Azure DevOps (scheduled pipeline)"] --> B["Batch account"]
B --> C["Job"]
C --> D["Task : one per database"]
E["Application package (versioned ZIP)"] --> F["Node pool"]
D --> F
F --> G["Managed identity (UAMI)"]
G --> H["Azure SQL (elastic pool)"]
G --> I["Key Vault / App Configuration"]
F --> J["Storage : output files"]
B --> K["Log Analytics : diagnostics"]
K --> L["Grafana / alerts"]
What one run looks like
- A scheduled pipeline (cron) queries the inventory to find which databases to process.
- It creates or reuses a job, then submits one task per database.
- A pool node downloads the application package (once per node) and starts the task's entry script.
- The script asks the managed identity for a token (the node's metadata service), reads its secrets from Key Vault and connects to Azure SQL without a password.
- Standard output and logs go to storage; task events go to Log Analytics.
The job only does throttling: for example MaxParallelTasks = 3 so a night of exports cannot saturate the target elastic pool.
Why not SQL Agent, Automation or Functions?
| Need | SQL Agent | Automation | Functions | Batch |
|---|---|---|---|---|
| Available on Azure SQL Database | No | Yes | Yes | Yes |
| Long runs (hours) | Yes | Limited (runbooks) | Limited (Consumption plan) | Yes |
Heavy tools (sqlpackage, bcp, 7-Zip) |
Yes | Hard | Hard | Yes, on the node |
| Massive fan-out, controlled parallelism | No | Weak | Possible, you build it | Native (tasks, jobs) |
| Control of image and tool versions | Yes | No | No | Yes |
Automation and Functions are great for short, small scripts. As soon as you export multi-GB databases with sqlpackage, or want a hundred tasks queued with three running at once, Batch is the right tool. The price: you own the node image and the job lifecycle.
Identity and secrets
The managed identity is attached to the pool, not to the script. Inside the task the code detects its context (admin workstation, pipeline, Batch node) and picks the matching identity. No password in configuration: external keys (for mail sending, say) are Key Vault secret names resolved at run time.
On the SQL side the identity must exist as a database user (CREATE USER ... FROM EXTERNAL PROVIDER). That is the topic of the fourth article in this series.
Logs
Three levels: the application log written by the script, the task output archived in storage, and Batch events (start, completion, scheduling failure) sent as diagnostics to Log Analytics. The last one feeds my Grafana dashboards.
Lessons learned
- The number of jobs per account is capped: one job per scheduled run eventually hits the quota. I use one shared job per elastic pool, never deleted, with cleanup of old tasks.
- Never assume a tool exists on the node: document the expected image versions (ODBC driver,
bcp, modules) or install them in the start task. - Several applications on one pool: never run a global cleanup (dropping network drives, purging folders) at task start, it would kill the neighbouring task.
- Task logs vanish with retention: archive what you need for a diagnosis.