sql server dba Filegroups

AAFiles:
There 3 types of file are there.
1. .MDF [(Master Data File, Main Data File, Primary Data File]
2. .LDF
3. .NDF


1. Primary data file (.MDF)
--Primary data files The primary data file is the starting point of the database
--Also primary data file contains all other files in the database.


-- Every database has one primary data file. The recommended file name extension for primary data files is .mdf.






2. Secondary data file (.NDF):[New Data File, Next Data File, Secondary Data File]


--If primary database file (.MDF) is full then we add secondary data file(.NDF) into the database.


--Multiple secondary files we can add and recommnded extension of the file is .NDF.




NOTE:
1.Data stores permently in either MDF or NDF but not in .LDF.


2. MDF or NDF file max limitation is at 16 TB.










3. Log data file[ .LDF]:


Main purpose of the log file is to recover the transactons in the database and the extension is always .LDF.


when ever we do any transactions ..every transaction should store or logged into the transacion log..This cancept is called as WAL [Write a head logging]








Note: PER DATABASE WE CAN BE ABLE TO CREATE 32767 FILE(.MDF,.NDF,.LDF)
Data file size: [.MDF or .NDF] –16 TB
LOG FILE: .LDF –2TB




SYSETM DATABASE : MASTER ,MODEL AND RESOURCE DATABASES WE CAN NOT BE ABLE TO ADD OVERFLOW OR SECONDARY .NDF OR .LDF FILES.
WHERE AS MSDB AND TEMPDB WE CAN ADD THE FILES(.NDF OR .LDF)




-----------------------------------------------
File groups:Collection of Files (MDF, NDF).


Benefits:
1) Ease of Administration
2) Filegroup Backups
3) Performance Benefit




NOte:
1. Per database we can add max 32767 file groups . A filegroup can have maximum 32767 files. Per database


2. .MDF file is always belongs to PRIMARY FILE GROUP By default.


3. LDF is never belongs to any file group.