STORAGE ARCHITECTURE & DATA FILE ARCHITECTURE OF MS SQL SERVER

 

STORAGE ARCHITECTURE & DATA FILE ARCHITECTURE:

File groups:

• The database files can be grouped in file groups for allocation and administration.
• No file can be a member of more than one group of files.
• The log files are never part of a group of files, log space is managed separately from data space.
• Files in a file group will not be autoGrow unless there is no space available on any of the files in the file group.
• Two types of file groups are there in SQL Server 1. Primary 2. User Defined.
• primary filegroup contains the primary data file and any other files not specifically assigned to another group of files.
• All pages for the system tables are allocated in the primary filegroup.
• Files user-defined groups are groups of files specified using the keyword filegroup in create database or alter database.
• A group of files in each database runs as the default file group.
• When SQL Server allocates a page to a table or index for which no filegroup is specified when they are created, pages are allocated from default filegroup.
• To change the default file group of a group of files to another file group must have db_owner fixed role db.
• By default primary filegroup is the default file group.
• User should have db_owner fixed db role to take backup of files and file groups individually.

Files:

• The databases have three types of files 1. Primary 2. Secondary datafile datafile file 3. Log
• primary datafile is the point of the database and points to other files starting in the database.
• Each database has one primary data file.
• We can give any extension to the primary data file, but the recommended extension is .mdf.
• secondary datafile is a file other than the primary data file in the database.
• Some databases can have multiple secondary data files.
• Some databases may not have a single secondary data file.
• Extension recommended for secondary datafile is .ndf, but we can give as our wish.
• Logfiles hold all the log information used to recover the database.
• Database must have at least a logfile.
• We can have several log files to a database.
• The recommended extension for logfile is .ldf, but we can change.
• Recommended Extension files to help identify use of the file.
• The location of all files in a database are stored in both master database and the main file of the database.
• when restoring master database
• Files with names 1. 2. two physical logic
• Logical name is used to refer to the file in all T-SQL statements
• Physical name is os_file_name, it must follow the rules of the OS
• The data files and logs can be placed on FAT or NTFS file systems, but can not be placed on compressed file systems
• We can have upto 32,767 files in one database.

Extents:

• Extents are the basic unit in which space is allocated to tables and indexes.
• A measure is 8 contiguous pages or 64 KB.
• SQL Server has two types of extents 1.Uniform 2.Mixed.
• uniform extents consist of only one object.
• Mixed extents are shared by up to eight objects.

Pages:

• It is the basic unit of data storage in MS SQL Server
• The page size is 8 kb
• The beginning of each page is 96 byte header used for information storage system such as the page type, amount of free space on the page and the object id of the object with the page

9 types of data pages are there in SQL Server

1. data --- data lines with all data except text, ntext and image data
2. Index --- Index entries
3. Test --- picture text, picture and data ntext
4. GAM --- the assigned extensions Information
5. SGAM --- Extensions Information allocated at system level
6. Free Space page (PFS) --- Information about free space on pages
7. Allocation Index map (IAM) --- Information extensions used by a table or index
8. Bulk changed the map (BCM) --- Information extensions modified by bulk operations since the declaration of last backup log
9. Differential Changed Map (DCM) --- Information on extensions that have changed since the last backup database statement

• The log files do not contain pages, but they contain a series of log records.
• offset table line starts at the end of the page.
• Row offset table contains one entry for each line on the page and each entry records how far the first byte of the line since the beginning of the page
• The inputs of the line offset table are in reverse order of the sequence of lines on the page