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 :
- Le script refuse les requêtes contenant des mots-clés d'écriture sauf si
-AllowWriteest passé. C'est une regex rudimentaire, pas une frontière de sécurité, mais elle attrape la faute de frappe. - Une exécution en écriture démarre sur une seule base témoin, on regarde le résultat, puis seulement on continue.
- La vraie protection vient de l'identité : pour les exécutions en lecture, utilisez un compte qui n'a que des droits de lecture.
- On journalise toujours le texte exact de la requête et la liste des cibles utilisée.
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
- Plafonnez la concurrence par pool élastique, pas seulement globalement : les bases d'un même pool partagent les mêmes ressources, et 20 requêtes lourdes sur un pool font mal à la production.
- Écrivez les résultats partiels sur disque au fil de l'eau : une exécution interrompue garde ce qu'elle a déjà collecté.
- Les requêtes lourdes (scans complets) se lancent hors heures de pointe avec un débit faible.
- Le coût de démarrage compte : charger des modules dans chaque runspace peut durer plus longtemps que la requête. Gardez des workers légers.
- Ajoutez
ServeretDatabaseà chaque ligne de sortie, sinon le CSV est inutilisable.