Skip to main content
Our Tech Ideas

SQL Server Database Architecture

SQL Server Database Architecture

SQL Server data mainly in 2 types of files,

  1. Data File (MDF)
  2. Log File (LDF)

Data file stores actual data with .mdf extension. It stores permanent data. Log files stores modified recorded information with .ldf extension. We have another file called secondary data file .ndf file extension. A database may or may not have these secondary data files.

Transactions

A Transaction is a set of T-SQL statements that read and wt=rite data into the database. There are 2 types of Transactions.

  • Implicit Transactions
  • Explicit Transactions

Implicit Transaction: Implicit transactions are these without begin transaction

Explicit Transaction: Explicit transactions are started using begin transaction and are controlled by using T-SQL Command commit transaction (or) Rollback transaction. Any transaction should process ACID properties then only changes more from LDF to MDF.

ACID Properties in SQL Server

Atomicity means all the statements of a transaction must complete successfully or rolled back completely means either all (or) None to updated.

Consistency

Consistency means a transaction never leaves database in half-finished state, whenever any change happen on parent object it should automatically reflect on dependent child object to ensure that database in a consistent state.

Isolation

Isolation keeps changes of incomplete transactions independent from one another.

Durability

Once a transaction is committed, it must be permanent even if there is a system failure means it cannot be rolled back.

Database Architecture

SQL Server stores data mainly in two types of files.

  1. Data File (MDF)
  2. Log File (LDF)

MDF  – It contains Permanent Data

LDF – LDF contains whatever changes we are performing on the database all the change-related information will be recorded in the LDF file.

SQL Server Buffer

Buffer is a ram to perform modifications on a copy of the permanent page. Once it commits record the information will record in LDF and the same changes apply on MDF when checkpoint runs.

How Buffer Works

SQL Server will not allow doing modifications directly to MDF. SQL Server will make a copy of pages from MDF to buffer. Once the transaction is fully committed it records the information that what type of data he is inserting, Number of pages affecting, what he is performing all these change-related information will record in the same sequential way in Log File. Pages will stay some time in the buffer for faster retrieval read and write operations from the buffer will be very faster comparing to operations from MDF Data. Using recorded information whenever checkpoint runs on the log file. It applies the same changes permanently on the MDF file.

SQL Server Checkpoint Process

The checkpoint is an internal mechanism performs regular based on the number of transaction (or) number of pages there is no time interval for running this. Checkpoint scans log file, checks how many committed transactions are there, how many failed and how many still running committed transactions more to MDF, failed transactions will be rolled back. Currently, running transactions will not be touched by the checkpoint.

Advantages of the checkpoint in SQL Server,

  1. Checkpoint help in the speeding recovery process
  2. Checkpoint helping in committing data permanently

SQL Server Recovery Process

Whenever SQL server restarts checkpoint verifies pending transactions before the restart, the SQL server will perform the recovery process. This process will analyze what is the state of the log file and perform 2 properties.

  1. Redo (or) Roll forward –  committed changes will be moved from LDF to MDF permanently.
  2. Undo (or) Roll Back – failed transactions and running transactions will be deleted from log file.

Once this recovery process complete then only users can able to access the database.

Lazy Writer in SQL Server

Modified pages will be in buffer some time, whenever the buffer is about to fill with these modified pages, Lazy writer is another internal mechanism usually in sleep mode invokes and clear the buffer pages.

It uses the LRU algorithm in the clearing, LRU stands for L. Recently used pages, on page header of the page there will e reference counter means how many times this page is used, based on counter least used pages will be deleted in the buffer.

Dirty Pages

Pages commit in the log file and waiting for checkpoint to more MDF, those called dirty pages.

#Ref. Ram Mohan & Rama Krishna notes

Below topics further included in this series