Delayed Durability In SQL Server 2014

DELAYED DURABILITY In SQL Server 2014

Delayed durability return the control to client/application before the data is committed to disk from Memory/Log.

let us understand Fully & Delayed durability:

Fully Durable Transaction (SQL Server):  First Data is written to Log --> Data is committed to Disk -->successful commit Command is issued to Client/application

    1 .       No Data loss

    2 .       I/O latency

    3 .       Synchronous

Delayed Durable Transaction (SQL 2014):  First data is written to Log --> Successful commit command is issue to client/application --> later data commits to Disk

    1 .       Data loss of the defined interval

    2.       Less I/O Latency and contention(Data is committed in batches)

    3.       Asynchronous

In Delayed Durable Transaction is visible to other transactions:

   1 .  When fully delayed transaction commits the changes to disk in same database

   2 .   When we manually successfully Executes system defined procedure sys.sp_flush_log to write            the log info to disk.

   3 .    In memory transaction log buffer fills up and flushed the log to disk.

Delayed Durability can be controlled at  Database level, Commit Level, Atomic block Level

ALTER DATABASE DBNameHere

SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

Delayed Durability Vs SQL Features

S#	SQL Features	With Delayed Durable
1	Always On/ Mirroring	No Guarantee ( Some Data may lost)
2	Failover Clustering	No Guarantee( Some Data may lost)
3	Transactional Replication	No Guarantee(Only durable data is shipped))
4	Log Shipping	No Guarantee(Only durable data is shipped)
5	Log Backup	No Guarantee(Only durable data is backed up)
6	Cross Database and DTC	No Guarantee(Only durable data is backed up)
7	Crash Recovery	Data is consistent only for Durable Data
8	CT & CDC	No Guarantee(Only durable data is backed up)