Compression de sauvegarde (SQL Server)

  • Article

S’applique à :SQL Server

Cet article décrit la compression des sauvegardes SQL Server, notamment les restrictions, le compromis des performances de compression des sauvegardes, la configuration de la compression de sauvegarde et le ratio de compression. La compression de sauvegarde est prise en charge sur les éditions de SQL Server : Enterprise, Standard et Developer. Chaque édition de SQL Server 2008 (10.0.x) et versions ultérieures peut restaurer une sauvegarde compressée.

Avantages

  • Une sauvegarde compressée étant plus petite qu'une sauvegarde non compressée des mêmes données, la compression d'une sauvegarde requiert en général moins d'E/S de périphérique et, par conséquent, augmente souvent considérablement la vitesse de la sauvegarde.

    Pour plus d’informations, consultez Impact sur les performances de la compression des sauvegardes, plus loin dans cet article.

Restrictions

Les restrictions suivantes s'appliquent aux sauvegardes compressées :

  • Les sauvegardes compressées et non compressées ne peuvent pas co-exister dans un support de sauvegardes.

  • Les versions précédentes de SQL Server ne peuvent pas lire les sauvegardes compressées.

  • Les NTbackups ne peuvent pas partager une bande avec des sauvegardes SQL Server compressées.

Impact sur les performances de la compression des sauvegardes

Par défaut, la compression augmente considérablement l'utilisation de l'UC et l'UC supplémentaire consommée par le processus de compression peut avoir un impact néfaste sur les opérations simultanées. Par conséquent, il peut être préférable de créer une sauvegarde compressée de priorité basse dans une session où l’utilisation de l’UC est limitée par Resource Governor. Pour plus d’informations, consultez Utiliser Resource Governor pour limiter l’utilisation de processeur avec la compression de sauvegarde (Transact-SQL).

À compter de SQL Server 2022 (16.x), vous pouvez utiliser le déchargement intégré et l’accélération pour compresser les sauvegardes et décharger les ressources processeur pour la sauvegarde.

Pour obtenir une bonne image de vos performances d'E/S de sauvegarde, vous pouvez isoler l'E/S de sauvegarde en direction ou depuis des unités en évaluant les types suivants de compteurs de performance :

  • Compteurs de performance d'E/S Windows, tels que les compteurs de disque physique

  • Compteur Débit d’unité en octets/s de l’objet SQLServer:Backup Device

  • Compteur Débit de sauvegarde/restauration/s de l’objet SQLServer:Databases

Pour des informations sur les compteurs Windows, consultez l'aide de Windows. Pour obtenir des informations sur l’utilisation des compteurs SQL Server, consultez Utiliser des objets SQL Server.

Calculer le ratio de compression d’une sauvegarde compressée

Pour calculer le taux de compression d’une sauvegarde, utilisez les valeurs pour la sauvegarde dans les colonnes backup_size et compressed_backup_size de la table de l’historique backupset , comme suit :

backup_size:compressed_backup_size

Par exemple, un taux de compression 3:1 indique que vous économisez environ 66 % de l'espace disque. Pour effectuer une requête sur ces colonnes, vous pouvez utiliser l'instruction Transact-SQL suivante :

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

Le taux de compression d'une sauvegarde compressée dépend des données compressées. Divers facteurs peuvent avoir une incidence sur le taux de compression obtenu. Les facteurs majeurs sont :

  • Le type des données.

    Les données caractères se compressent plus que d'autres types de données.

  • La cohérence des données dans les lignes sur une page.

    En général, si une page contient plusieurs lignes dans lesquelles un champ contient la même valeur, une compression importante peut se produire pour cette valeur. En revanche, pour une base de données qui contient des données aléatoires ou qui contient une seule grande ligne par page, une sauvegarde compressée serait presque aussi importante qu'une sauvegarde non compressée.

  • Si les données sont chiffrées

    Le taux de compression des données chiffrées est beaucoup moins élevé que celui des données non chiffrées correspondantes. Par exemple, si les données sont chiffrées au niveau de la colonne avec Always Encrypted, ou avec un autre chiffrement au niveau de l’application, la compression des sauvegardes peut ne pas réduire la taille de manière significative.

    Pour plus d’informations sur la compression des bases de données chiffrées avec Transparent Data Encryption (TDE), consultez Compression des sauvegardes avec TDE.

  • Si la base de données est compressée.

    Si la base de données est compressée, compresser des sauvegardes peut réduire faiblement leur taille, voire pas du tout.

Compression des sauvegardes avec TDE

Avec SQL Server 2016 (13.x) et versions ultérieures, définir MAXTRANSFERSIZEsur une valeur supérieure à 65536 (64 Ko) permet d’utiliser un algorithme de compression optimisé pour les bases de données chiffrées avec Transparent Data Encryption (TDE), qui chiffre d’abord une page, la compresse, puis la chiffre de nouveau. Si MAXTRANSFERSIZE n’est pas spécifiée, ou si MAXTRANSFERSIZE = 65536 (64 Ko) est utilisé, la compression de sauvegarde pour les bases de données chiffrées avec TDE compresse directement les pages chiffrées et peut ne pas fournir de bons taux de compression. Pour plus d’informations, consultez Backup Compression for TDE-enabled Databases.

À partir de SQL Server 2019 (15.x) CU5, la définition de MAXTRANSFERSIZE n’est plus nécessaire pour activer cet algorithme de compression optimisé avec TDE. Si la commande de sauvegarde est spécifiée WITH COMPRESSION ou que la configuration serveur de compression par défaut des sauvegardes est définie sur 1, MAXTRANSFERSIZE sera automatiquement augmentée à 128 K pour activer l’algorithme optimisé. Si MAXTRANSFERSIZE est spécifiée dans la commande Backup avec une valeur > 64 Ko, la valeur fournie est respectée. En d’autres termes, SQL Server ne diminue jamais automatiquement la valeur, elle l’augmente uniquement. Si vous avez besoin de sauvegarder une base de données chiffrée TDE avec MAXTRANSFERSIZE = 65536, vous devez spécifier WITH NO_COMPRESSION ou vous assurer que la configuration serveur de compression par défaut des sauvegardes est définie sur 0.

Pour plus d’informations, consultez BACKUP (Transact-SQL).

Allocation d’espace pour le fichier de sauvegarde

Pour les sauvegardes compressées, la taille du fichier de sauvegarde final dépend de la capacité de compression des données. Or, celle-ci n'est pas connue avant la fin de l'opération de sauvegarde. Par conséquent, par défaut, lors de la sauvegarde d'une base de données faisant appel à la compression, le moteur de base de données utilise un algorithme de préallocation pour le fichier de sauvegarde. Cette algorithme préalloue un pourcentage prédéfini de la taille de la base de données pour le fichier de sauvegarde. Si davantage d'espace est requis au cours de l'opération de sauvegarde, le moteur de base de données augmente la taille du fichier. Si la taille finale est inférieure à l'espace alloué, à la fin de l'opération de sauvegarde, le moteur de base de données réduit le fichier à la taille finale réelle de la sauvegarde.

Pour permettre au fichier de sauvegarde de croître autant que nécessaire uniquement afin d'atteindre sa taille définitive, utilisez l'indicateur de trace 3042. L'indicateur de trace 3042 permet à l'opération de sauvegarde de contourner l'algorithme de préallocation de la compression de sauvegarde par défaut. Cet indicateur de trace est utile si vous devez économiser de l'espace en allouant uniquement la taille réelle requise pour la sauvegarde compressée. Toutefois, le recours à cet indicateur de trace peut entraîner une légère baisse des performances (augmentation possible de la durée de l'opération de sauvegarde).

Tâches associées

Étapes suivantes

Backup Overview (SQL Server)
Indicateurs de trace (Transact-SQL)