SQL Server Database Architecture

SQL Server database is a logical collection of data. In SQL Server, data is stored in two set of files, Data files and Log files. When we create a database there are two files created for every database that is the Data File (.mdf) and Log File(.ldf) and a Primary filegroup (a container) is created in which .mdf (Primary Data File) resides. Log file cannot be contained in a Filegroup. It is always recommended to store the Log File in a different disk separate from data file. Maximum size of the database in Enterprise Edition can be 524 Petabytes. 1 Petabyte is equal to 1024 TB and 1 TB is equal to 1024 GB.     

DATA files: –

  • Data files in SQL server stores the actual data.
  • We can create multiple data files for a particular database.
  • Maximum size of each data files is said to be 16 TB.
SQL Server Database Architecture

Database has two different types of data files as s shown in the image.

1.MDF (Master Data File): –

  • This is a default data file, which is created when a database is created.
  • We cannot delete this file, also we cannot create more than one MDF file, there can be one and only MDF file per database.
  • Database will not be available without MDF file
  • This file stores user data along with some Metadata (data about data) about the database configurations like collation, recovery model, user’s information and roles etc.

2.NDF (User Data File): –

  • This is a secondary data file. We can create multiple ndf files with any extension.
  • Data files can be grouped with filegroups.

LOG files: –

  • This file stores all the transactional logs in sequential order, each transaction will be given a unique number called LSN (Log Sequential Number).
  • We can create multiple log files per database.
  • Log files cannot be grouped under file groups.
  • The maximum size of each log file is 2 TB.

Leave a Reply