SQL Server Database Architecture

SQL Server stores data mainly in two types of files.
1. Data File(MDF)
2. Log File(LDF)

Data File(MDF):
Data file stores actual data.
It stores permanent data.
Extension of data file is .mdf

Log File(LDF):
Log files stores modified recorded information. ldf contains whatever changes we are performing on database.
Extension is .ldf

We have another file called seconday data file and extension is .ndf. A database may or may not have secondary data files.


Buffer is a RAM to perform modifications on a copy of permanent modifications on a copy of permanent page.

How Buffer works:

SQL Server will not allow to do modifications directly on mdf. SQL Server will make a copy of pages from mdf to buffer.
Once transaction is fully committed, it records the information that what type of data is inserting, number of pages effecting, what the user is performing.
All these changes related information will record in same sequential way in log file.
Pages will stay same time in buffer for faster retrieval.
Read and Write operations from buffer will be very faster comparing to operations from mdf data.
Using recorded information whenever checkpoint runs on log file, it applies same changes to permanently on mdf file.

SQL Server Checkpoint: Read more information at SQL Server Checkpoint Process

Recovery Process in SQL Server:

Whenever SQL Server restarts checkpoint verifies pending transactions before restart. SQL Server will perform recovery process. This process will analyze what is the state of log file and performs two operations:
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 deleted from log file.

Once recovery process completes, then only users can able to access the database.

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

It uses LRU algorithm in clearing. LRU stands for the least recently used pages. On page header of page there will be reference counter means how many times this page being used, based on counter least used pages will be detected in buffer.

Dirty Pages:
Pages commit in log file ad waiting for checkpoint to move to mdf, those are called dirty pages.

Powered by k2schools