LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon