Force database to persist on commit? I was wondering whether databases such as SQL Server was writing changes to persistent medium (disk) whenever a operation is completed, but it seems not? Not even when committing a transaction it seem to do this but only commit to RAM. Correct me if I am wrong. The disk-write seem to happen on CHECKPOINT which is server-initiated and perhaps doesn't have to happen straight efter a COMMIT? In any case, my questions are:


If an update to the db is not persisted before returning OK to the client, what then will it do if the update fails in the end? How will the client know this? Can a client somehow instruct the db-server to really persist before saying "all is fine"?
Is there a good reason for not just persisting to disk directly instead of RAM in a modern server with really fast SSD-disk? Only if the performance is really critical? More so than persistence?
SQL Server does indeed write at commit. What it writes is to the transaction log, however. The data (dirty pages) can be written at a later time (for instance at checkpoint). Thanks to "force log write at commit" SQL server can re-construct a consistent view of the database at startup, where what should be committed is persisted.