SQL Server Database Architecture

In the previous post, we have learnt how to drop database in SQL Server. We have discussed about MDF and LDF files. These files are important for performance of databases in SQL Server. These files are useful in understanding database architecture.

Learning about database architecture is important for DBA to administer databases in SQL Server. Even database developer can benefit from this knowledge. The performance of database and fine tuning database is based on database architecture.

SQL Server Storage Concepts

You have seen that database can be created in GUI mode or by using the SQL query in SSMS. When you create a new database, you can specify the size of the database and its owner. Every database will have two files- data file for primary data storage and log file for logging purpose. They have file extension MDF and LDF respectively. Database size is resized over the time when new database objects are created. Every database has some default size when it is created. Right click a database and click Properties menu item to see its size etc.

A database has two types of files to store data. They are data files and transaction log file. Data files are further of two types - primary data file and secondary data file.

Database files

1. Data Files
   1. Primary data file
   2. Secondary data file
2. Transaction log file

Like file system, database that is created in SQL Server and all its objects are stored permanently in hard disk. SQL Server uses three different types of files to store databases on disc. They are primary data files, secondary data files and transaction log files.

Primary data files have MDF file extension. When a database is created in SQL Server, this file is created. This file contains different user defined objects such as tables, views and various system tables which are created inside the database. System tables created in the database are used by SQL Server to keep tracking of the database. If the size of MDF file grows to its maximum value then secondary data file is created which file extension is NDF.

The data files can be scattered on different disks. The primary data files are part of PRIMARY file group. Secondary data files are part of another file group as per the file group created by the user. You can create different file groups for them.

Transaction file has the file extension LDF. Whenever any transaction occurs in database then log is generated and saved into transaction file. Note that data is never directly saved into data file. First of all, it is saved into transaction log file. Later it is committed into the data file when transaction is committed. SQL Server first of all writes data into transaction log file then into data file. This is called write-ahead log.

Data is stored into pages. Page is the smallest unit of data storage in SQL Server. Each page is of 8192 bytes. Depending on the data types there are different types of pages.

An extent is a collection of 8 continuous pages which is used to keep the database from getting fragmented. Suppose a table data is stored in different pages and these pages are scattered in database file. This will be known as fragmentation of the pages. If database pages are scattered then performance of database will be affected badly. SQL Server tries to organize the pages according to extent.

In the next post, you will learn about how to create tables in a SQL Server database.