Exporter des milliers de bases Azure SQL vers des fichiers avec bcp et Azure Batch
Une tâche Batch par base, options bcp (natif ou caractère, séparateurs, UTF-8, taille de paquet), authentification Entra, compression, envoi blob, contrôle des lignes, relance ciblée et nettoyage.
Exporter une base vers des fichiers plats est facile. En exporter des milliers chaque nuit, de façon fiable, en sachant lesquelles ont échoué, est un vrai sujet d'ingénierie. Ma réponse : une tâche Azure Batch par base, bcp pour les données, compression, envoi vers le stockage blob, contrôle des nombres de lignes, et un nouvel essai qui ne touche que les tâches en échec. (L'architecture Batch, l'empaquetage et la supervision sont traités dans mes autres articles sur Azure Batch ; ici je me concentre sur l'export lui-même.)
Le flux général
flowchart TD
A["Planificateur (pipeline)"] --> B["Liste des cibles (serveur, base)"]
B --> C["Job Batch : une tâche par base"]
C --> D["bcp out : un fichier par table"]
D --> E["Contrôle des nombres de lignes"]
E --> F["Compression (zip ou gzip)"]
F --> G["Envoi vers le stockage blob"]
G --> H["Blob marqueur de fin"]
C --> I["Résultat de la tâche (succès / échec)"]
I --> J["Relance des seules tâches en échec"]
J --> C
K["Règle de cycle de vie"] --> G
Pourquoi une tâche par base
Une tâche est l'unité naturelle de panne : une base lente, verrouillée ou injoignable ne doit pas bloquer les autres. Batch fournit gratuitement l'ordonnancement, l'état de la tâche, stdout/stderr par tâche et un nombre d'essais par tâche. La taille du pool fixe le parallélisme ; je le garde modeste pour ne jamais surcharger les serveurs sources (ni leurs pools élastiques). Les tâches doivent être idempotentes : relancer une tâche écrase sa propre sortie, et rien d'autre.
Choisir les options de bcp
| Option | Usage |
|---|---|
-n (natif) |
Le plus rapide, types exacts, mais lisible seulement par bcp/SQL Server. Bon pour des transferts SQL vers SQL. |
-c (caractère) |
Fichiers texte lisibles par tout outil. À utiliser quand le consommateur n'est pas SQL Server. |
-w |
Format caractère Unicode (UTF-16). Fichiers plus lourds. |
-C 65001 |
Page de code UTF-8 en mode caractère (versions récentes de bcp). Sans cela, la page de code du client est utilisée et les accents peuvent devenir des ? sans erreur. |
-t / -r |
Séparateurs de champ et de ligne. Choisissez un séparateur de champ qui ne peut pas apparaître dans les données (le pipe n'est souvent pas assez sûr : testez) et gardez \n comme séparateur de ligne. |
-a |
Taille des paquets réseau. Par défaut 4096 octets ; une valeur plus grande (par exemple 16384) accélère souvent les grosses tables. Mesurez avant et après. |
-o |
Écrit les messages de bcp dans un fichier : il contient la ligne « rows copied » utilisée pour le contrôle. |
queryout |
Exporte une requête (vues, colonnes calculées, lignes filtrées) au lieu d'une table entière. |
Le compromis à retenir : le mode caractère est portable mais n'a ni guillemets ni échappement. Si votre texte peut contenir les séparateurs ou des retours à la ligne, le fichier est corrompu sans aucune erreur. Choisissez les séparateurs après avoir regardé les données, et vérifiez avec les nombres de lignes.
Authentification
Pour Azure SQL, je veux des identités Entra et aucun mot de passe. Les versions récentes de bcp acceptent -G pour l'authentification Entra ; la variante qui fonctionne (interactive, mot de passe, identité managée) dépend des versions de bcp et du pilote ODBC présents sur l'image Batch. Testez donc sur l'image réelle du pool et lisez la documentation de la version que vous déployez. Si -G ne convient pas, le plan B consiste à lire les données soi-même avec une bibliothèque cliente SQL et un jeton d'accès, puis à écrire le fichier depuis le code : plus long à écrire, mais entièrement maîtrisé. Dans les deux cas, l'identité n'a besoin que de droits de lecture sur la source.
Le script de la tâche
param([string]$Server, [string]$Database, [string]$Run, [string]$Account)
$work = Join-Path $env:AZ_BATCH_TASK_WORKING_DIR $Database
New-Item -ItemType Directory -Path $work -Force | Out-Null
$tok = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net/').Token
if ($tok -is [securestring]) { $tok = $tok | ConvertFrom-SecureString -AsPlainText }
$tables = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -AccessToken $tok `
-Query "SELECT s.name AS sch, t.name AS tbl FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id"
$report = foreach ($t in $tables) {
$name = "$($t.sch).$($t.tbl)"
$file = Join-Path $work "$name.txt"
$log = Join-Path $work "$name.bcp.log"
& bcp "[$($t.sch)].[$($t.tbl)]" out $file -S $Server -d $Database -G `
-c -C 65001 -t '|' -r '\n' -a 16384 -o $log | Out-Null
$copied = [long](Select-String -Path $log -Pattern '^(\d+) rows copied' | ForEach-Object { $_.Matches[0].Groups[1].Value })
$source = (Invoke-Sqlcmd -ServerInstance $Server -Database $Database -AccessToken $tok `
-Query "SELECT COUNT_BIG(*) AS n FROM [$($t.sch)].[$($t.tbl)]").n
[pscustomobject]@{ Table = $name; Exported = $copied; Source = $source; Ok = ($copied -eq $source -and $LASTEXITCODE -eq 0) }
}
if ($report.Where({ -not $_.Ok })) { $report | Format-Table | Out-String | Write-Error; exit 1 }
L'appel avec -G est schématique : ajoutez les arguments d'identification dont votre version de bcp a besoin.
Nombres de lignes, compression, envoi
Le contrôle compare ce que bcp annonce avec un COUNT_BIG(*) exécuté sur la même source. Sur une base vivante, les deux peuvent différer de quelques lignes : exportez depuis un réplica en lecture seule ou dans une fenêtre calme, ou acceptez une tolérance et journalisez-la. Un contrôle en échec fait échouer la tâche, qui apparaît dans Batch comme n'importe quelle autre panne.
Ensuite on compresse le dossier (Compress-Archive ou gzip par fichier) et on envoie avec l'identité Entra du pool, jamais avec une clé de stockage :
$zip = Join-Path $env:AZ_BATCH_TASK_WORKING_DIR "$Database.zip"
Compress-Archive -Path "$work\*" -DestinationPath $zip -Force
$ctx = New-AzStorageContext -StorageAccountName $Account -UseConnectedAccount
Set-AzStorageBlobContent -File $zip -Container 'exports' -Blob "$Run/$Database.zip" -Context $ctx -Force | Out-Null
'ok' | Set-Content "$work\done.txt"
Set-AzStorageBlobContent -File "$work\done.txt" -Container 'exports' -Blob "$Run/$Database.done" -Context $ctx -Force | Out-Null
Le petit blob .done est écrit en dernier : un consommateur qui le voit sait que l'archive est complète. Supprimez le dossier de travail local à la fin, le disque du nœud Batch est petit.
Relancer uniquement les tâches en échec
Une tâche Batch enregistre son résultat. Après l'exécution, je liste les tâches dont le résultat d'exécution est un échec et je les soumets de nouveau sous un nouvel identifiant (par exemple avec le suffixe -r1) ; comme les tâches sont idempotentes et que le marqueur .done indique ce qui est terminé, la relance ne coûte que les bases en échec. Gardez un plafond de relances automatiques et envoyez les échecs restants dans un rapport au lieu de boucler indéfiniment.
Nettoyer les anciens exports
N'écrivez pas de script de nettoyage si la plateforme le fait déjà : une règle de gestion du cycle de vie des blobs sur le compte de stockage supprime les blobs plus vieux de N jours sous le préfixe des exports. Organisez les blobs par date d'exécution (<run>/<base>.zip) pour que la règle reste simple et qu'une exécution puisse être supprimée en bloc.
Retour d'expérience
- Vérifiez toujours les nombres de lignes : une troncature silencieuse coûte bien plus cher que le contrôle.
- Testez les caractères accentués et les retours à la ligne embarqués avant de choisir le mode caractère et les séparateurs.
- Choisissez explicitement la page de code ; ne comptez jamais sur la valeur par défaut du client.
- Gardez le pool assez petit pour les serveurs sources, et exportez depuis un secondaire quand il y en a un.
- Rendez chaque tâche idempotente et chaque nom de sortie prévisible : relances et nettoyage deviennent triviaux.
- Ne laissez jamais de données exportées sur le nœud : nettoyez dans un bloc
finally.