SQL Server: File and Filegroups Architecture

-   SQL Server maps a database over a set of operating-system files.
-   Data and log information are never mixed in the same file, and individual files are used only by one database.
-   Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.

---------------------------------------- Database Files ----------------------------------------------

SQL Server databases have three types of files:

Primary data files 

-   The primary data file is the starting point of the database and points to the other files in the database. 

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

Secondary data files 

-   Secondary data files make up all the data files, other than the primary data file. 

-   Some databases may not have any secondary data files, while others have several secondary data files. 

-   The recommended file name extension for secondary data files is .ndf. 

Log files

-   Log files hold all the log information that is used to recover the database. 

-   There must be at least one log file for each database, although there can be more than one. 

-   The recommended file name extension for log files is .ldf. 

--------------------------------Logical and Physical File Names----------------------------

SQL Server files have two names:

logical_file_name

-   The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. 

-   The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name

-   The os_file_name is the name of the physical file including the directory path. 

-   It must follow the rules for the operating system file names.

SQL Server data and log files can be put on either FAT or NTFS file systems. We recommend using the NTFS file system because the security aspects of NTFS. Read/write data filegroups and log files cannot be placed on an NTFS compressed file system. Only read-only databases and read-only secondary filegroups can be put on an NTFS compressed file system.

--------------------------------------------- File Size ------------------------------------------------

-   SQL Server files can grow automatically from their originally specified size. 

-   When you define a file, you can specify a specific growth increment. Every time the file is filled, it increases its size by the growth increment. If there are multiple files in a filegroup, they will not autogrow until all the files are full. Growth then occurs in a round-robin fashion.

-   Each file can also have a maximum size specified. If a maximum size is not specified, the file can continue to grow until it has used all available space on the disk. 

Database Snapshot Files

The form of file that is used by a database snapshot to store its copy-on-write data depends on whether the snapshot is created by a user or used internally:

• A database snapshot that is created by a user stores its data in one or more sparse files. Sparse file technology is a feature of the NTFS file system. At first, a sparse file contains no user data, and disk space for user data has not been allocated to the sparse file.
  
  
• Database snapshots are used internally by certain DBCC commands. These commands include DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKFILEGROUP. An internal database snapshot uses sparse alternate data streams of the original database files. Like sparse files, alternate data streams are a feature of the NTFS file system. The use of sparse alternate data streams allows for multiple data allocations to be associated with a single file or folder without affecting the file size or volume statistics.

------------------------------------ Database Filegroups -----------------------------------------

Database objects and files can be grouped together in filegroups for allocation and administration purposes. There are two types of filegroups:

Primary

-   The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. 

-   All pages for the system tables are allocated in the primary filegroup.

User-defined

-   User-defined filegroups are any filegroups that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

-   Log files are never part of a filegroup. Log space is managed separately from data space.

-   No file can be a member of more than one filegroup. 

-   Tables, indexes, and large object data can be associated with a specified filegroup. In this case, all their pages will be allocated in that filegroup, or the tables and indexes can be partitioned. The data of partitioned tables and indexes is divided into units each of which can be placed in a separate filegroup in a database. 

-   One filegroup in each database is designated the default filegroup. When a table or index is created without specifying a filegroup, it is assumed all pages will be allocated from the default filegroup. 

-  Only one filegroup at a time can be the default filegroup. 

-   Members of the db_owner fixed database role can switch the default filegroup from one filegroup to another. If no default filegroup is specified, the primary filegroup is the default filegroup.

File and Filegroup Example

The following example creates a database on an instance of SQL Server. The database has a primary data file, a user-defined filegroup, and a log file. The primary data file is in the primary filegroup and the user-defined filegroup has two secondary data files. An ALTER DATABASE statement makes the user-defined filegroup the default. A table is then created specifying the user-defined filegroup.

USE master;

GO

-- Create the database with the default data

-- filegroup and a log file. Specify the

-- growth increment and the max size for the

-- primary data file.

CREATE DATABASE MyDB

ON PRIMARY

  ( NAME='MyDB_Primary',

    FILENAME=

       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_Prm.mdf',

    SIZE=4MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB),

FILEGROUP MyDB_FG1

  ( NAME = 'MyDB_FG1_Dat1',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_1.ndf',

    SIZE = 1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB),

  ( NAME = 'MyDB_FG1_Dat2',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_2.ndf',

    SIZE = 1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB)

LOG ON

  ( NAME='MyDB_log',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB.ldf',

    SIZE=1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB);

GO

ALTER DATABASE MyDB 

  MODIFY FILEGROUP MyDB_FG1 DEFAULT;

GO

-- Create a table in the user-defined filegroup.

USE MyDB;

CREATE TABLE MyTable

  ( cola int PRIMARY KEY,

    colb char(8) )

ON MyDB_FG1;

GO