Delayed Durability - Getting Started with SQL Server 2014 Administration - page 56

Delayed Durability and other SQL Server
components
How does Delayed Durability integrate with the components of SQL Server? We will
have a brief discussion here on how Delayed Durability will work with some other
components of SQL Server.
Let's start with the most important: crash recovery. You, as the DBA, have no control
over crash recovery. Crash recovery is the process that brings the database into a
consistent state after the system has crashed for whatever reason. It is possible that
changed data pages have not been written or hardened to the data file. In previous
versions of SQL Server, they would have been written and hardened to the log-
file as part of the commit process. With Delayed Durability in SQL Server 2014, its
possible, using asynchronous commit, that transactions have not been hardened to
the transaction log on disk and as such these transactions will be lost even though
a commit was issued. The database will still be in a consistent state but potentially
data will be lost. The following is a really useful TechNet article on understanding
SQL Server logging and crash recovery: http://technet.microsoft.com/en-us/
magazine/2009.02.logging.aspx .
AlwaysOn Availability Groups have been enhanced in SQL Server 2014 and you
can combine AlwaysOn Availability Groups with Delayed Durability. Be aware that
durability is not guaranteed on either the primary or secondary. In fact, it is possible
that the secondary will not have knowledge of the transaction on primary until it
hardens to the log. This is true for secondary setups in synchronous commit mode.
In reality, if you are using synchronous commit mode with AlwaysOn Availability
Groups, you are likely going to be using Delayed Durability on your database
anyway—as data loss will be unacceptable. But it is worth pointing out that if you
set up your database to use Delayed Durability, then you could potentially lose
data in the event of a failover.
Much the same is true with SQL Server failover clustering. In the event of a failover,
crash recovery will run on the second node when SQL Server starts up. If crash
recovery runs and the transaction and its data changes have not been hardened to
the log, then those transactions will be lost.
Transaction log backups will only contain transactions that have been made durable.
So unless the transaction has been hardened to the transaction, they will not be
included in the transaction log backup.