Sql server Database architecture

Database is nothing but it is a logical collection of data, Data will be stored in two different sets of files DATA (.mdf and .ndf) files and LOG (.ldf) files which are database resources.

   

Let’s see when we are creating a database.

  

This stores the actual data, we can create multiple data files, and each data file will be 4 MB by default when we create a database. Max size of each data files is said to be 16 TB. And by default log size is 1 MB. By default it will increase 1MB and log file 10%. We can set the size of it also.

  

I have increase the size of the files

  

We can have multiple ndf files and log files in a database but only one, we can create mdf.

Adding ndf files in the database

For adding the ndf files click on the add

   

Here I am adding 2 ndf files.

  

Click ok. Now the database has been created with 1 mdf, 1 ldf and 2 ndf files.

We can create the database using below sql script also

CREATE DATABASE [Test]  CONTAINMENT = NONE  ON  PRIMARY ( NAME = N'Test', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test.mdf' , SIZE = 10240KB , FILEGROWTH = 10%), ( NAME = N'Test_ndf1', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_ndf1.ndf' , SIZE = 10240KB , FILEGROWTH = 10%), ( NAME = N'Test_ndf2', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_ndf2.ndf' , SIZE = 10240KB , FILEGROWTH = 10%)  LOG ON ( NAME = N'Test_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_log.ldf' , SIZE = 5120KB , FILEGROWTH = 10%)

Database has been created.

  

All file are stores by default on c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\

 We can change it also.

Four files are created

  

Data File:

A database will have 2 different data files

Ø  MDF (Master Data File)

Ø  NDF (User Data File)

MDF (Master Data File)

Ø  This is a default file we get when a database is created.

Ø  We cannot delete this file and also we cannot create one more MDF file, there will be one and only MDF file per database.

Ø  Without this file database will not be available.

Ø  This stores user data along with some METADATA (Data about Data/ Definition of data) about the database configurations like collation, recovery model, user’s information, roles…

Ø  File extension of this is .mdf

  

NDF: (User Data File)

Ø  This is a secondary data file; we can create multiple ndf files with any extension.

Ø  It is not necessary to have a secondary data file. Therefore some databases may not have any secondary data file. But it is also possible to have multiple secondary data files for a single database.

Ø  Data files can be grouped with filegroups

Ø  It’s also possible to store the secondary data file in a separate physical drive than the one which primary data file is stored. 

 Log File:

This file store all the transactional (insert, update create and delete. Except select) logs in sequential order, each transaction will be given a unique number call 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.