patternsqlMinor
Does SQL Server guarantee accurate data storage? If so, how?
Viewed 0 times
guaranteesqlstorageaccuratedoeshowserverdata
Problem
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!
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!
Solution
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
You can use checksum protection on your data pages, backups, and regular checks for corruption with
Your plan of writing and re-reading data seems somewhat pointless as unless you are continually running
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 CHECKDBYour 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.Context
StackExchange Database Administrators Q#11223, answer score: 4
Revisions (0)
No revisions yet.