LLI Wiki — My Digital Knowledge Base

Lancer une requête sur des centaines de bases Azure SQL avec PowerShell

Construire la liste des cibles, paralléliser avec ForEach-Object -Parallel, réutiliser le jeton Entra, gérer délais et nouveaux essais, et produire un CSV de résultats et un rapport d'échecs.

Un jour ou l'autre, on vous demande : « peux-tu vérifier ça sur toutes les bases ? ». Avec quelques bases, on ouvre une fenêtre de requête. Avec des centaines de bases réparties sur de nombreux serveurs et pools élastiques, il faut un schéma reproductible : construire la liste des cibles, lancer le même T-SQL partout, regrouper les lignes au même endroit et, surtout, savoir précisément quelles bases ont échoué. Voici le schéma que j'utilise, en PowerShell pur.

Le flux général

flowchart LR
    A["Inventaire ou Resource Graph"] --> B["Liste des cibles (serveur, base, pool)"]
    B --> C["Garde-fous (contrôle lecture seule)"]
    C --> D["Jeton Entra (un par lot)"]
    D --> E["Workers parallèles (ForEach-Object -Parallel)"]
    E --> F["Nouvel essai si erreur transitoire"]
    F --> G["Table de résultats / CSV"]
    F --> H["Liste des échecs + raison"]

1. Construire la liste des cibles

Deux sources fonctionnent bien :

Source Avantages Inconvénients
Votre propre table d'inventaire (alimentée par un job) Rapide, peut porter des colonnes en plus (environnement, pool, responsable) En retard sur la réalité, il faut lui faire confiance
Azure Resource Graph Toujours à jour, multi-abonnements, pas besoin de login SQL Demande des droits de lecture sur les abonnements ; renvoie un nom serveur/base à découper

Exemple Resource Graph (module Az.ResourceGraph) :

$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' }

Quelle que soit la source, la liste devient un simple tableau d'objets avec Server, Database et Pool. Je l'enregistre en CSV à côté des résultats : elle documente ce que l'exécution a couvert.

2. Garde-fous : lecture seule par défaut

Un fan-out multiplie les erreurs par le nombre de bases. Mes règles :

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 'Mot-clé d''écriture détecté. Relisez la requête, puis relancez avec -AllowWrite.'
}

3. Parallélisme et réutilisation du jeton

ForEach-Object -Parallel (PowerShell 7) crée un runspace par worker ; -ThrottleLimit les plafonne. Les variables voyagent avec $using:. Le jeton Entra est demandé une fois et réutilisé par tous les workers, au lieu d'une authentification par base. Un jeton vit environ une heure : pour les longues exécutions, je traite la liste par lots et je renouvelle le jeton à chaque lot.

$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
    }
}

Invoke-Sqlcmd -AccessToken vient du module SqlServer. On ne rejoue que ce qui peut se résoudre seul (délai dépassé, base occupée, limitation, connexion coupée). Un « login failed » échoue identiquement à chaque fois : on le signale tout de suite, inutile de gaspiller trois essais.

4. Collecter et rapporter

$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} en échec" -f @($all | Where-Object Ok).Count, @($all | Where-Object { -not $_.Ok }).Count

La liste des échecs est la sortie la plus précieuse : elle permet de corriger la cause (utilisateur manquant, base en cours de déplacement) puis de relancer avec ce CSV comme liste de cibles.

Runspaces ou Azure Batch ?

Jusqu'à quelques milliers de requêtes courtes, une seule machine avec -Parallel suffit. Quand l'exécution est longue, planifiée, ou que la machine ne doit pas être mobilisée, j'empaquette le même script et je lance une tâche Batch par serveur (chaque tâche boucle sur ses propres bases avec -Parallel). Une tâche par serveur garde la concurrence par serveur sous contrôle et isole facilement une panne. L'empaquetage, les pools et la supervision sont traités dans mes autres articles sur Azure Batch ; seul ajout : l'identité est l'identité managée du pool, donc aucun secret nulle part.

Retour d'expérience

Mastodon LLIMastodon SocialFosstodon