Checksums & Server Migrations

• Jim_K

  SSCrazy

  Points: 2418

  More actions

  March 28, 2012 at 8:28 am

  #253863

  Hello,

  I find myself in a bit of a strange situation. I work with a database that is part of an application by an outside vendor, and while doing some testing prior to an upgrade up to SQL 2008 R2 from SQL 2005 I queried sys.databases and noticed that page verification was turned to off on a couple of the databases. I'm not the DBA (just a developer) but I found this strange and asked our sysadmin to ask the vendor why this wasn't on and if we could turn it on going forward as the data contained within is sensitive and should be protected to the best of our ability.

  The vendor replied it wasn't turned on because their databases were created prior to SQL 2000 and, dbcc checkdb should be enough to catch any corruption, the database is I/O heavy and they had performance concerns, also that since only updated pages would get the checksum you wouldn't see much of the benefit and all of the performance penalties. Because of all this they don't turn on checksums, and that as far as they knew no other client had them turned on either.

  Now that you've read through all this (stay with me) I have three questions:

  1) I've read that there are cases where dbcc checkdb won't catch corruption when checksums are off (as shown here[/url]) Are there any other benefits for Checksum's being on? The only other thing I can think of is that you can do your backups with checksums for verification.

  2) Searching around I've seen that the rule of thumb for performance impact for page verification is around 2% CPU load. IO shouldn't be affected from a volume perspective because data pages are 8KB and the checksum is part of the header, right?

  3) If, when migrating your databases, you turned on checksum page verification on the old server, backed up the database, and restored it to the new server, would the act of restoring all the pages to the new server cause the checksum to be added to the page?

  Thanks for your help and guidance,

  Jim

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  March 28, 2012 at 11:44 am

  #1465879

  I had some of this scenario too with inheriting DBs created in 2000 but now on 2005, and I enabled CHECKSUM. It's a DB engine feature that has no logical bearing on the outcome from queries, i.e. performance implications only and impact is minimal unless you're system is already stressed and nearing a tipping point on major resources. Barring you being close to said tipping point (only you can judge) I would not listen to the vendor in this instance. It is true that your pages will not contain the CHECKSUM until they are written back to disk the first time, i.e. a 'size of data' operation is the only way I know to get CHECKSUMs written to existing pages, and I did not go there. CHECKDB is still going to be your friend, but seriously, I would enable it.

  As a side note, since you are dealing with databases originally created in 2000 here is another item for you to consider regarding data_purity: CHECKDB From Every Angle: How to tell if data purity checks will be run?

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Jim_K

  SSCrazy

  Points: 2418

  More actions

  March 28, 2012 at 1:12 pm

  #1465935

  Thanks for the link to that article! I didn't know that data purity checks were disabled on pre-2005 databases by default.

  Update: Looking at some test restores on our to-be-production server we never have done a DBCC CheckDB with data purity...