>>>>>As for buffering on the server, never...ever... use write caching on the server unless the disk controller is specially designed to work with databases. This advice is not just for Fox data. I've been reading SQL Server Books Online lately and that warning is all over in there.
>>>>
>>>>In your experience, what are the drive controller manufacturers whose products have been "specially designed to work with databases"?
>>>
>>>I can't say I know of one above another. Just what the docs are saying.
>>
>>OIC... so MS is basically saying, don't use a disk controller with SQL Server unless we've approved it. I wonder how one does that...
>>
>
>No. They don't list any brands or models. It's basically a warning that if you do use write caching, that it can corrupt your data. I've seen this with VFP.
>
>>>>It's a bad idea to extrapolate SQL Server to all back-end or file-server databases. All applications, not just databases, are supposed to delegate file I/O to the OS; most have no other choice. However, SQL Server enjoys a (too?) close relationship to NT/W2K and uses a lot of tricks to improve performance. It's not surprising the SQL Server docs are littered with warnings.
>>>
>>>Issues are different with databases apps as opposed to non-database apps. Word, for example, pulls the entire document into memory. Database apps inheritly have problems with write caching because of what they do.
>>
>>Like what?
>
>- Person A edits a record, then saves changes. Person B retrieves the record, but doesn't see the changes.
>
>- Person A adds a new record. The insert code does a lookup in a table to get the next Primary Key, then increments the counter in the field. Person B then does the same thing. However, because the writes from person A are cached, person B gets the same key.

These are both examples of what happens when write caching doesn't work properly. When it's working properly, they don't happen.

Write caching is simply a part of a persistent data store, that typically depends on 3 things:

- Disk and controller hardware
- Software driver(s) for the above
- OS support for the relevant I/O

If there are problems with any of these, your data store will be unreliable. In some cases, turning off write caching may alleviate some of the problem, but it certainly isn't a fix - it's a band-aid at best. If you're running an OS/hardware combination that won't reliably support write caching, it simply isn't reliable - period. If it's mission-critical, it's imperative to lean on the vendors for a fix.

Write caching is a technology that's been around for a long time. It's well understood and has been reliably implemented in thousands of different configurations. Turning it off can result in a large performance hit and should never be undertaken lightly.