Forum aux questions SQLPartitionnement, contrôles de cohérence et plus encore

Paul S. Randal

Q J'ai attaché accidentellement une base de données de production à un serveur SQL Server® 2005. Maintenant, je souhaite l'attacher au serveur correct, qui exécute SQL Server 2000. J'ai essayé de détacher simplement la base de données et de l'attacher au serveur SQL Server 2000, puis j'ai essayé de faire la même chose en effectuant une sauvegarde et une restauration, mais aucune de ces opérations n'a résolu le problème. Pourquoi cela ne fonctionne-t-il pas sur mon serveur SQL Server 2000 ? Celle-ci est la seule copie de la base de données dont je dispose.

R La première chose à noter ici est l'importance d'avoir des copies de secours. Les administrateurs de bases de données pensent habituellement n'avoir besoin de copies de sauvegarde que pour la restauration suite à une corruption ou un incident de grande ampleur. Mais le problème que vous avez rencontré est beaucoup moins évident : un problème est survenu lors d'une mise à jour, mais dans ce cas-là, la mise à jour en question à été effectuée par erreur. Il n'est reste pas moins que vous devriez toujours disposer d'une copie de sauvegarde récente des bases de données entières au cas où il arrive quelque chose à votre base de données.

Une mise à jour, qu'elle soit intentionnelle ou accidentelle, est une opération à sens unique. Il est donc très difficile d'en inverser les effets. Lorsque la mise à jour concerne les versions d'un serveur SQL, une série d'étapes de mise à jour est exécutée sur la base de données. Chaque étape implique d'ordinaire certaines modifications physiques à la base de données et chaque étape augmente le numéro de version de la base de données.

Par exemple, une des modifications majeures exécutées lors de la mise à jour d'un serveur SQL Server 2000 vers un serveur SQL Server 2005 concerne la structure des catalogue de système de la base de données (souvent appelés métadonnées de base de données ou tables de système). Ce catalogue contient différentes métadonnées sur les tables, les index, les colonnes, les allocations et autres détails relatifs à la structure relationnelle et physique de la base de données.

Au fur et à mesure que ces mises à jour sont effectuées, le numéro de version des bases de données augmente. Par exemple, les bases de données SQL Server 7.0 ont la version numéro 515, les bases de données SQL Server 2000 la version numéro 539 et les bases de données SQL Server 2005 la version numéro 611 (ou 612 si la fonction Vardecimal est activée). Ceci permet à SQL Server de savoir quelle est la dernière étape de mise à jour effectuée sur la base de données.

Certaines versions de SQL Server ne peuvent pas lire les bases de données mises à jour aux versions plus récentes de SQL Server (par exemple, SQL Server 2000 ne peut pas lire une base de données mise à jour à la version SQL Server 2005). Ceci est dû au fait que les anciennes versions n'ont pas le code requis pour interpréter les structures et la disposition de base de données mises à jour. Le problème de votre base de données, mise à jour vers la version SQL Server 2005 : il devient impossible de l'attacher au serveur SQL Server 2000.

En l'absence d'une copie de secours complète de la base de données, votre seule option est d'exporter toutes les données de la base de données mise à jour et de les retransférer vers une nouvelle base de données SQL Server 2000. Du moment que vous n'avez pas utilisé les nouvelles fonctions de SQL Server 2005, vous pouvez scripter la structure de la base de donnée, créer la base de données sur le serveur SQL Server 2000, puis exporter/importer les données.

Pour scripter la base de données à partir du serveur SQL Server 2005, vous pouvez utiliser l'Explorateur d'objets dans SQL Server Management Studio (cliquez avec le bouton droit sur la base de données, sélectionnez Tâches puis l'option Générer des scripts). L'assistant est explicite et génère un script permettant de créer tous les objets, les index, les contraintes, les déclencheurs, et ainsi de suite.

Q Récemment, nous avons reconçu notre schéma afin de pouvoir utiliser la partition de tables sur la table principale car on nous a dit que cela optimise les performances. La base de données est enregistrée sur un seul lecteur de 120 Go et la table se trouve dans un seul groupe de fichiers. Il n'y a pas de fenêtres défilantes, seulement de nouvelles partitions ajoutées toutes les semaines. Toutes les données doivent être disponibles en ligne. La plupart de nos requêtes traitent les données d'une seule semaine et il n'y en a que quelques unes qui concernent l'année précédente. Ceci semble plus facile à faire dans un groupe de fichiers unique. Est-ce correct ou y a-t-il autre chose à savoir ?

R Bien qu'il semble plus facile d'utiliser un groupe de fichiers unique, il est improbable qu'un tel partitionnement vous apporte des bénéfices. Le partitionnement se justifie essentiellement lorsque l'on souhaite accroître l'efficacité de la maintenance des bases de données et améliorer la disponibilité des données en cas d'incident majeur. De plus, le partitionnement vous permet de créer un schéma qui offre des gains de performance.

L'exemple type de partitionnement est une table des ventes avec 12 partitions, chacune représentant un mois de données de vente de l'année précédente. À la fin du mois, la partition la plus ancienne est désactivée (et archivée ou supprimée) et une nouvelle partition activée. Ce sont les fenêtres défilantes que vous mentionniez. La partition du mois en cours est en lecture/écriture et les mois précédents sont en lecture seule. Chaque partition est enregistrée dans un groupe de fichiers séparé. Ce schéma offre tous les avantages du partitionnement, mais ne s'adapte pas forcément à toutes les circonstances.

Ma femme, Kimberly, a proposé une variante du schéma ci-dessus permettant une indexation plus efficace. Imaginez que l'on fractionne la table de vente en deux tables : l'une contenant la partition en lecture/écrite, l'autre les 11 partitions en lecture seule, avec une vue partitionnée des deux tables.

Dans ce cas, la table en lecture/écriture présente un nombre réduit d'index et la table en lecture seule un plus grand nombre d'index, ce qui permet de prendre en charge des requêtes de rapport. Ceci accroît remarquablement l'efficacité du langage de manipulation des données (DML) sur les données en lecture/écriture. En effet, il ne doit plus gérer autant d'index non mis en cluster.

De plus, les requêtes sur les données en lecture/écriture n'ont pas à traiter les données en lecture seule. La suppression des partitions dans les plans de requête de SQL Server 2005 n'est pas parfaite, surtout en présence de prédicats complexes, mais cet aspect a été remarquablement amélioré dans SQL Server 2008. Pour plus d'informations à ce sujet, voir sqlskills.com/blogs/kimberly/default,month,2007-10.aspx#a71c70243-3d57-4940-9af7-a802b73f2f93.

Pour mieux vous illustrer mes propos, j'expliquerai quelques unes des fonctions activées par le partitionnement sur des groupes de fichiers multiples.

Disponibilité partielle de bases de données Il s'agit de l'aptitude d'une base de données à être en ligne et accessible en cas de restauration suite à un incident majeur pourvu que le groupe de fichiers primaire soit en ligne. Si vous avez un seul groupe de fichiers, pendant la restauration, la base de données entière est hors service. Si les données sont distribuées sur des groupes de fichiers multiples, seuls les groupes de fichiers endommagés seront hors ligne pendant la restauration. Dans ce cas, votre application pourra peut-être continuer à fonctionner.

Restauration fragmentaire Ce schéma est semblable à la disponibilité partielle de bases de données. Avec un seul groupe de fichiers, l'unité de restauration est constituée d'une page unique ou de la base de données entière. Avec des groupes de fichiers multiples, vous pouvez restaurer un groupe de fichiers unique. Les bases de données seront alors partiellement disponibles.

Maintenance des bases de données partitionnées Les deux schémas de partitionnement décrits ci-dessus vous permettent d'effectuer une suppression de fragmentation d'index par partition, même si toutes les partitions se trouvent dans un groupe de fichiers unique. Mais avec un groupe de fichiers unique, vous ne pourrez plus effectuer les contrôles de cohérence par groupe de fichiers, ce qui peut réduire considérablement la quantité de données que le contrôle de cohérence des bases de données (DBCC) doit traiter (et, donc, la quantité de ressources de processeur et E/S utilisées).

Pour simplifier, alors que vous pouvez avoir plusieurs partitions dans le même groupe de fichiers, avoir un mappage 1-1 entre partitions et groupes de fichiers présente de nombreux avantages.

Q Récemment, l'un de nos serveurs de bases de données haut de gammes nous a fait une frayeur : une carte de mémoire défectueuse provoquait des altérations. Nous avons découvert ces altérations lorsque des données aléatoires ont commencé à s'afficher dans notre application. Nous avons exécuté DBCC CHECKDB et identifié toutes sortes d'altérations. Malheureusement, ces altérations se trouvaient même dans nos copies de secours. Nous avons, donc, dû éliminer manuellement les données altérées.

Pour résumer, nous avons dû remplacer le matériel défectueux et activer le contrôle de pagination. Nous souhaiterions exécuter des contrôles de cohérence réguliers, mais nous disposons d'une fenêtre de maintenance réduite et il faut longtemps pour contrôler notre base de données 2.4To. Que pouvons-nous faire ?

R La question « Comment effectuer des contrôles de cohérence et autres opérations de maintenance sur une base de données très volumineuse (VLDB) ? » se présente de plus en plus souvent. De nombreux administrateurs de données abandonnent lorsqu'ils découvrent que le DBCC CHECKDB prend plus de temps que leur fenêtre de maintenance ne le permet. (Dans certains cas, la base de données est utilisée 24h/24, 7j/7et il est impossible de trouver une plage horaire appropriée et suffisante pendant laquelle les ressources du processeur et des E/S pourraient être dédiées à l'exécution du DBCC CHECKDB).

Sans laisser tomber et abandonner l'exécution des vérifications de cohérence (ce que je déconseille fortement), il existe quatre méthodes qui pourraient vous permettre d'exécuter des vérifications de cohérence. J'ai personnellement aidé certains utilisateurs à utiliser ces quatre méthodes.

Utiliser l'option WITH PHYSICAL_ONLY du DBCC CHECKDB Un DBCC CHECKDB habituel exécute plusieurs vérifications de cohérence logiques et occupe considérablement le processeur (puisqu'il s'agit essentiellement l'une opération dépendante du processeur). L'utilisation de l'option WITH PHYSICAL_ONLY se limite à exécuter les contrôles de cohérence de bitmap d'allocation très rapides, puis à lire et auditer chacune des pages allouées dans la base de données, en forçant le test des sommes de contrôle de page présentes sur les pages à tester. Le DBCC CHECKDB devient alors une opération dépendante des E/S, ce qui réduit considérablement son temps d'exécution (en fait, la vitesse d'exécution peut être doublée par rapport à un DBCC CHECKDB complet, ce qui correspond à un gain de temps remarquable).

Diviser la charge de travail du contrôle de cohérence Ceci signifie subdiviser les tables de la base de données en groupes de dimensions semblables (l'approche la plus facile consiste à subdiviser en fonction des numéros de page) et utiliser les commandes de DBCC CHECKTABLE pour exécuter un contrôle de cohérence de toutes les tables dans un groupe unique tous les soirs. Ainsi, avec, par exemple, sept groupes, si l'on contrôle un groupe par nuit et que l'on exécute un DBCC CHECKALLOC et DBCC CHECKCATALOG une fois par semaine, il est possible d'exécuter l'équivalent d'un DBCC CHECKDB sur une semaine.

Utiliser le partitionnement de table avec des groupes de fichiers multiples Il est possible de subdiviser les tables les plus grandes des bases de données très volumineuses en plusieurs groupes de fichiers. Un schéma de vérification de cohérence possible est l'exécution quotidienne d'un DBCC CHECKFILEGROUP sur le groupe de fichiers contenant la partition en lecture/écriture et d'un DBCC CHECKFILEGROUPs par semaine sur les groupes de fichiers contenant les partitions en lecture seule. La logique est que les données en lecture seule sont complètement sauvegardées et qu'elles ne sont pas utilisées dans le traitement de tous les jours. Par conséquent, il n'est pas nécessaire d'exécuter de vérifications de cohérence fréquentes, l'altération de ces données n'étant pas critique.

Décharger les vérifications de cohérence sur un autre serveur Cette option implique la restauration des copies de secours régulières et complètes des banques de données sur un autre serveur et l'exécution du DBCC CHECKDB sur ce serveur. Évidemment, ceci décharge complètement la charge de travail de la vérification de cohérence du serveur de production. Cependant, l'inconvénient est que, si une altération est identifiée, il faudra exécuter une vérification de cohérence sur le serveur de production. Mais ceci ne devrait arriver que très rarement.

Comme vous pouvez le voir, les administrateurs de bases de données disposent de nombreuses possibilités pour exécuter les vérifications de cohérences sur une base de données très volumineuse tout en réduisant la charge supplémentaire nécessaire à l'exécution d'un DBCC CHECKDB complet. Comme je l'ai déjà dit, j'ai moi-même aidé des utilisateurs à utiliser ces quatre méthodes. Je suis convaincu que ces options résoudront vos problèmes aussi.

Conseil : Utiliser des déclencheurs pour implémenter une logique côté serveur

Dans certains cas, vous devez implémenter une logique côté serveur avec des déclencheurs. Mais il y a des pièges dont vous devez être conscient. Voici quelques unes des choses que vous ne devez pas oublier.

• Les déclencheurs sont activés par instruction, pas par ligne. Ceci dit, veillez à placer la logique additionnelle à l'intérieur de votre logique de déclenchement de sorte à pouvoir gérer aussi bien les cas où plusieurs lignes sont affectées par l'instruction que celui où aucune ligne n'est affectée (les déclencheurs sont activés par instruction même si aucune ligne n'est affectée). Les données affectées sont contenues dans des tables virtuelles pour les instructions de langage de manipulation des données. Ces tables peuvent être jointes, ce qui vous permet de travailler avec les données.
• Les déclencheurs s'exécutent de manière synchrone dans votre transaction. Souvenez-vous de ceci à chaque fois que vous souhaitez appeler une application externe ou accéder à une ressource externe, et que le temps de réaction risque d'être trop long. Par exemple, si vous activez une instruction de mise à jour pour une table et qu'un déclencheur est activé pendant cette action, la transaction (le caractère implicite de l'instruction de mise à jour) ne se terminera pas tant que la logique du déclencheur ne sera pas terminée. Si l'application ou le processus externe renvoie un code d'erreur, le serveur SQL Server risque d'annuler la transaction et de restaurer votre transaction (en fonction de la gestion de l'erreur implémentée et du code d'erreur). Donc, si vous devez exécuter une opération externe au sein du déclencheur et que celle-ci n'est pas critique pour la transaction (ou ne doit pas être exécutée dans la même étendue), vous devriez l'attribuer à un autre processus et récupérer les données de manière asynchrone. SQL Server 2005 a introduit le SQL Server Service Broker, qui permet d'effectuer ce type d'opérations de manière asynchrone.
• Toute erreur provoquée par une instruction au sein d'un déclencheur est très difficile à identifier. Si plusieurs tables sont impliquées dans la transaction, n'oubliez pas d'inspecter les déclencheurs en cas d'erreur et d'implémenter une gestion appropriée des erreurs. Si vous modifiez le schéma dans votre base de données, veillez également à suivre la logique de déclencheur. Dans le cas contraire, un petit déclencheur risque d'affecter de manière considérable la stabilité et les performances générales. Vous pouvez vérifier la plupart des implications liées aux modifications de schéma à l'aide de Visual Studio® pour les professionnels des bases de données. Pour cela, exécutez des contrôles de schéma automatiques lors de l'édition du projet ainsi que des analyses de code statiques pour identifier des incohérences de type de données.

—Jens K. Suessmeyer, consultant en base de données chez Microsoft

Paul S. Randal est directeur général de SQLskills.com et MVP SQL Server. Il a travaillé sur le moteur de stockage de données de SQL Server chez Microsoft de 1999 à 2007. Paul a écrit DBCC CHECKDB/repair for SQL Server 2005 et était responsable du Core Storage Engine pendant le développement de SQL Server 2008. Expert de la récupération après incident, de la haute disponibilité et de la maintenance de base de données, Paul présente fréquemment des conférences. Vous trouverez son blog à l'adresse SQLskills.com/blogs/paul.

© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. La reproduction partielle ou totale sans autorisation est interdite.