Does SQL Server guarantee accurate data storage? If so, how?

Question

This question is specific to SQL Server, but other DB replies welcome.

Read below for some history. 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!

Answer 1

All the major SQL databases provide this; it is called being ACID compliant. This is probably the key phrase you need to be looking up in the documentation (and/or Google).

As for how they do it, MS SQL Server is closed-source, as is Oracle, so no-one outside the company can really answer that question, but as you say, if they didn't manage it for large-scale systems, their products would be out of the market very rapidly.

Answer 2

Can't help with SQL Server but Oracle has a couple of parameters that can be set to validate against memory or disk corruption including DB_BLOCK_CHECKSUM and DB_BLOCK_CHECKING. At the top end, Oracle can automatically go to backups or a DR location to get a 'clean' copy of corrupted block.

Obviously a RAID configuration can also help protect against disk failures. As pointed out in comments, at the hardware level a desktop machine isn't built to the same standards of resilience as a server.

Answer 3

The answer to this question is "no".

I just finished reading SQL Server 2008 Internals and the last chapter in that book written by Paul Randal on DBCC internals covers the huge variety of possible corruptions that can happen to database pages that can be detected by DBCC CHECKDB.

You can use checksum protection on your data pages, backups, and regular checks for corruption with DBCC CHECKDB

Your plan of writing and re-reading data seems somewhat pointless as unless you are continually running checkpoint to ensure all dirty pages are written to disc and then dbcc dropcleanbuffers you will likely just get handed the page straight from memory anyway.