En regardant une session de Paul Randal sur les corruptions à la teched de Barcelone en 2008* , j’ai réalisé qu’il manquait une étape dans notre listing de choses à faire à la suite d’une migration de 2000 vers 2005 ou 2008.
Par défaut en version 2000, PAGE_VERIFY n’a qu’une seule valeur possible pour vérifier la consistance des pages sur disque, TORN_PAGE_DETECTION. TPD propose un mécanisme de vérification lorsque l’écriture d’une page ne peut pas se faire en entier, en raison d’une coupure d’alimentation par exemple ou tout autre problème entre SQL Server et le disque. Mais le scope de détection était limité car seuls les 2 premiers bits de chacun des 16 secteurs de 512 octets d’une page étaient scrutés pour calculer une séquence de contrôle (m_tornBits dans un dbcc page). Un écrasement de bits au milieu d’un secteur ne pouvait donc pas être détecté de cette manière.
A partir de la version 2005, une nouvelle valeur CHECKSUM est devenue option par défaut. Elle va calculer une somme de contrôle de l’intégralité de chaque page pour pouvoir détecter lorsque le contenu d’une page a été modifié même de manière infime. L’algorithme a été revu et implémenté de telle sorte qu’il n’impacte pas les performances (cf post Linchi Shea). Il est évidemment recommandé de laisser cette option PAGE_VERIFY sur CHECKSUM.
Le problème, c’est que PAGE_VERIFY, tout comme le mode de compatibilité de la base, n’est pas modifié lorsque la base migre de 2000 vers 2005 ou 2008:
restore database pagechecksumtest from disk='F:\SQLDATA$SQL2000\BACKUP\pagechecksumtest.bak'
with move 'pagechecksumtest' to 'E:\SQLDATA$SQL2005\DATA\pagechecksumtest.mdf',
move 'pagechecksumtest_log' to 'F:\SQLDATA$SQL2005\LOG\pagechecksumtest_log.ldf',
stats16 percent processed. 24 percent processed. 33 percent processed. 41 percent processed. 58 percent processed. 66 percent processed. 74 percent processed. 83 percent processed. 91 percent processed. 100 percent processed. Processed 96 pages for database 'pagechecksumtest', file 'pagechecksumtest' on file 1. Processed 1 pages for database 'pagechecksumtest', file 'pagechecksumtest_log' on file 1. Converting database 'pagechecksumtest' from version 539 to the current version 611. Database 'pagechecksumtest' running the upgrade step from version 539 to version 551. (...) Database 'pagechecksumtest' running the upgrade step from version 610 to version 611. RESTORE DATABASE successfully processed 97 pages in 0.047 seconds (16.754 MB/sec).
select name, page_verify_option_desc from sys.databases where name = 'pagechecksumtest'name page_verify_option_desc ------------------------- ----------------------------------- pagechecksumtest TORN_PAGE_DETECTION
Donc il faut bien penser à l’activer à la suite de la migration pour assurer un contrôle optimal:
alter database pagechecksumtest set PAGE_VERIFY CHECKSUMAttention, les pages ne sont pas protégées pour autant immédiatement après: il faudra qu’elles suivent un cycle complet : qu’elles soient luent depuis le disque vers le buffer pool, modifiées, puis réécrites par le checkpoint pour embarquer la somme de contrôle dans l’entête.
Si on veut creuser un peu plus, on peut se demander si la page sera bien protégée si elle se trouve dans un filegroup en readonly. En effet, la page ne pouvant pas être modifiée, le checkpoint ne pourra pas marquer le checksum dans son entête (on peut le voir avec un dbcc page, en regardant la valeur de m_flagBits qui normalement doit embarquer 0x200 dans son bitmap pour montrer que le checksum a été calculé).
Scénario: je pars sur une base fraîchement migrée, je choisis d’ajouter un filegroup et de créer des données dedans, pour enfin le passer en read-only. Et ensuite, je me rends compte que PAGE_VERIFY est toujours sur TORN_PAGE_DETECTION, donc je le passe sur CHECKSUM. Est-ce que l’écrasement de données par un filter driver ou tout autre logiciel tiers s’exécutant sous NTFS sera vue par SQL Server ?
Voici la repro pour vous permettre de vous faire votre propre idée:
1) Sur une instance en SQL Server 2000, créer une base, une table et sauvegarder la base:
create database tpdtest
use tpdtest
create table T1(a numeric identity, b char(1))
insert into T1 values ('A')
backup database tpdtest to disk='F:\SQLDATA$SQL2000\BACKUP\tpdtest.bak' with init, stats2) Remonter la base sur une instance 2005 ou 2008, ajouter un filegroup, déplacer la table dans ce filegroup et le passer en READONLY:
restore database tpdtest from disk='F:\SQLDATA$SQL2000\BACKUP\tpdtest.bak' with move 'tpdtest' to 'E:\SQLDATA$SQL2005\DATA\tpdtest.mdf', move 'tpdtest_log' to 'F:\SQLDATA$SQL2005\LOG\tpdtest_log.ldf', stats, replace alter database tpdtest add filegroup RO_tpdtest alter database tpdtest add file(name='RO_tpdtest', filename='E:\SQLDATA$SQL2005\DATA\RO_tpdtest.ndf') to filegroup RO_tpdtest use tpdtest create unique clustered index UCQ_T1_a on T1(a) on RO_tpdtest alter database tpdtest modify filegroup RO_tpdtest readonly
Oulala mais j’ai oublié de passer PAGE_VERIFY sur CHECKSUM, vite:
alter database tpdtest set PAGE_VERIFY CHECKSUMEt là je simule une corruption de données avec un éditeur héxa dans la page de données. Un coup de dbcc ind pour identifier la page en question:
dbcc ind('tpdtest','T1',1)PageFID PagePID IAMFID IAMPID ObjectID PageType --------- --------- ---------- ---------- ------------ ----------- 3 9 NULL NULL 1977058079 10 3 8 3 9 1977058079 1
La page en question est la page 8 dans le fichier 3, donc en convertissant 8 en offset, on obtient (8*8192) = 0x10000. A partir de là, je passe la base offline et avec un éditeur hexa de mon choix, je m’en vais écraser la valeur ‘A’ avec une autre valeur, ‘C’ par exemple:
A ce niveau-là, il est important de ne pas toucher aux premiers octets de l’entête, car dans ce cas le mécanisme de TPD jouerait son rôle et on verrait une erreur 824 pointer son nez. On veut vraiment simuler une corruption au beau milieu de la page. On remet la base en ligne et on va lire la valeur:
alter database tpdtest set online
use tpdtest
select * from T1a b -- -- 1 C
Oooops. ..
A titre de comparaison, en créant le même cas dans un filegroup en read-write:
use tpdtest
create table T2(a numeric identity, b char(1))
create unique clustered index UCQ_T2_a on T2(a)
insert into T2 values ('A')
dbcc ind('tpdtest','T2',1)PageFID PagePID IAMFID IAMPID ObjectID PageType --------- --------- ---------- ---------- ------------ ----------- 1 25 NULL NULL 2089058478 10 1 15 1 25 2089058478 1
La page est la 15, donc en ouvrant le fichier MDF, à l’offset (15*8192) = 0x1E000, on refait la même vilaine corruption au même endroit:
Mais cette fois lorsque l’on veut relire la page:
alter database tpdtest set online
use tpdtest
select * from T2Msg 824, Level 24, State 2, Line 1SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xb3834c64; actual: 0xb2834c64). It occurred during a read of page (1:15) in database ID 21 at offset 0x0000000001e000 in file ‘E:\SQLDATA$SQL2005\DATA\tpdtest.mdf’.
Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for more information, see SQL Server Books Online.
Le CHECKSUM est un superset de TPD, donc ça marche.
Donc résumé:
– Quand on migre, il faut penser aussi à modifier PAGE_VERIFY et le passer sur CHECKSUM car ce n’est pas automatique.
– Le checksum en question ne sera implémenté qu’une fois la page lue depuis le disque, modifiée, puis réécrite par le CHECKPOINT.
– Les données dans les filegroups en read_only ne sont pas pour autant protégés. Ils conservent le TPD comme seul mode de protection.
A+ David B.
Continuez votre lecture sur le blog :
- Mythe: TORN PAGE DETECTION est moins coûteux que CHECKSUM (David Baffaleuf) [SQL Server]
- Principes d’une sauvegarde à chaud (David Baffaleuf) [SQL ServerVintage]
- Alter table rebuild (Benjamin VESAN) [SQL Server]
- Modes de récupération et journal de transactions, épisode 1 (David Baffaleuf) [SQL ServerVintage]
- Fragmentation sur des tables stockées en S-GAM (David Baffaleuf) [SQL Server]
Pour info, l’article a été publié sur SQL Server Central la semaine dernière. Il est disponible à ce lien: http://www.sqlservercentral.com/articles/PAGE_VERFIFY/70541/
Merci à Steve Jones et aux relecteurs de SSC !
David B.