Any database professional know what it means by ACID. They are some golden properties of relational systems.
A – Atomicity
C – Consistency
I - Isolation
D – Durability
SQL Server supports ACID properties fully until SQL Server 2012. In SQL Server 2014 (still in CTP level) DBA has the option to control the D of ACID. Till SQL Server 2012, the log records need to be flushed to the disk before a transaction can be declared committed. However in SQL Server 2014, this statement is not 100% valid with the introduction of new feature called “delayed durability”.
With this new option, SQL Server transactions can be either fully durable or delayed durable. BOL states;
“Fully durable transaction commits are synchronous and report a COMMIT as successful and return control to the client only after the log records for the transaction are written to disk. Delayed durable transaction commits are asynchronous and report a COMMIT as successful before the log records for the transaction are written to disk. Hardening transaction log entries to disk is required for a transaction to be durable. Delayed durable transactions become durable when the transaction log entries are flushed to disk.”
As it obvious thing, the delayed durability comes with a cost that, there could be a data loss in the event like fail over. The advantages of this option are, reduce contention and increase throughput.
Use of delayed durability option is not suitable for all the cases. This can not be used in situations where you cant tolerate a data loss.
Delayed durability option can be applied at Database level, Atomic code block level and Commit level.
It is worth to be considered why Microsoft changed the ACID behavior after 22 years of SQL Server history. I believe this is an response to the highly volatile database technology market which we are seeing today, specially with the widely use of NoSQL technologies. NoSQL technologies are using “BASE” (Basically Available, Soft state, Eventual Consistency) against the ACID in relation systems. With the introduction of BASE the NoSQL vendors claim that the support for big data and huge performance gain in OLTP as opposed to the relational databases.
Finally, I believe it is a great option and DBAs will like the new feature. However, SQL Server 2014 is still in CTP which means it is subjected to change in future releases, sometimes this could be completely removed.
For more details, How to: Control Transaction Durability
