{"id":890,"date":"2010-04-06T14:35:11","date_gmt":"2010-04-06T13:35:11","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=890"},"modified":"2022-12-01T17:56:17","modified_gmt":"2022-12-01T16:56:17","slug":"modifier-page_verify-apres-une-migration-depuis-sql-2000","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/modifier-page_verify-apres-une-migration-depuis-sql-2000\/","title":{"rendered":"Modifier PAGE_VERIFY apr\u00e8s une migration depuis SQL 2000"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

En regardant une session <\/a>de Paul Randal sur les corruptions \u00e0 la teched de Barcelone en 2008* , j’ai r\u00e9alis\u00e9 qu’il manquait une \u00e9tape dans notre listing de choses \u00e0 faire \u00e0 la suite d’une migration de 2000 vers 2005 ou 2008.<\/p>\n

Par d\u00e9faut en version 2000, PAGE_VERIFY n’a qu’une seule valeur possible pour v\u00e9rifier la consistance des pages sur disque, TORN_PAGE_DETECTION.\u00a0 TPD propose un m\u00e9canisme de v\u00e9rification lorsque l’\u00e9criture d’une page ne peut pas se faire en entier, en raison d’une coupure d’alimentation par exemple ou tout autre probl\u00e8me entre SQL Server et le disque. Mais le scope de d\u00e9tection \u00e9tait limit\u00e9 car seuls les 2 premiers bits de chacun des 16 secteurs de 512 octets d’une page \u00e9taient scrut\u00e9s pour calculer une s\u00e9quence de contr\u00f4le (m_tornBits<\/em> dans un dbcc page). Un \u00e9crasement de bits au milieu d’un secteur ne pouvait donc pas \u00eatre d\u00e9tect\u00e9 de cette mani\u00e8re.<\/p>\n

A partir de la version 2005, une nouvelle valeur CHECKSUM est devenue option par d\u00e9faut. Elle va calculer une somme de contr\u00f4le de l’int\u00e9gralit\u00e9 de chaque page pour pouvoir d\u00e9tecter lorsque le contenu d’une page a \u00e9t\u00e9 modifi\u00e9 m\u00eame de mani\u00e8re infime. L’algorithme a \u00e9t\u00e9 revu et impl\u00e9ment\u00e9 de telle sorte qu’il n’impacte pas les performances (cf post<\/a> Linchi Shea). Il est \u00e9videmment recommand\u00e9 de laisser cette option PAGE_VERIFY sur CHECKSUM.<\/p>\n

Le probl\u00e8me, c’est que PAGE_VERIFY, tout comme le mode de compatibilit\u00e9 de la base, n’est pas modifi\u00e9 lorsque la base migre de 2000 vers 2005 ou 2008:<\/p>\n

restore database pagechecksumtest from disk='F:\\SQLDATA$SQL2000\\BACKUP\\pagechecksumtest.bak'\r\nwith move 'pagechecksumtest' to 'E:\\SQLDATA$SQL2005\\DATA\\pagechecksumtest.mdf',\r\nmove 'pagechecksumtest_log' to 'F:\\SQLDATA$SQL2005\\LOG\\pagechecksumtest_log.ldf',\r\nstats<\/span><\/pre>\n
16 percent processed.\r\n24 percent processed.\r\n33 percent processed.\r\n41 percent processed.\r\n58 percent processed.\r\n66 percent processed.\r\n74 percent processed.\r\n83 percent processed.\r\n91 percent processed.\r\n100 percent processed.\r\nProcessed 96 pages for database 'pagechecksumtest', file 'pagechecksumtest' on file 1.\r\nProcessed 1 pages for database 'pagechecksumtest', file 'pagechecksumtest_log' on file 1.\r\nConverting database 'pagechecksumtest' from version 539 to the current version 611.\r\nDatabase 'pagechecksumtest' running the upgrade step from version 539 to version 551.\r\n(...)\r\nDatabase 'pagechecksumtest' running the upgrade step from version 610 to version 611.\r\nRESTORE DATABASE successfully processed 97 pages in 0.047 seconds (16.754 MB\/sec).<\/em><\/pre>\n
select name, page_verify_option_desc from sys.databases where name = 'pagechecksumtest'<\/span><\/pre>\n
name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 page_verify_option_desc\r\n------------------------- \u00a0 -----------------------------------\r\npagechecksumtest\u00a0\u00a0\u00a0\u00a0\u00a0       TORN_PAGE_DETECTION<\/em><\/pre>\n
Donc il faut bien penser \u00e0 l’activer \u00e0 la suite de la migration pour assurer un contr\u00f4le optimal:<\/p>\n
alter database pagechecksumtest set PAGE_VERIFY CHECKSUM<\/span><\/pre>\n
Attention, les pages ne sont pas prot\u00e9g\u00e9es pour autant imm\u00e9diatement apr\u00e8s: il faudra qu’elles suivent un cycle complet : qu’elles soient luent depuis le disque vers le buffer pool, modifi\u00e9es<\/strong>, puis r\u00e9\u00e9crites par le checkpoint pour embarquer la somme de contr\u00f4le dans l’ent\u00eate.<\/p>\n
Si on veut creuser un peu plus, on peut se demander si la page sera bien prot\u00e9g\u00e9e si elle se trouve dans un filegroup en readonly. En effet, la page ne pouvant pas \u00eatre modifi\u00e9e, le checkpoint ne pourra pas marquer le checksum dans son ent\u00eate (on peut le voir avec un dbcc page, en regardant la valeur de m_flagBits<\/em> qui normalement doit embarquer 0x200 dans son bitmap pour montrer que le checksum a \u00e9t\u00e9 calcul\u00e9).<\/p>\n
Sc\u00e9nario: <\/strong>je pars sur une base fra\u00eechement migr\u00e9e, je choisis d’ajouter un filegroup\u00a0 et de cr\u00e9er des donn\u00e9es 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’\u00e9crasement de donn\u00e9es par un filter driver ou tout autre logiciel tiers s’ex\u00e9cutant sous NTFS sera vue par SQL Server ?<\/p>\n
Voici la repro pour vous permettre de vous faire votre propre id\u00e9e:<\/p>\n
1) Sur une instance en SQL Server 2000, cr\u00e9er une base, une table et sauvegarder la base:<\/p>\n
create database tpdtest\r\nuse tpdtest\r\ncreate table T1(a numeric identity, b char(1))\r\ninsert into T1 values ('A')\r\nbackup database tpdtest to disk='F:\\SQLDATA$SQL2000\\BACKUP\\tpdtest.bak' with init, stats<\/span><\/pre>\n
2) Remonter la base sur une instance 2005 ou 2008, ajouter un filegroup, d\u00e9placer la table dans ce filegroup et le passer en READONLY:<\/p>\n
restore database tpdtest from disk='F:\\SQLDATA$SQL2000\\BACKUP\\tpdtest.bak'\r\nwith move 'tpdtest' to 'E:\\SQLDATA$SQL2005\\DATA\\tpdtest.mdf',\r\nmove 'tpdtest_log' to 'F:\\SQLDATA$SQL2005\\LOG\\tpdtest_log.ldf',\r\nstats, replace\r\n\r\n<\/span>alter database tpdtest add filegroup RO_tpdtest\r\nalter database tpdtest add file(name='RO_tpdtest',\r\nfilename='E:\\SQLDATA$SQL2005\\DATA\\RO_tpdtest.ndf') to filegroup RO_tpdtest\r\nuse tpdtest\r\ncreate unique clustered index UCQ_T1_a on T1(a) on RO_tpdtest\r\nalter database tpdtest modify filegroup RO_tpdtest readonly<\/span><\/pre>\n
Oulala mais j’ai oubli\u00e9 de passer PAGE_VERIFY sur CHECKSUM, vite:<\/p>\n
alter database tpdtest set PAGE_VERIFY CHECKSUM<\/span><\/pre>\n
Et l\u00e0 je simule une corruption de donn\u00e9es avec un \u00e9diteur h\u00e9xa dans la page de donn\u00e9es. Un coup de dbcc ind pour identifier la page en question:<\/p>\n
dbcc ind('tpdtest','T1',1)<\/span><\/pre>\n
PageFID    PagePID    IAMFID      IAMPID      ObjectID      PageType\r\n---------  ---------  ----------  ----------  ------------  -----------\r\n3\u00a0\u00a0 \u00a0      9\u00a0\u00a0 \u00a0      NULL\u00a0\u00a0 \u00a0    NULL\u00a0\u00a0 \u00a0    1977058079    10\r\n3\u00a0\u00a0 \u00a0      8\u00a0\u00a0 \u00a0      3\u00a0\u00a0 \u00a0       9\u00a0\u00a0 \u00a0       1977058079    1<\/em><\/pre>\n
La page en question est la page 8 dans le fichier\u00a0 3, donc en convertissant 8 en offset, on obtient (8*8192) = 0x10000.\u00a0 A partir de l\u00e0, je passe la base offline et avec un \u00e9diteur hexa de mon choix, je m’en vais \u00e9craser la valeur ‘A’ avec une autre valeur, ‘C’ par exemple:<\/p>\n
\"\"<\/a><\/p>\n
A ce niveau-l\u00e0, il est important de ne pas toucher aux premiers octets de l’ent\u00eate, car dans ce cas le m\u00e9canisme de TPD jouerait son r\u00f4le 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:<\/p>\n
alter database tpdtest set online\r\nuse tpdtest\r\nselect * from T1<\/span><\/pre>\n
a\u00a0\u00a0 b\r\n--\u00a0 --\r\n1\u00a0\u00a0 \u00a0C<\/em><\/pre>\n
Oooops. ..<\/p>\n
A titre de comparaison, en cr\u00e9ant le m\u00eame cas dans un filegroup en read-write:<\/p>\n
use tpdtest\r\ncreate table T2(a numeric identity, b char(1))\r\ncreate unique clustered index UCQ_T2_a on T2(a)\r\ninsert into T2 values ('A')\r\ndbcc ind('tpdtest','T2',1)<\/span><\/pre>\n
\u00a0<\/em>PageFID    PagePID    IAMFID      IAMPID      ObjectID      PageType\r\n---------  ---------  ----------  ----------  ------------  -----------\r\n1\u00a0\u00a0 \u00a0      25\u00a0\u00a0 \u00a0      NULL\u00a0\u00a0 \u00a0    NULL\u00a0\u00a0 \u00a0    <\/em>2089058478    \u00a0<\/em>10\r\n1\u00a0\u00a0 \u00a0      15\u00a0\u00a0 \u00a0      1\u00a0\u00a0 \u00a0       25\u00a0 \u00a0       <\/em>2089058478    <\/em>1\r\n\r\n<\/em><\/pre>\n
La page est la 15, donc en ouvrant le fichier MDF, \u00e0 l’offset (15*8192) = 0x1E000, on refait la m\u00eame vilaine corruption au m\u00eame endroit:<\/p>\n
\"\"<\/a><\/p>\n
Mais cette fois lorsque l’on veut relire la page:<\/p>\n
alter database tpdtest set online\r\nuse tpdtest\r\nselect * from T2<\/span><\/pre>\n
Msg 824, Level 24, State 2, Line 1<\/em><\/span><\/pre>\n
SQL 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’.
\nAdditional 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).
\nThis error can be caused by many factors; for more information, see SQL Server Books Online.<\/em><\/span><\/p>\n
Le CHECKSUM est un superset de TPD, donc \u00e7a marche.<\/p>\n
Donc r\u00e9sum\u00e9: <\/strong>
\n– Quand on migre, il faut penser aussi \u00e0 modifier PAGE_VERIFY et le passer sur CHECKSUM car ce n’est pas automatique.
\n– Le checksum en question ne sera impl\u00e9ment\u00e9 qu’une fois la page lue depuis le disque, modifi\u00e9e, puis r\u00e9\u00e9crite par le CHECKPOINT.
\n– Les donn\u00e9es dans les filegroups en read_only ne sont pas pour autant prot\u00e9g\u00e9s. Ils conservent le TPD comme seul mode de protection.<\/p>\n
A+ David B.<\/p>\n