Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Jan 21 '12 at 20:20

This question came from our site for professional and enthusiast programmers.

1  
You can use checksum protection to make detection of issues probable. I'm not sure that a 100% guarantee of error avoidance or detection is possible though as it depends on hardware. –  Martin Smith Sep 20 '10 at 14:47
    
What was the nature of the corruption you experienced with the Express version? –  Martin Smith Sep 20 '10 at 15:07
    
Sorry, don't know about the corruption; it was always a matter of getting operational quickly, so we generally just restored the database. But these are desktop machines running a host of programs including SQL Server Express, the programs for line operation, virus scanner, etc. That does bring up an interesing point, does Express have the same data guarantees as Standard, Workgroup, or Enterprise? It's a good thing we are getting away from local data storage. –  logo1664 Sep 20 '10 at 15:23
1  
@logo1664 - In my own experience with large-scale desktop deployments of Express (this is for backoffice reporting in POS Systems), the only failures occur because of hardware issues, or environmental (loss of power while DB is being written) type issues. We had over 1000 sites deployed, each site with at least 1 GB-size database, some with up to 7 databases, and I never had a non-attributable data integrity issue. –  JNK Sep 20 '10 at 15:52
1  
Storing data on the desktop means it is subject to risks that don't usually apply to servers, such as power cuts, unexpected shutdowns and interference from other software. Also, desktop drive controllers don't support the kind of write guarantees that are the norm for data centre storage. So this doesn't sound like you had a problem with SQL Server. Much more likely it's due to the limitations of running data on the desktop. Ask your server administrators about the high availability features of your servers, UPS and storage. –  sqlvogel Sep 20 '10 at 15:55

3 Answers 3

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.

share|improve this answer
    
I take it you're referring to the "D" in ACID? This just means write ahead logging AFAIK. It's not going to cover page corruption due to hardware issues. –  Martin Smith Sep 20 '10 at 15:02

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.