Recently we had a discussion at work place about setting up the PAGE_VERIFY option to CHECKSUM for few databases on one of the SQL Server 2005 instance (dedicated) that is being used extensively by an application that has very limited maintenance window in terms of 24/7 usage.
By default you can enable of disable this PAGE_VERIFY option by using ALTER DATABASE statement [Set the PAGE_VERIFY Database Option to CHECKSUM] that can be affected per database level, the main advantage of this option is this can discover damaged database pages caused by disk I/O path errors. Such errors might have caused due to a bad disk or corrupted spindle of disk that throws out I/O path errors which can be the cause of database corruption problems and are generally caused by power failures or disk hardware failures that occur at the time the page is being written to disk.
Such significant changes betweeen the versions 2000 and 2005 (2008 is similar) that SQL Server tries to prevent paging activities by reducing its committed footprint when possible. When a user or system database is upgraded to SQL Server 2005, the PAGE_VERIFY value is retained as NONE or TORN_PAGE_DETECTION, in general the best practice recommends to use CHECKSUM option. Due to the fact that TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the CHECKSUM protection. So the option for PAGE_VERIFY can be set without taking the database offline, locking the database, or otherwise impeding concurrency on that database, but one thing to keep in mind that you cannot enable both CHECKSUM and TORN_PAGE_DETECTION at the same time on the database.
So talking about how the memory processes are paged out that are written to teh disk that during the standby and hibernate actions, paging involves other disk storage devices; these other devices, although they are not used by SQL Server directly, should also be SQL Server I/O-compliant to ensure data integrity. In majority of cases that it is difficult to protect against the in-memory data corruption that is caused by the paging of a data page. Both 2005 and 2008 versions of SQL Server are designed better to perform latch enforcement for database pages located on the buffer pool cache. What it means is, this can be used to change the virtual memory protection (VirtualProtect) as the database pages are transitioned between the clean and dirty states. One of the Technet article refers that
: ... default latch enforcement protection setting is disabled. Latch enforcement may be enabled with trace flag –T815. SQL Server 2000 SP4 and 2005 allow for the trace flag to be enabled and disabled without a restart of the SQL Server process by using the DBCC traceon(815, -1) and DBCC traceoff(815,-1) commands. Earlier versions of SQL Server require the trace flag as a startup parameter.
The general recommendation within Books ONline and forums you see whenever a torn page or checksum failure is detected, the best option to recover the data is by restoring the data or potentially rebuilding the index if the failure is limited only to index pages. As the internal architecture of backup and restore operations that use checksum capabilities increase data integrity protection and also increase CPU usage requirements. A backup or restore with the checksum option requires that each byte be interrogated as it is streamed, thereby increasing CPU usage. The checksum that is used for backup and restore uses the same algorithm to calculate the checksum value for the backup media as is used for data pages and log blocks. But as a whole if you encounter a checksum failure on the database, to determine the type of database page or pages affected, run DBCC CHECKDB to fix the inconsistency errors.
So going further on enabling page CHECKSUM option on the database here is the excellent coverage by Linchi Shea, SQL MVP Performance-Impact_PAGE_CHECKSUM blog post and also another reference from Microsoft Storage Engine Blog Enabling_checksum post. In addition to these 2 here is another reference from real-world examples from Paul Randal on Page-Checksums blog posts on their site.
By default all the newly created databases within SQL Server 2005/2008 will have PAGE_VERIFY option to CHECKSUM and if you have upgraded databases then the checksum will only be calculated going forward. It isn't calculated for all existing data unless it is read from the database, changed and written back, as referred by blog post above.