Whitepaper : 5 Most Common Critical SQL Integrity Issues

The word “integrity” has multiple meanings in the SQL Server world. One meaning deals with the quality of data stored within SQL Server. This form of integrity is maintained through proper data typing, constraints (default, check, unique, and key), and data validation processes. This is the application/database design side of things. The other definition of integrity deals with the logical and physical consistency of the underlying structures of a database. This is an important part of keeping the lights on.

Integrity is ensuring that the logical and physical data stored in SQL Server are structurally sound and consistent. Simply put, it means that the data present in SQL Server is written correctly and is where it is expected to be. Why is this important? Simply put if SQL Server can’t read or find the data then it may as well no longer exist. One bit o in the wrong page can cause a loss of the entire database. There are different tolerances for data loss of course, but I think we can all agree that it’s bad.

SQL Server has functionality built in for protecting the integrity of data written to the disk. Some of this functionality is enabled by default and some is not. Even the integrity functionality that is enabled by default is not in use by a lot of the systems out there. You can turn it off without realizing if you are not careful.

Corruption and other data integrity issues can be scary to deal with, but if you follow the advice and tips provided in this whitepaper, you will limit your exposure to corruption and always be able to take the quickest route to resolution with minimal or no data loss and minimal downtime. Just remember to stay calm and walk through the steps. As database administrators, we instinctively want to fix something that is broken, but we have to be careful because jumping to action without forethought can cause more damage than good. Oftentimes, the reactive steps people take can result in a non-recoverable situation.

This whitepaper describes the definition of integrity, page validity, backup checksums, how to determine which pages failed, call to action, reading integrity check output, corruption in allocation pages, corruption in clustered indexes and heaps, corruption in nonclustered indexes, chain linkage problems, and data purity errors.