Vérification bases de données

Version imprimable

03/04/2017, 18h43
Francky8

Vérification bases de données

Bonjour,

J'ai l'option Page Verify à CHECKSUM sur mes bases.
J'ai l'option CHECKSUM sur le backup de mes bases.
J'ai un job qui surveille la table suspect_pages (msdb) et qui envoie un email en cas d'alerte.
J'aimerais m'assurer de l'intégrité de mes bases.
Est-ce que l'option CHECKSUM que j'utilise au niveau base et backup est suffisante pour m'assurer d'avoir des bases en "bonne santé" ?
Ou est-ce qu'il faut impérativement mettre en place un job quotidien qui vérifie les bases (je pense notamment à dbcc check) ?
Sachant que cette vérification de base (dbcc checkdb) aura des conséquences au niveau charge en raison de la taille de certaines bases.

Par avance, merci pour votre aide.

Franck
04/04/2017, 03h21
elsuket

Bonjour,

Citation:

Est-ce que l'option CHECKSUM que j'utilise au niveau base et backup est suffisante pour m'assurer d'avoir des bases en "bonne santé" ?

Malheureusement non :)

L'option de base de données PAGE_VERIFY à CHECKSUM est une bonne chose, mais elle ne recalcule la somme de contrôle que lorsque la page est chargée en RAM à partir des disques, modifiée en RAM, puis écrite sur disque après ce changement.

L'option CHECKSUM de l'instruction BACKUP est aussi une bonne chose, puisqu'alors toutes les pages allouées sont vérifiées par le calcul de cette somme de contrôle lors d'un backup complet de la base. Cette vérification est aussi réalisée lors des sauvegardes différentielles, mais seulement pour les pages qui ont été modifiées depuis la dernière sauvegarde complète.

Ces mécanismes permettent de détecter au plus tôt des pages corrompues, en augmentant la fréquence de calcul de la somme de contrôle. Mais aucun mécanisme ne garantit qu'aucune page n'est corrompue. En effet, même lorsqu'on exécute DBCC CHECKDB, il est tout à fait possible qu'une page soit corrompue juste après l'exécution de cette commande sans détection d'erreur.

Une corruption plus insidieuse pourrait être, par exemple, l'écriture d'une page en RAM avec son checksum, corrompue par une barrette de RAM défectueuse : dans ce cas on a une somme de contrôle correcte, mais une page incorrecte. Le seul moyen de le détecter est d'utiliser DBCC CHECKDB.

Identiquement, on peut mettre en place une session d'événements étendus ou des alertes de l'Agent SQL Server sur génération des erreurs 823 à 825 : c'est un bon mécanisme, mais encore une fois on n'a pas de garantie que d'autres pages ne sont pas corrompues. Ceci est simplement du au fait qu'une base de données vit, que des pages sont échangées entre les disques et la RAM, ...

Citation:

Ou est-ce qu'il faut impérativement mettre en place un job quotidien qui vérifie les bases (je pense notamment à dbcc check) ?

Vous l'aurez compris, c'est un passage obligé.

Citation:

Sachant que cette vérification de base (dbcc checkdb) aura des conséquences au niveau charge en raison de la taille de certaines bases.

Pour les plus grosses bases, vous pouvez découper le travail que fait DBCC CHECKDB par les instructions que celle-ci exécute implicitement : DBCC CHECKCATALOG, DBCC CHECKTABLE, DBCC CHECKALLOC. Vous pouvez aussi, si vous avez plusieurs groupes de fichiers, vous en remettre à DBCC CHECKFILEGROUP. Il suffit alors de créer de groupes de tables suivant leur taille, de façon à équilibrer le volume de données à vérifier sur les jours de la semaine par exemple, en exécutant les vérifications sur les tables les plus volumineuses dans les périodes creuses ou plus longues (4h de nuit en semaine, 8h le weekend par exemple).

Notez aussi que depuis le SP2 de SQL Server 2014, les instructions DBCC CHECKDB et DBCC CHECKFILEGROUP sont parallélisables (voir leur option MAXDOP).

@++ ;)
04/04/2017, 09h14
Francky8

Bonjour elsuket,
Merci beaucoup pour cette réponse claire et précise.
Je vais donc adapter mes vérifications en fonction de tes conseils.

Encore merci :D

Franck
04/04/2017, 09h30
Francky8

Re-bonjour elsuket,

Je me pose une question concernant votre phrase :
"L'option de base de données PAGE_VERIFY à CHECKSUM est une bonne chose, mais elle ne recalcule la somme de contrôle que lorsque la page est chargée en RAM à partir des disques, modifiée en RAM, puis écrite sur disque après ce changement."
Sauf si j'ai mal compris mais vous semblez dire que ce contrôle n'est réalisé que lors des écritures. C'est bien cela ?

Pour ma part, j'avais compris que ce CHECKSUM est calculé et écrit lors d'une opération d'écriture. Ensuite, à chaque lecture, un recalcul (CHECSUM) est réalisé afin de vérifier s'il correspond au CHECSUM réalisé lors de l'opération d'écriture. Ce qui aurait pour conséquence d'avoir un contrôle des pages à chaque opération de lecture. Vous confirmez ou pas ?

En tout cas, merci pour vos précieuses explications ...

Franck
04/04/2017, 11h02
SQLpro

Ne confondez pas écriture logique => en RAM et écriture physique => disque.

A +
04/04/2017, 12h05
Francky8

Bonjour Frédéric,

Je voulais être certain d'avoir bien compris le fonctionnement de CHECKSUM.
Voilà comment je vois le fonctionnement :
une vérification est faite lors de toute lecture c'est-à-dire
la page est chargée en mémoire,
un recalcul du CHECKSUM est fait
et la valeur trouvée est comparée avec la valeur stockée dans l'entête de la page.
C'est bien cela ?

Merci pour vos éclaircissements

Franck
05/04/2017, 11h51
SQLpro

C'est bien cela...

"
option de base de données PAGE_VERIFY sur CHECKSUM : SQL Server calcule une somme de contrôle portant sur le contenu de la page toute entière et stocke la valeur obtenue dans l'en-tête de la page dès que cette dernière est écrite sur le disque. Lorsque la page est lue à partir du disque, la somme de contrôle est recalculée, puis comparée à la valeur de la somme de contrôle stockée dans l'en-tête de page
"

Cela n’empêchera pas des corruption de disques survenir pour les pages non lues depuis un certain temps... Et si cette page n'a pas été lue depuis très longtemps, il sera difficile de la récupérer dans les sauvegardes, si ces dernières sont régulièrement écrasées. C'est pourquoi il convient de faire une vérification du stockage (DBCC CHECK...) à la même fréquence que la sauvegarde complète...

A +
05/04/2017, 12h13
Francky8

Bonjour Frédéric,

Merci pour votre réponse claire et précise.
Suite à tous vos conseils, en plus du CHECKSUM, j'ai ajouté un job réalisant un DBCC CHECKDB du lundi au vendredi sur les bases.
Sauf pour une base très volumineuse et très critique. La vérification risque de "perturber" l'application utilisée en continu.
Pour cette base, j'ai opté pour un backup/restore sur une autre instance SQL Server afin de faire (sans gêner personne) un DBCC CHECKDB (with tablock) sur la base restaurée.
Cette base est en SQL Server 2008 R2. Le backup correspondant à une copie bit-by-bit, le restore de la base sera vraiment une image identique et le dbcc checkdb sera donc légitime.
Je n'ai pas encore réalisé ce job mais c'est à venir dans les heures qui viennent. ;)

Merci à vous tous ... c'est toujours un plaisir de poser une question sur ce forum

Franck
05/04/2017, 12h21
SQLpro

Vous pouvez aussi faire un DBCC CHECK... sur un replicas AlwayOn, ceci présente l'avantage de cumuler une réparation automatique des pages en sus de la verif...

A lire : https://www.brentozar.com/archive/20...bility-groups/

A +
05/04/2017, 16h29
Francky8

Frédéric,
Merci pour l'information. Je la note pour "mes" environnements en SQL Server 2012 ou plus. L'instance dont je parlais est en SQL Server 2008 R2 ... donc non concernée par Always On.

Merci pour tout.

Franck
06/04/2017, 11h23
elsuket

Citation:

Cette base est en SQL Server 2008 R2. Le backup correspondant à une copie bit-by-bit, le restore de la base sera vraiment une image identique et le dbcc checkdb sera donc légitime.

Cela vous permet en plus de vérifier votre séquence de restauration, donc avec la vérification d'intégrité, vous êtes au top.
Si par hasard la base de données à été portée vers SQL Server 2008 depuis SQL Server 2000, sachez que par défaut l'option DATA_PURITY n'est pas implicitement exécutée par DBCC CHECKDB. Pour le savoir, il faut lire la page de boot de la base de données à l'aide de l'instruction DBCC DBINFO WITH TABLERESULTS.
Dans le résultat de cette instruction, dans la colonne Field, recherchez la ligne pour laquelle elle est à dbi_createVersion.
Si la valeur de la colonne Value pour cette lignes vaut moins que 611, alors la base a été créée avant SQL Server 2005.
Dans ce cas, il faut alors regarder la ligne pour laquelle la colonne Field vaut dbi_dbccFlags : si elle est à zéro, alors cette vérification n'est pas réalisée.
Il faut exécuter DBCC CHECKDB WITH DATA_PURITY pour l'activer. A ce moment là, la colonne Value pour la ligne dbi_dbccFlags vaut 2.

@++ ;)