Forum aux questions sur SQL Déplacement, performances, réglage, sauvegarde et mise en miroir de bases de données

Article
08/24/2016

Paul S. Randal

Déplacement de nouvelle baie

Q : Notre système RAID se remplit à vitesse grand V ; nous devons donc déplacer certaines bases de données SQL Server 2005. La nouvelle baie est prête et j'ai préparé le déplacement des bases de données. Je viens de découvrir que l'une des bases de données est un serveur de publication de réplication transactionnelle, et je sais que cela signifie qu'il est impossible de déplacer la base de données. Que dois-je faire ?

R : Bonne nouvelle : seul SQL Server 2000 (et versions antérieures) présente une limitation empêchant de déplacer une base de données de publication sans réinitialiser la réplication transactionnelle ou modifier directement les diverses tables système.

Avec SQL Server 2005 et SQL Server 2008, il existe un processus documenté qui vous permet de déplacer une base de données sans avoir à toucher à la réplication transactionnelle, tant que la base de données demeure attachée à la même instance de SQL Server. Vous devez accepter un certain temps mort, car il n'existe aucun moyen de déplacer un fichier de base de données pendant qu'il est encore en ligne. La procédure est la suivante :

Tout d'abord, mettez la base de données hors connexion à l'aide du code suivant. Si des utilisateurs sont connectés à la base de données, vous devrez d'abord les déconnecter pour que ce processus réussisse :

ALTER DATABASE MyDatabaseName SET OFFLINE;

Ensuite, copiez les fichiers de données au nouvel emplacement. Utilisez une copie plutôt qu'un déplacement, afin de pouvoir revenir rapidement en arrière en cas de problème (autrement, vous devrez effectuer une restauration). Ensuite, indiquez à SQL Server le nouvel emplacement de chaque fichier à l'aide du code suivant :

ALTER DATABASE MyDatabaseName 
MODIFY FILE
   (NAME = N'LogicalFileName',
   FILENAME = N'pathname\filename');

Après avoir copié physiquement tous les fichiers et mis à jour leurs emplacements dans SQL Server, remettez la base de données en ligne avec le code suivant :

ALTER DATABASE MyDatabaseName SET ONLINE;

Fermeture du verrou de page

Q : J'éprouve des difficultés à comprendre certains des concepts liés au réglage des performances. J'ai lu à plusieurs reprises qu'il me fallait prévenir les problèmes de « verrou de page ». Je ne comprends pas à quoi font ici référence ces termes de « verrou » et de « page », ni même pourquoi un verrou de page pourrait être la source de problèmes. Pourriez-vous me donner une explication ?

R : Toutes les données d'une base de données SQL Server sont stockées dans des fichiers de données. En interne, ces fichiers sont organisés dans des séquences de segments de 8 Ko appelés pages. Une page est l'unité de base de stockage et d'E/S que peut gérer SQL Server. Les pages se trouvent généralement dans les fichiers de données sur disque et doivent être lues par le cache de SQL Server (appelé pool de mémoires tampons) avant tout traitement de requête.

SQL Server utilise différents genres de pages pour stocker différents genres de données relationnelles (telles que les lignes d'une table, les lignes d'un index non-cluster ou des données métier/texte). Il existe également des pages qui stockent des parties des structures de données internes nécessaires à SQL Server pour organiser et accéder aux pages qui stockent les données relationnelles.

Un verrou est un mécanisme interne léger utilisé par SQL Server pour synchroniser l'accès à une page dans le cache. Deux types de verrous de pages doivent être surveillés : les verrous de pages ordinaires et les verrous d'E/S de pages. Si un thread SQL Server doit attendre pour acquérir l'un de ces verrous, cela traduit un problème au niveau des performances.

Lorsque SQL Server attend qu'une partie d'un fichier de données soit lue à partir du disque, cela peut entraîner une attente de verrou d'E/S de page. Si un verrou d'E/S de page nécessite une durée excessive, cela traduit généralement un problème au niveau des performances du sous-système de disque sous-jacent (à savoir, une surcharge).

Lorsque plusieurs threads dans SQL Server tentent d'accéder à la même page de fichier de données de 8 Ko en mémoire et qu'un conflit d'accès à la page se produit, cela peut donner lieu à une attente de verrou de page. Le plus souvent, cela est dû à une utilisation élevée de petits objets temporaires dans la base de données tempdb.

Une explication plus approfondie de la façon d'analyser et d'atténuer les attentes de verrou de page n'entre pas dans le cadre de cet article, mais vous trouverez davantage d'informations aux emplacements suivants :

La section « SQL Server, objet de statistiques d'attente » de la documentation en ligne de SQL Server 2008 montre comment analyser les statistiques d'attente à l'aide du Moniteur système.
La section « sys.dm_os_wait_stats » de la documentation en ligne de SQL Server 2008 répertorie les types d'attente SQL Server courants et leur signification, et explique comment analyser les statistiques d'attente depuis SQL Server.
Le livre blanc intitulé « Résolution des problèmes de performances dans SQL Server 2008 » (éventuellement en anglais) présente différentes requêtes et techniques de dépannage, notamment des statistiques d'attente.

Examen des instantanés de bases de données

Q : Je viens de découvrir les instantanés de bases de données. J'envisage maintenant de les utiliser en guise d'alternative au modèle de récupération complète et aux sauvegardes de journaux. Je vais créer un instantané chaque heure environ ; de cette manière, en cas de problème, je pourrai récupérer les données endommagées. Les instantanés semblent constituer un moyen de restauration beaucoup plus rapide et plus simple. Selon vous, ce changement peut-il poser des problèmes ?

R : Oui. Les instantanés de bases de données ne constituent pas un substitut pratique ou viable d'une stratégie de récupération d'urgence complète. Un instantané ne procure pas les mêmes fonctionnalités qu'une sauvegarde des journaux de transactions en termes de récupération complète suite à un sinistre. L'instantané de base de données ne contient pas de copie de toutes les pages de la base de données, mais uniquement de celles qui ont changé depuis sa création initiale. Cela signifie qu'en cas d'endommagement de la base de données, l'instantané de base de données est inutile en l'absence de la base de données sous-jacente. Il s'agit simplement d'une collection de pages disparates issues de la base de données, qui ne peuvent pas être utilisées pour la récupération.

Un instantané de base de données vous permet de récupérer des données qui ont été supprimées accidentellement de la base de données, à condition que la base de données proprement dite soit encore disponible. Si par exemple une table supprimée dans la base de données existe encore dans l'instantané, vous pouvez l'utiliser pour recréer cette table supprimée.

Ceci dit, il est déconseillé de créer trop d'instantanés d'une base de données (comme substitut d'une sauvegarde des journaux de transactions effectuée toutes les demi-heures) en raison des problèmes de performances potentiels. Avant de pouvoir échanger une page de base de données (voir l'explication dans la réponse de la section « Fermeture du verrou de page »), vous devez d'abord copier de manière synchronisée la page dans tous les instantanés de base de données existants qui ne contiennent pas encore de version de cette page. Plus vous créez d'instantanés de bases de données, plus vous avez de copies de pages à effectuer et plus les performances se dégradent.

L'une des autres raisons pour lesquelles il est déconseillé de créer trop d'instantanés de bases de données est le fait que chacun contiendra des copies des pages de base de données antérieures au changement. Chacun d'eux augmentera en taille à mesure que la base de données changera. Cela peut entraîner des problèmes au niveau de l'espace disque, ainsi que des problèmes de performances.

Les instantanés de bases de données ne sont pas censés se substituer aux sauvegardes de journaux fréquentes. Vous trouverez une étude plus approfondie des implications en matière de performances des instantanés de bases de données dans le livre blanc intitulé « Considérations relatives aux performances des instantanés de bases de données sous des charges de travail d'E/S intensives » (éventuellement en anglais).

En outre, si vous utilisez le modèle de récupération complète et des sauvegardes des journaux de transactions, vous souhaitez sans aucun doute être en mesure de récupérer jusqu'au point de sinistre et/ou d'effectuer des restaurations jusqu'à une date et heure. (Pour une explication à ce sujet, voir mes articles de juillet 2009 et novembre 2009 intitulés respectivement « Présentation des sauvegardes SQL Server » et « SQL Server : Récupération d'urgence à l'aide de sauvegardes ».)

Miroir, miroir

Q : On m'a demandé de configurer un miroir de base de données pour notre base de données, mais je crains que la mise en miroir de base de données ne va pas aider à résoudre notre problème. Nous avons rencontré des problèmes d'endommagement au niveau de notre stockage SAN, et l'objectif est de faire en sorte que la mise en miroir de base de données nous protège contre tout endommagement. Mais tout endommagement ne sera-t-il pas envoyé automatiquement vers le miroir ? Comment la mise en miroir de base de données va-t-elle pouvoir nous aider ?

R : Cette question génère beaucoup de confusion. On pourrait penser que toute technologie qui procure une copie redondante d'une base de données serait susceptible à la propagation des endommagements de la base de données principale vers la base de données miroir (pour utiliser la terminologie propre à la mise en miroir de base de données), mais en réalité ce n'est pas le cas.

Le cœur de la question réside dans la façon dont la base de données miroir est maintenue. Tout endommagement serait probablement propagé vers le miroir si le mécanisme de synchronisation sous-jacent copiait des pages de base de données complètes de la base de données principale vers le miroir. Une page endommagée de la base de données principale serait alors placée dans le miroir.

Cependant, la mise en miroir de base de données évite spécifiquement ce problème car elle ne copie pas de pages de base de données d'une base de données à l'autre, mais copie les enregistrements des journaux de transactions de la base de données principale vers le miroir. Les enregistrements des journaux de transactions décrivent les modifications apportées aux pages de base de données mais ne contiennent pas les pages proprement dites. (Pour une explication complète des enregistrements des journaux de transactions, de la journalisation et de la récupération, voir mon article de février 2009 intitulé « Comprendre la journalisation et la récupération dans SQL Server ».)

Même si la base de données est endommagée par le sous-système d'E/S sous-jacent de la base de données principale, il est impossible que cet endommagement se propage automatiquement vers la base de données miroir. Dans le pire des cas, si SQL Server ne détecte pas un endommagement de page (car les totaux de contrôle de pagination ne sont pas activés), une valeur de colonne endommagée pourrait être utilisée pour calculer une valeur stockée dans la base de données. Le résultat incorrect serait alors propagé dans la base de données miroir : un effet d'endommagement de second ordre. Comme je l'ai mentionné, si les totaux de contrôle de pagination sont activés, cet endommagement ne sera pas détecté lors de la lecture de la page à partir du disque et l'endommagement de second ordre n'aura pas lieu.

Ce comportement explique également pourquoi l'exécution d'une vérification de cohérence sur la base de données principale ne donne aucune information concernant l'état de cohérence de la base de données miroir, et inversement. Il s'agit de deux bases de données distinctes dont la synchronisation est assurée en envoyant des descriptions des modifications physiques apportées à la base de données, et non les pages de base de données proprement dites.

Remarque du rédacteur en chef : Merci à Kimberly L. Tripp de SQLskills.com pour sa révision technique de l'article de ce mois-ci.

Paul S. Randal* est directeur général de SQLskills.com, directeur régional Microsoft et MVP SQL Server. Il a travaillé sur le moteur de stockage de données de SQL Server chez Microsoft de 1999 à 2007. Il a écrit DBCC CHECKDB/repair pour SQL Server 2005 et était responsable de Core Storage Engine pendant le développement de SQL Server 2008. Expert de la récupération d'urgence, de la haute disponibilité et de la maintenance de base de données, il anime fréquemment des conférences. Vous trouverez son blog à l'adresse SQLskills.com/blogs/paul et sa page Twitter à l'adresse Twitter.com/PaulRandal.*