Automating go-to-production for an Azure SQL database with a request queue
A request queue, creation in the right elastic pool, failover group, users, tags, inventory and a report, built from idempotent, resumable steps.
Putting a new database into production looks simple: create it, add it to the failover group, grant access. In practice it is a dozen small actions across several resources, and every one of them can fail halfway. Done by hand, two databases never end up configured exactly the same way. I replaced the manual checklist with a queue-driven workflow where each step can be replayed safely.
The idea
A request is a row in a queue table: who asked, which source, which target environment, a status. A processor script reads the pending rows and runs the workflow for one request at a time. The processor is scheduled; the workflow script can also be launched by hand for a single request, with a parameter to resume from a given step.
flowchart TD
Q["Request queue (SQL table)"] --> P["Queue processor"]
P --> W["Workflow script (one request)"]
W --> S1["1. Validate request and source"]
S1 --> S2["2. Choose elastic pool"]
S2 --> S3["3. Create database in the pool"]
S3 --> S4["4. Add to failover group"]
S4 --> S5["5. Users and roles"]
S5 --> S6["6. Tags and diagnostics"]
S6 --> S7["7. Update inventory"]
S7 --> R["Report mail + status update"]
W -. "-StartFromStep n" .-> S4
Idempotent steps
The whole design rests on one rule: every step first looks at the current state and only does what is missing. A step is never "create", it is "ensure".
| Step | Check before acting | Action if missing |
|---|---|---|
| Create database | Get-AzSqlDatabase finds it? |
New-AzSqlDatabase -ElasticPoolName ... |
| Failover group | Is the database in the group's database list? | Add-AzSqlDatabaseToFailoverGroup |
| Users and roles | sys.database_principals / role membership |
CREATE USER ... FROM EXTERNAL PROVIDER, ALTER ROLE ... ADD MEMBER |
| Tags | Compare existing tags with the expected set | merge tags, never overwrite foreign ones |
| Inventory | Row exists for this database name? | INSERT or UPDATE |
function Confirm-DatabaseInFailoverGroup {
param($Rg, $Server, $Fog, $DbName)
$fog = Get-AzSqlDatabaseFailoverGroup -ResourceGroupName $Rg -ServerName $Server -FailoverGroupName $Fog
if ($fog.DatabaseNames -contains $DbName) {
Write-Information "Already in $Fog, nothing to do"
return
}
$db = Get-AzSqlDatabase -ResourceGroupName $Rg -ServerName $Server -DatabaseName $DbName
Add-AzSqlDatabaseToFailoverGroup -ResourceGroupName $Rg -ServerName $Server `
-FailoverGroupName $Fog -Database $db | Out-Null
}
The user step is the same in T-SQL: IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'app_user') CREATE USER .... Role membership is checked with IS_ROLEMEMBER before ALTER ROLE.
Choosing the pool
The pool is chosen by a rule, not by a person: among the pools of the right server that still have room, take the one with the fewest databases, or the lowest used storage, according to a threshold from the config file. The rule lives in configuration so it can change without touching the code. A dry-run switch prints the chosen pool and stops.
Resume and retries
Each step announces itself (Step 4 of 7) and the processor accepts a start step. When a run fails at step 5, I fix the cause and relaunch the same request from step 5. Because steps are idempotent, relaunching from step 1 would also work, only slower. Transient Azure errors are retried a limited number of times with a growing delay; anything else stops the run and goes to the report.
Failures do not block the queue: a failed request is marked in error with the reason, and the processor continues with the next one. A cap on the number of requests per run limits the blast radius of a bug.
Reporting
Every run ends with a mail (HTML, built by a shared module) listing each request, its final status and the failed step, with the log attached. The queue row gets the same status. Success mails are only sent when something was actually processed, so an empty queue does not create noise.
Retour d'expérience
- Check state, don't trust status. The queue says "in progress" but the database may already exist from a previous crashed run. Reading Azure is the source of truth.
- Order matters. The failover group step must come after the database is fully online, and the secondary side needs the same pool logic; otherwise the geo-secondary lands in the wrong place.
- Resource locks on a resource group make creation or group changes fail in surprising ways; handle them explicitly and log it.
- Inventory last. Update the inventory only when everything else succeeded, otherwise reports show a database that is not really ready.
- Keep a dry run. Being able to see the plan for a request before executing it caught many bad requests early.
- Identity. Run under a managed identity, no secrets in the queue or the config; the identity needs rights on the pool, the group and the database only.