September 20, 2010 at 8:56 am
I cross-posted to stackoverflow (http://stackoverflow.com/questions/3752510/does-sql-server-guarantee-accurate-data-storage-if-so-how) but thought I would post to a SQL Server-specific forum.
My short question is, how does SQL Server guarantee that when I request "ABCDE" to be written to the database, that "ABCDE" is written, not "ABECD"? It has been suggested that data be re-read and compared to guarantee the same results, with the obvious performance penalty.
The company I work for is launch a high-volume product for us (~35,000 pieces each day) for which we will collect data, stored in SQL Server.
Previous, lower-volume products had data stored at the line, locally in SQL Server Express databases, then periodically copied to the server (SQL Server Standard) using cross-server transactions. On occasion, these desktop databases became corrupted, often requiring restoration.
Our new product's process will write to the server directly, bypassing local / desktop storage.
Our management is carrying forward this "desktop corruption" experience into the new product.
Other than appeals to authority (Microsoft would not be in the database business if it didn't store data accurately) or anecdotal experience as evidence (in my five years managing SQL Server databases, have never seen a server [Standard / Enterprise] database become corrupted), is there any Microsoft documentation referencing SQL Server "guarantees"?
This is such a basic truth that is rarely questioned, especially with the standard players (DB2, Oracle, SQL Server).
Thanks!
September 20, 2010 at 9:16 am
SQL's got what it calls a page checksum. If the page verify property of the database is set to Checksum, all data pages are protected by that checksum.
When a modified data page is written to disk, that checksum is computed and added to the end of the page. When the page is read back, the checksum is calculated again and checked against the checksum stored on the page. If they are different, the page is flagged as corrupt (firing a severity 24 error message)
Such data corruption means that either the page was modified after SQL requested it be written to disk or while it was on the disk. Either way, SQL considers it a critical error (the highest severity possible is 25).
Such data corruption is typically (in about 99% of cases) a faulty IO subsystem.
You said that periodically the express database became corrupt. That should not be able to happen unless the disks they were on was faulty, and if so, it's not something that should happen often. If it is, I'd be hunting long and hard for a root cause.
There's a reason that basic truths such as 'data is written correctly' aren't questioned. Bugs that caused incorrect writes would be picked up early in testing/beta testing of the product.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 9:19 am
Checksums in the page header will check that the data has not been corrupeted by the i/o system.
I would be worried about your SQL express databases becoming peridoically corrupted, this should not be happening and would be a sign of a faulty i/o system. SQL server will not courrupt data by itself (unless you find a very rare un-reported bug)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply