Replacing manual DBA scripts across 220 databases

Use Elastic Database Jobs. Deploy a job agent (it creates a job database), define a target group covering the 18 servers and a separate one for the managed instances, and add a T-SQL step. What you gain:

• Parallel execution per target, so 220 databases stop being a serial 4-hour loop.
• A built-in retry policy (attempts and interval) instead of a DBA watching a console.
• One execution-history row per target database per run - that is the audit evidence, queryable and retained, which a console log is not.

Why the alternatives lose: an Azure Automation runbook calling Invoke-Sqlcmd in a foreach is serial, gives one log rather than 220 records, and its Run As accounts are retired, so you would rebuild it on a managed identity anyway. SQL Server Agent on the managed instances only reaches databases on those instances - it cannot touch the 220 Azure SQL databases.

Before building, verify two things: how the job agent reaches servers that deny public network access (it needs a private path, not the public endpoint), and that the index scripts are idempotent, because a retry will re-run a step that partially succeeded.

A single DELETE of 30 million rows grows the log for the entire run, holds locks far longer than the batch window, and competes with the log-rate limit - on a 4-hour window it will not finish cleanly.

• Batch by key: delete 5,000-20,000 rows in a loop with a commit per batch. On Azure SQL Database log truncation depends on routine log backups and checkpoints, so frequent commits are what actually allows the log to be reused.
• Support the predicate with an index on the retention column; otherwise every batch scans and the batching buys nothing.
• Make the loop restartable (highest key remaining, or a TOP (n) with an ordered key) so a retry resumes instead of starting over - pair it with the elastic job's retry policy and a longer step timeout.
• Watch WRITELOG waits and the log write rate during the first run; halve the batch size if the application's writes slow.
• Schedule it after the batch window, not inside it, even if that means the next morning shows partial deletion.

If the largest table will keep growing, the durable answer is partitioning with a sliding-window SWITCH - but that is a schema redesign, not a job change, and should be raised as separate work.

Move the publish into the pipeline as an explicit, reviewable step:

• SqlPackage /Action:Publish with Microsoft Entra authentication (managed identity or a service principal with a federated credential). No SQL logins in the repo.
• Generate /Action:Script first and publish it as a pipeline artifact so a DBA reviews the exact DDL before it executes; then publish the same DACPAC.
• /p:BlockOnPossibleDataLoss=True so a dropped column fails the deployment instead of silently destroying data.
• /Action:DriftReport weekly against each server to catch out-of-band changes - otherwise the next publish quietly reverts someone's hotfix.
• Self-hosted agents in the peered VNet: Microsoft-hosted agents cannot reach servers that deny public network access. Give the managed instances their own stage, since their connectivity path differs.

Structure it as stages with an approval gate per environment, and deploy the same DACPAC to all 18 servers. Per-server scripts are how schema drift begins.