SQL Server IO - Continued...

Now that we understand what is "Transaction" and "ACID properties" of transaction... lets get moving on the next set...Write Ahead Log (WAL) :O

What!?... How is WAL related to ACID properties or transaction? Yes, they are related.

WAL is technique which helps to adhere to two of the four ACID properties "Atomicity" and "Durability". Now that we understand what is atomicity and durability... next is to understand "How WAL works" and also we need to peep into the advantages we get from it.

Let me just put my words as per SQL Books online which is very much easier to understand:
Write-ahead log (WAL) guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.

To understand how the write-ahead log works, it is important for us to know how modified data is written to disk. SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification needs to be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.

At the time when modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

I hope the above is very much clear in its explanation...

And this is it for now...

SQL Server IO

I had the opportunity to look back into the basics of SQL Server IO. To start off... first was to understand "What is Transaction" and then "Transaction properties".

What is Transaction?
There can be many ways of defining the same... however, the most simple could be as follows:
A transaction is a logical unit of work in which, all the steps must be performed or none.

Once we understand "what is transaction"... next is to understand "the properties" exhibited by transaction.
There are FOUR important properties of a transaction:
1. Atomicity
2. Consistency
3. Isolation and then
4. Durability

ATOMICITY - Any... for that matter any database modifications must follow an “all or nothing” rule... which means: If one part of the transaction fails, the complete transaction fails. Each transaction is said to be “ATOMIC”. It is critical that SQL Server maintains the atomic nature of transactions... not only SQL Server any DBMS, operating system or hardware failure.

CONSISTENCY - States that only valid data will be written to the database. In simple words if the database was consistent before the execution of the transaction then it should remain consistent after the complete execution of that transaction.

For some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.

On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.

ISOLATION - First, let me put it in straight words: The transaction should not be interfered by any other transaction executing concurrently.

Now that means - multiple transactions occurring at the same time not impact each other’s execution. For example, if User A issues a transaction against a database at the same time that User B issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform User A's entire transaction before executing User B's or vice-versa.

This helps in preventing User A's transaction from reading intermediate data due to User B's transaction that will not eventually be committed to the database.

We  always get into confusion w.r.t the way we describe isolation. We should alyways remember that isolation property does not ensure which transaction will execute first, it is just that transactions will not interfere with each other.

DURABILITY - means any changes made by the transaction should be permanently committed in the database. This ensures that any transaction committed to the database will not be lost in spite of any subsequent software or hardware failures.

To ensure Durability is at its best we make use of database backups and transaction logs that facilitate the restoration of committed transactions.

I hope the above helps us to understand ACID properties in a simple way! 

Phases of Database recovery

From my previous post “What happens when a SQL Server instance is restarted?” we know what activities will be carried out when the SQL Server instance gets restart request.

Now, it’s time to understand what recovery phases the database will undergo.
The databases undergo recovery phases in two scenarios

1. When the SQL server  or service is restarted
2. When the database is being restored.

There are 3 Phases of Recovery and are based on the last checkpoint in the transaction log.

Recovery Phases - Drill Down

Recovery Phases - Graphical