Optimisation des performances de sauvegarde et de restauration dans SQL Server
MicrosoftSQL Server propose les deux méthodes suivantes pour accélérer les opérations de sauvegarde et de restauration :
L'utilisation simultanée de plusieurs périphériques permet aux sauvegardes d'être écrites en parallèle sur toutes ces unités. La vitesse des unités de sauvegarde est un goulet d'étranglement potentiel qui peut limiter le débit des sauvegardes. L'utilisation de plusieurs périphériques peut augmenter le débit proportionnellement au nombre de périphériques utilisés. Inversement, la sauvegarde pourra être restaurée en parallèle à partir de ces différents périphériques. Pour plus d'informations, consultez « Utilisation de plusieurs supports ou périphériques » plus loin dans cette rubrique.
L'utilisation d'une combinaison de sauvegardes complètes, différentielles et (pour les modes de restauration complète et de récupération utilisant les journaux de transactions) des journaux de transactions permet de réduire la durée de la récupération. Les sauvegardes différentielles de bases de données sont en général plus rapides à créer que les sauvegardes complètes de bases de données et réduisent la quantité de journaux de transactions nécessaires pour récupérer la base de données. Pour plus d'informations, consultez Création de sauvegardes différentielles et complètes d'une base de données SQL Server.
Utilisation de plusieurs supports ou périphériques
La copie des données et du journal des transactions depuis les unités de sauvegarde vers les fichiers de la base de données et du journal des transactions est effectuée par des threads de lecture/écriture ; un thread est affecté à chaque unité de sauvegarde. Les performances sont limitées soit par la capacité des périphériques de sauvegarde à livrer les données, soit par la capacité des fichiers de base de données et du journal des transactions à recevoir les données. C'est pour cette raison que les performances augmentent avec le nombre d'unités de sauvegarde, jusqu'à ce que le débit maximal des fichiers de base de données et du journal des transactions pour accepter les données soit atteint.
L'utilisation de plusieurs périphériques de sauvegarde pour les opérations de sauvegarde et de restauration permet à SQL Server d'utiliser des entrées/sorties (E/S) parallèles pour augmenter la vitesse des opérations de sauvegarde et de restauration, car toutes les unités de sauvegarde peuvent être écrites ou lues en même temps. Pour les entreprises possédant des bases de données volumineuses, l'utilisation d'un grand nombre de périphériques de sauvegarde peut considérablement réduire le temps consacré aux opérations de sauvegarde et de restauration. SQL Server prend en charge jusqu'à 64 unités de sauvegarde pour une seule opération de sauvegarde.
Lorsqu'une sauvegarde est écrite sur plusieurs unités de sauvegarde, plusieurs points de synchronisation internes sont effectués. Le point de ce type le plus important est atteint lorsque toutes les données d'une base de données ont été sauvegardées et que le journal des transactions est sur le point d'être sauvegardé.
Important
Si vous utilisez plusieurs unités de sauvegarde pour effectuer les opérations de sauvegarde, le support de sauvegarde impliqué ne peut être utilisé que pour des opérations de sauvegarde SQL Server. Pour plus d'informations, consultez Utilisation du support de sauvegarde.
La création et la restauration de sauvegardes à l'aide de plusieurs unités de sauvegarde sont identiques à la création et à la restauration effectuées avec une seule unité. La seule différence est que vous devez spécifier toutes les unités de sauvegarde impliquées dans l'opération, au lieu d'une seule. Par exemple, lorsqu'une sauvegarde de base de données est créée à l'aide de trois unités de sauvegarde sur bande telles que \\.\TAPE0, \\.\TAPE1 et \\.\TAPE2, chacune des unités sur bande doit être spécifiée comme prenant part à l'opération de sauvegarde, même si un nombre inférieur d'unités de sauvegarde sur bande peut être utilisé lors de la restauration ultérieure de la sauvegarde.
Lorsque vous créez une sauvegarde sur plusieurs unités de sauvegarde appartenant à des supports amovibles, celles-ci peuvent fonctionner à des vitesses différentes et les volumes de support peuvent avoir différentes quantités d'espace disponible. Au cours de l'opération de sauvegarde, si le volume de support sur une unité de sauvegarde manque d'espace, l'opération arrête l'écriture sur l'unité de sauvegarde et vous demande un nouveau volume de support. L'unité demeure bloquée jusqu'à ce que vous ayez remplacé le volume de support saturé par un volume vide. En attendant, l'opération de sauvegarde continue à écrire des données sur les unités dont le support n'est pas encore saturé. Lorsque vous remplacez le volume de support saturé, son unité devient disponible et la sauvegarde recommence à y écrire des données. Toutefois, si un point de synchronisation interne se produit alors qu'une unité est bloquée, l'opération de sauvegarde s'interrompt complètement jusqu'à ce que l'unité devienne de nouveau disponible.
Exemple
Considérons un scénario dans lequel trois unités de sauvegarde sur bande de vitesse égale stockent une sauvegarde complète de base de données. Les deux premières bandes disposent de 10 gigaoctets (Go) d'espace, tandis que la troisième ne dispose que de 5 Go. Si une base de données de 20 Go est sauvegardée sur les trois unités de sauvegarde sur bande simultanément, la troisième bande sera saturée avant la fin de la sauvegarde. Après l'écriture de 5 Go de données sur la troisième bande, l'opération de sauvegarde arrête d'y enregistrer des données. L'opération bloque cette unité et demande une nouvelle bande. En attendant, l'opération de sauvegarde continue d'écrire des données sur les deux autres unités. Toutefois, avant que la troisième bande ne soit remplacée, un point de synchronisation interne se produit. À ce stade, la totalité de l'opération de sauvegarde s'interrompt jusqu'à ce qu'une nouvelle bande ait été montée sur la troisième unité.
Optimisation des performances pour les sauvegardes complètes et différentielles
La création d'une sauvegarde complète ou différentielle comprend les étapes suivantes :
Copie des données des fichiers de la base de données sur les périphériques de sauvegarde.
Copie de la partie du journal des transactions nécessaire pour restaurer par progression la base de données dans un état en cohérence avec les mêmes unités de sauvegarde.
La création d'une sauvegarde différentielle est identique à la création d'une sauvegarde complète, sauf que seules les données modifiées sont copiées. La sauvegarde d'un fichier de base de données consiste simplement à copier les données du fichier sur les unités de sauvegarde.
Les fichiers de base de données utilisés pour stocker la base de données sont triés par une unité de disque et un thread de lecture est affecté à chaque unité. Le thread de lecture lit les données provenant des fichiers de base de données. Un thread d'écriture est affecté à chaque unité de sauvegarde. Il procède à l'écriture des données sur l'unité de sauvegarde. Le nombre d'opérations de lecture en parallèle peut être augmenté par l'entrelacement des fichiers de base de données sur un plus grand nombre d'unités logiques. De même, les opérations d'écriture en parallèle peuvent être accélérées grâce à l'utilisation d'un plus grand nombre d'unités de sauvegarde.
Généralement, le goulet d'étranglement se produit au niveau des fichiers de base de données ou des unités de sauvegarde. Si le débit de lecture total est supérieur au débit total des unités de sauvegarde, le goulet d'étranglement survient alors du côté des unités de sauvegarde. L'ajout d'unités de sauvegarde supplémentaires (et de contrôleurs SCSI le cas échéant) permet d'améliorer les performances. Toutefois, si le débit total de sauvegarde est supérieur au débit total de lecture, augmentez ce dernier, par exemple en ajoutant plus de fichiers de base de données ou d'unités (ou en ajoutant davantage de disques à un périphérique RAID).
Optimisation des performances de sauvegarde du journal des transactions
La création d'une sauvegarde des journaux de transactions suppose simplement la copie de la partie du journal non encore sauvegardée sur les unités de sauvegarde. Même s'il peut y avoir plusieurs fichiers du journal des transactions, ce dernier est logiquement un flux lu séquentiellement par un thread.
Un thread d'écriture est affecté à chaque unité de sauvegarde. Des performances supérieures peuvent être obtenues par l'ajout d'unités de sauvegarde.
Un goulet d'étranglement peut-être dû à l'unité de disques contenant les fichiers du journal des transactions ou à l'unité de sauvegarde, selon leur vitesse relative et le nombre d'unités de sauvegarde utilisées. L'ajout d'unités de sauvegarde augmente la vitesse de façon linéaire jusqu'à ce que la capacité de l'unité de disque contenant les fichiers du journal des transactions soit atteinte ; après quoi, aucun gain supplémentaire n'est possible sans augmenter la vitesse des unités de disque contenant le journal des transactions, par exemple à l'aide de l'agrégation par bandes.
Optimisation des performances de restauration
La restauration d'une sauvegarde de base de données ou différentielle comporte quatre étapes :
Création des fichiers de base de données et du journal des transactions s'ils n'existent pas déjà.
Copie des données des périphériques de sauvegarde dans les fichiers de base de données.
Copie du journal des transactions à partir des fichiers du journal des transactions.
Restauration par progression du journal des transactions, puis relance de la récupération si nécessaire.
L'application d'une sauvegarde du journal des transactions comprend deux étapes :
Copie des données des périphériques de sauvegarde dans le fichier du journal des transactions.
Restauration par progression du journal des transactions.
La restauration d'un fichier de base de données comporte deux étapes :
Création des fichiers de base de données manquants.
Copie des données des périphériques de sauvegarde dans les fichiers de base de données.
Initialisation des fichiers
Si les fichiers de la base de données et du journal des transactions n'existent pas déjà, ils doivent être créés avant que les données puissent leur être restaurées. Ces deux types de fichiers sont créés et leur contenu initialisé à zéro. Des threads de travail séparés créent et initialisent les fichiers en parallèle. Les fichiers de la base de données et du journal des transactions sont triés par unité de disque et un thread de travail séparé est affecté à chacune de ces unités. Comme la création et l'initialisation des fichiers requièrent un débit très élevé, une répartition uniforme des fichiers sur les disques logiques disponibles permet d'améliorer les performances.
Initialisation instantanée des fichiers
Dans SQL Server 2005 et versions ultérieures, les fichiers de données peuvent être initialisés instantanément, permettant ainsi l'exécution rapide des opérations de restauration de bases de données ou de groupes de fichiers. L'initialisation instantanée des fichiers récupère l'espace disque utilisé sans le remplir avec des zéros. Au lieu de cela, le contenu du disque est remplacé par les nouvelles données inscrites dans les fichiers. L'initialisation des fichiers journaux nécessite toujours la remise à zéro, mais elle se produira parallèlement au transfert des données à partir de la sauvegarde. La phase de restauration par progression de l'opération de restauration ne démarrera pas avant le transfert de la totalité des données et l'initialisation de l'intégralité du journal.
[!REMARQUE]
L'initialisation instantanée des fichiers est disponible uniquement sur les systèmes Microsoft Windows XP, Windows Server 2003 ou ultérieurs.
Pour utiliser l'initialisation instantanée des fichiers, vous devez exécuter le compte de service MSSQLSERVER sous un compte Windows et affecter le privilège spécial Windows SE_MANAGE_VOLUME_NAME à ce compte Windows. Ce privilège est affecté par défaut au groupe Administrateurs Windows. Si vous disposez de droits d'administrateur système, vous pouvez affecter ce privilège en ajoutant le compte Windows à la stratégie de sécurité Effectuer des tâches de gestion des volumes. Pour plus d'informations sur l'affectation de droits utilisateur, consultez la documentation de Windows.
Optimisation des performances d'un périphérique de sauvegarde sur bande
Plusieurs variables influent sur les performances des unités de sauvegarde sur bande et permettent aux performances des opérations de sauvegarde et de restauration de SQL Server d'augmenter proportionnellement au nombre de périphériques à bandes ajoutés :
Taille des blocs de données du logiciel.
Nombre de périphériques à bandes partageant une interface SCSI (Small Computer System Interface).
Type de périphérique à bandes.
La taille des blocs de données du logiciel est calculée par SQL Server pour assurer des performances optimales et ne doit pas être modifiée. La valeur BLOCKSIZE maximale est de 64 Ko.
De nombreux lecteurs de bande rapides fonctionnent mieux s'ils sont équipés d'un bus SCSI spécialisé pour chaque lecteur utilisé. Les lecteurs dont la vitesse de transfert native dépasse 50 % de la vitesse du bus SCSI doivent se trouver sur un bus SCSI dédié pour éviter les pertes de performances. Pour plus d'informations sur les paramètres qui affectent les performances des lecteurs de bande, consultez la documentation du lecteur de bandes.
Important
Ne placez jamais un lecteur de bande sur le même bus SCSI que des disques ou un lecteur de CD-ROM. Les opérations de traitement des erreurs de ces périphériques sont mutuellement incompatibles.
Lorsque vous effectuez plusieurs opérations de sauvegarde sur une bande chargée, vous pouvez améliorer les performances en spécifiant NOREWIND. Cette option indique à SQL Server de maintenir la ou les bandes ouvertes après l'opération de sauvegarde. NOREWIND implique NOUNLOAD.
Optimisation des performances des périphériques de sauvegarde sur disque
La vitesse brute d'E/S d'un périphérique de sauvegarde sur disque affecte ses performances et permet aux performances des opérations de sauvegarde et de restauration de SQL Server d'augmenter proportionnellement au nombre d'unités de disque ajoutées.
L'utilisation d'un périphérique RAID comme unité de sauvegarde sur disque doit être soigneusement évaluée. Par exemple, le RAID de niveau 5 présente des performances en écriture médiocres, environ la même vitesse que pour un disque unique (en raison de la gestion des informations de parité). En outre, la vitesse brute d'ajout de données dans un fichier est considérablement moins élevée que la vitesse brute d'écriture du périphérique.
Si le périphérique de sauvegarde est fortement entrelacé, de sorte que la vitesse maximale d'écriture sur le périphérique de sauvegarde dépasse la vitesse à laquelle il peut ajouter des données dans un fichier, il peut alors s'avérer judicieux de disposer plusieurs périphériques de sauvegarde logiques sur le même ensemble entrelacé. En d'autres termes, les performances de sauvegarde peuvent être améliorées en plaçant plusieurs familles de supports de sauvegarde sur la même unité logique. Toutefois, une approche empirique est nécessaire pour déterminer s'il s'agit d'un gain ou d'une perte pour chaque environnement. Il vaut mieux, généralement, placer chaque périphérique de sauvegarde sur une unité de disque séparée.
Habituellement, sur un bus SCSI, seuls quelques disques peuvent être exploités à la vitesse maximale, bien que les bus Ultra-wide et Ultra-2 puissent en traiter davantage. Toutefois, pour obtenir des performances optimales, il convient d'apporter toute son attention à la configuration.
Pour plus d'informations sur les valeurs influant sur les performances des disques, consultez la documentation du fournisseur.
Compression de données
Les lecteurs de bande évolués sont équipés de la compression intégrée de données matérielles qui peut augmenter considérablement la vitesse de transfert des données vers le lecteur. La compressibilité des données réelles de la base de données dépend des données elles-mêmes et des lecteurs de bande utilisés. Les taux de compression habituels vont de 1,2:1 à 2:1 pour une gamme étendue de bases de données. Ce taux de compression est généralement celui des données de nombreuses applications de gestion, mais certaines bases de données peuvent connaître des taux beaucoup plus élevés ou beaucoup plus faibles. Par exemple, une base de données constituée pour l'essentiel d'images déjà compressées ne pourra pas être compressée davantage par les lecteurs de bande. Pour plus d'informations sur la compression de données, consultez la documentation du fournisseur de lecteurs de bande.
Par défaut, SQL Server prend en charge la compression matérielle, mais cette procédure peut être désactivée à l'aide de l'indicateur de trace 3205. La désactivation de la compression par matériel peut, très exceptionnellement, améliorer les performances de sauvegarde. Si par exemple les données sont déjà compressées au maximum, vous éviterez, en désactivant la compression matérielle, que le périphérique à bandes perde du temps à essayer de les compresser davantage.
Pour plus d'informations sur les indicateurs de trace, consultez Indicateurs de trace (Transact-SQL).
Compression de sauvegardes
Par défaut, sauvegarder en utilisant compression de la sauvegarde augmente considérablement l'utilisation de l'UC et l'UC supplémentaire consommée par le processus de compression peut nuire aux opérations simultanées. Par conséquent, il peut être préférable, dans une session où l'utilisation de l'UC est limitée, de créer une sauvegarde compressée de priorité basse à l'aide du gouverneur de ressources en cas de contention du processeur. Pour plus d'informations, consultez Procédure : utiliser le gouverneur de ressources pour limiter l'utilisation de l'UC par compression de sauvegarde (Transact-SQL).
Quantité de données transférées sur bande
La création d'une sauvegarde de données ou différentielle conserve uniquement la partie de la base de données qui contient des données réelles ; l'espace non utilisé n'est pas sauvegardé. Les sauvegardes sont donc plus rapides.
Bien que les bases de données SQL Server puissent être configurées pour croître automatiquement au fur et à mesure des besoins, vous pouvez continuer à réserver de l'espace dans la base de données pour garantir la disponibilité de cet espace. Ceci ne ralentit pas le débit de sauvegarde et ne rallonge pas la durée globale de la sauvegarde de la base de données.
Optimisation de la synchronisation de la copie des journaux de transactions
Lorsque vous essayez de synchroniser une destination de copie des journaux de transactions, il n'est pas nécessaire d'utiliser WITH STANDBY entre les étapes RESTORE LOG.