Checksum features in SQL Server 2005

Original: Checksum features in SQL Server 2005

Go from here

Page checksum is a new feature of SQL2005 that provides a powerful mechanism for detecting IO damage than torn page detection. The following is a detailed description:

Page CHECKSUM:

During the time that the database page is written from the disk to the SQL Server, it is possible that the damage occurred due to external causes. For example I/O device damage, drive damage or due to power instability is not written complete. The checksum mechanism enables SQL Server to detect these corruptions. It is important to note that the checksum mechanism only allows us to determine if the I/O subsystem causes page corruption, but cannot fix them automatically. As an administrator, we can use this information to identify and replace damaged I/O devices, or to repair a page corruption that has occurred using methods such as rebuilding the index or repairing corrupted files.

The checksum feature can be turned on and off on each database individually by the ALTER DATABASE command on the database. When checksum is turned on, SQL Server calculates the checksum of the page when the page is written and read. SQL Server calculates the checksum based on the bit pattern on the page, stores it in the page header, and then sends an I/O to write the page. SQL Server computes the checksum again in the same way when the page is read and compares it to the checksum value stored in the page header. If the results are the same, the page is not corrupted during the read-write cycle. The algorithm for computing checksum is complex because it involves reading and writing multiple pages, increasing the load on the CPU and possibly affecting the throughput of the system. In addition, checksum is not corresponding to the page bit mode one by one, different pages may also have the same checksum value, so there is a very small possibility that the page corruption can not be checked out.

Here are a few things to note:

Database has been created for a period of time before opening checksum

The checksum is calculated only when the checksum feature is turned on every time the page is written, and it is not recalculated until the page is read. If checksum is not opened at the beginning of the database creation, there will be some pages that are not logged checksum. When these pages are read, SQL Server does not calculate checksum because there is no checksum that can be used to compare, the calculation is meaningless. There is currently no command or tool to force the calculation of checksum for all pages of the database. Do this and?? There are too great benefits. Imagine, when the data page has been destroyed, we can only calculate the damaged checksum, and do not know the normal situation of checksum, damage can not be detected. Of course, if there is such a command or tool, we can detect a corrupted page due to bit corruption, such as a valid page corruption after being written to disk, or if the database file is transferred or copied to another medium.

L Stop checksum function

You can stop checksum functionality when you don't want checksum to consume CPU resources or make sure that I/O systems are reliable. After stopping, the checksum value is no longer calculated and logged. In this case, SQL Server could be affected by external page corruption, and an undetected corruption could result in:

o data loss unless full backup recovery is run (provided the log backup is not corrupted)

o Potential application exceptions, especially if corruption does not change the page structure but only changes some of the data.

o Reduce the availability of the database because media recovery must be performed to remediate corruption.

How to verify the checksum of all pages in a database

With the DBCC CHECKDB command, it is best to add the physical_only parameter to reduce resource consumption.

Database open torn pages (torn page) detection

Database checksum detection and torn page detection cannot be turned on at the same time. Because the checksum mechanism for page corruption verification is more stringent than torn pages, and produces checksum matching but the likelihood of page breakage is very small, so open checksum equivalent to automatically get torn page detection. If you turn off the checksum feature of the page, we recommend that you turn on torn page detection to detect I/O subsystem exceptions at a lesser cost.

Backup checksum

Backup checksum, like the page checksum algorithm, calculates checksum whenever a page is backed up, except that the values of multiple pages checksum are combined into a single backup checksum value. The backup checksum is stored in the backup medium instead of the page. During backup you can choose to generate backup checksum for recovering backups when detecting backup damage. In addition, if the page checksum is available, it can be used for confirmation during backup and recovery.

Checksum features in SQL Server 2005