LLI Wiki — My Digital Knowledge Base

Running one query on hundreds of Azure SQL databases with PowerShell

Build the target list, parallelise with ForEach-Object -Parallel, reuse the Entra token, handle timeouts and retries, and produce a results CSV plus a failure report.

Sooner or later a DBA gets the question: "can you check this on every database?" With a handful of databases you open a query window. With hundreds spread over many servers and elastic pools, you need a repeatable fan-out: build the list of targets, run the same T-SQL everywhere, collect the rows in one place and, above all, know exactly which databases failed. This is the pattern I use, written in plain PowerShell.

The overall flow

flowchart LR
    A["Inventory table or Resource Graph"] --> B["Target list (server, database, pool)"]
    B --> C["Guard-rails (read-only check)"]
    C --> D["Entra token (one per chunk)"]
    D --> E["Parallel workers (ForEach-Object -Parallel)"]
    E --> F["Retry on transient errors"]
    F --> G["Results table / CSV"]
    F --> H["Failed list + reason"]

1. Build the target list

Two sources work well:

Source Pros Cons
Your own inventory table (refreshed by a job) Fast, can carry extra columns (environment, pool, owner) Lags behind reality, you must trust it
Azure Resource Graph Always current, cross-subscription, no SQL login needed Needs read rights on the subscriptions; returns a server/database name you must split

A Resource Graph example (Az.ResourceGraph module):

$kql = @"
resources
| where type =~ 'microsoft.sql/servers/databases'
| where name !endswith '/master'
| project Server = tostring(split(name, '/')[0]),
          Database = tostring(split(name, '/')[1]),
          Pool = tostring(split(tostring(properties.elasticPoolId), '/')[-1])
"@
$targets = @()
$skip = $null
do {
    $page = Search-AzGraph -Query $kql -First 1000 -SkipToken $skip
    $targets += $page
    $skip = $page.SkipToken
} while ($skip)
$targets | ForEach-Object { $_.Server += '.database.windows.net' }

Whatever the source, the list becomes a plain object array with Server, Database and Pool. Save it as a CSV next to the results: it documents what the run covered.

2. Guard-rails: read-only by default

A fan-out multiplies mistakes by the number of databases. My rules:

param(
    [Parameter(Mandatory)][string]$QueryFile,
    [int]$Throttle = 8,
    [int]$TimeoutSec = 120,
    [switch]$AllowWrite
)
$sql = Get-Content -Path $QueryFile -Raw
if (-not $AllowWrite -and $sql -match '(?i)\b(insert|update|delete|merge|drop|alter|create|truncate|exec(ute)?)\b') {
    throw 'Write keyword detected. Review the query, then rerun with -AllowWrite.'
}

3. Parallelism and token reuse

ForEach-Object -Parallel (PowerShell 7) gives one runspace per worker; -ThrottleLimit caps them. Variables travel with $using:. The Entra token is requested once and reused by all workers, instead of one authentication per database. A token lives about an hour, so for long runs I process the list in chunks and refresh the token for each chunk.

$all = foreach ($chunk in ($targets | Group-Object { [math]::Floor([array]::IndexOf($targets, $_) / 200) })) {
    $tok = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net/').Token
    if ($tok -is [securestring]) { $tok = $tok | ConvertFrom-SecureString -AsPlainText }

    $chunk.Group | ForEach-Object -ThrottleLimit $Throttle -Parallel {
        $t = $_
        $out = [pscustomobject]@{ Server = $t.Server; Database = $t.Database; Ok = $false; Error = $null; Rows = @() }
        foreach ($attempt in 1..3) {
            try {
                $out.Rows = @(Invoke-Sqlcmd -ServerInstance $t.Server -Database $t.Database `
                    -Query $using:sql -AccessToken $using:tok `
                    -QueryTimeout $using:TimeoutSec -ConnectionTimeout 30 -ErrorAction Stop)
                $out.Ok = $true; $out.Error = $null; break
            } catch {
                $out.Error = $_.Exception.Message
                if ($out.Error -notmatch 'timeout|40613|40501|10928|10929|49918|forcibly closed') { break }
                Start-Sleep -Seconds (5 * $attempt)
            }
        }
        $out
    }
}

Note that Invoke-Sqlcmd -AccessToken comes from the SqlServer module. Retry only what can heal by itself (timeouts, database busy, throttling, dropped connection). A "login failed" error fails identically every time: report it immediately, do not waste three attempts.

4. Collect and report

$rows = $all | Where-Object Ok | ForEach-Object {
    $s = $_.Server; $d = $_.Database
    $_.Rows | Select-Object -Property * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors |
        Select-Object @{n='Server';e={$s}}, @{n='Database';e={$d}}, *
}
$stamp = Get-Date -Format 'yyyyMMddHHmmss'
$rows | Export-Csv "result_$stamp.csv" -NoTypeInformation -Encoding utf8BOM
$all | Where-Object { -not $_.Ok } | Select-Object Server, Database, Error |
    Export-Csv "failed_$stamp.csv" -NoTypeInformation -Encoding utf8BOM
"{0} ok, {1} failed" -f @($all | Where-Object Ok).Count, @($all | Where-Object { -not $_.Ok }).Count

The failed list is the most valuable output. It lets you fix the cause (missing user, database being moved) and rerun with that CSV as the target list.

Runspaces or Azure Batch?

For up to a few thousand short queries, a single machine with -Parallel is enough. When the run is long, scheduled, or the machine must not be tied up, I package the same script and run one Batch task per server (each task loops over its own databases with -Parallel). One task per server keeps concurrency per server under control and makes a failure easy to isolate. Packaging, pools and monitoring are covered in my other Azure Batch articles; the only addition is that the identity is the Batch pool's managed identity, so there is no secret anywhere.

Lessons learned

Mastodon LLIMastodon SocialFosstodon