SQL Server follows ACID properties for all the transactions where A stands for Atomicity, C stands for Consistency, I stands for Isolation and D stands for Durability. In this lab we’ll be talking about the durability. In SQL Server, a transaction will be durable if it has been committed successfully, i.e. all the transaction records generated by a transaction (DML operation) are written to the transaction log file on disk. Whenever a DML operation is performed, the affected pages (Dirty Pages) are written to log buffer. The affected pages aren’t written to the physical transaction log file immediately. By default, SQL Server follows full durability where commit is a synchronous operation, i.e. the control (query successful message) will not be return to the client until all the log records related to the transaction are written to the disk. In this lab, we’ll learn about the delayed durability feature in SQL Server 2014 where commit operation works in asynchronous manner i.e. control will be returned to the client even when the log records related to the transaction have not been written to the disk. In the first exercise, we will learn about full durability and how SQL Server flushes the log records to the transaction log file. In the second exercise, we will learn about delayed durability and steps to configure it. In the third exercise, we will learn about a scenario where we can use delayed durability. In the fourth exercise, we’ll talk about delayed durability for memory optimized tables.
Exercise 1: Full Durability and Log Buffer Flush
Exercise 2: Delayed Durability in Action
Exercise 3: Delayed durability Scenarios
Exercise 4: Delayed durability with Memory Optimized Tables