Using Files and file groups

SQL Server maps a database over a set of operating-system files. Data and log information are never mixed on the same file, and individual files are used only by one database. File groups are named collections of files and are used to simplify data placement and administrative tasks such as backup and restore operations.To gain more information please follow this link  Files and File groups Architecture.

At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in file groups for allocation and administration purposes.
 

 Database Files

SQL Server databases have three types of files, as shown in the following table. 

File	Description
Primary	The primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or in secondary data files. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.
Secondary	Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow. The recommended file name extension for secondary data files is .ndf.
Transaction Log	The transaction log files hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.

For example, a simple database named Sales can be created that includes one primary file that contains all data and objects and a log file that contains the transaction log information. Alternatively, a more complex database named Orders can be created that includes one primary file and five secondary files. The data and objects within the database spread across all six files, and the four log files contain the transaction log information.

By default, the data and transaction logs are put on the same drive and path. This is done to handle single-disk systems. However, this may not be optimal for production environments. We recommend that you put data and log files on separate disks.

File groups

Every database has a primary filegroup. This filegroup contains the primary data file and any secondary files that are not put into other file groups. User-defined file groups can be created to group data files together for administrative, data allocation, and placement purposes.

For example, three files, Data1.ndf, Data2.ndf, and Data3.ndf, can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1. Queries for data from the table will be spread across the three disks; this will improve performance. The same performance improvement can be accomplished by using a single file created on a RAID (redundant array of independent disks) stripe set. However, files and file groups let you easily add new files to new disks.

All data files are stored in the file groups listed in the following table.

Filegroup	Description
Primary	The filegroup that contains the primary file. All system tables are allocated to the primary filegroup.
User-defined	Any filegroup that is specifically created by the user when the user first creates or later modifies the database.

Default Filegroup

When objects are created in the database without specifying which filegroup they belong to, they are assigned to the default filegroup. At any time, exactly one filegroup is designated as the default filegroup. The files in the default filegroup must be large enough to hold any new objects not allocated to other file groups.

The PRIMARY filegroup is the default filegroup unless it is changed by using the ALTER DATABASE statement. Allocation for the system objects and tables remains within the PRIMARY filegroup, not the new default filegroup. Below is an example of how add a user defined filegroup with two secondary files on it.By using an ALTER DATABASE statement we can make the user defined filegroup the default. A table is created on the specified user defined filegroup.

USE master;
GO
CREATE DATABASE DBTEST
ON PRIMARY
  ( NAME='DBTEST_Primary',
    FILENAME=
       'E:\Dbtest\DBTEST.mdf',
    SIZE=4MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
FILEGROUP DBTEST_FG1
  ( NAME = 'DBTEST_FG1_Dat1',
    FILENAME =
       'E:\Dbtest\DBTEST_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
  ( NAME = 'DBTEST_FG1_Dat2',
    FILENAME =
       'E:\Dbtest\DBTEST_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB)
LOG ON
  ( NAME='DBTEST_log',
    FILENAME =
       'E:\Dbtest\DBTEST.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO
ALTER DATABASE DBTEST
  MODIFY FILEGROUP DBTEST_FG1 DEFAULT;
GO

USE DBTEST;
CREATE TABLE TestTable
  ( id int PRIMARY KEY,
    name  char(8) )
ON DBTEST_FG1;
GO

File and File group Fill Strategy:

File groups use a proportional fill strategy across all the files within each file group. As data is written to the file group, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the file group, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free; one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.

As soon as all the files in a file group are full, the Database Engine automatically expands one file at a time in a round-robin manner to allow for more data, provided that the database is set to grow automatically. For example, a file group is made up of three files, all set to automatically grow. When space in all the files in the file group is exhausted, only the first file is expanded. When the first file is full and no more data can be written to the file group, the second file is expanded. When the second file is full and no more data can be written to the file group, the third file is expanded. If the third file becomes full and no more data can be written to the file group, the first file is expanded again, and so on.

 Improving Database Performance:

Using files and file groups improves database performance, because it lets a database be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that is made up of three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can access the data in parallel at the same time. This speeds up database operations. To get more information about hardware solutions, see Database Performance.

Additionally, files and file groups enable data placement, because a table can be created in a specific file group. This improves performance, because all I/O for a specific table can be directed at a specific disk. For example, a heavily used table can be put on one file in one file group, located on one disk, and the other less heavily accessed tables in the database can be put on the other files in another file group, located on a second disk.

 Implementing Backup and Restore Strategies

Databases made up of multiple file groups can be restored in stages by a process known as piecemeal restore. Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model. The piecemeal restore scenario includes all three phases of restore: data copy, redo or roll forward, and undo or roll back. To get more information please, see Performing Piecemeal Restores.

When multiple file groups are used, the files in a database can be backed up and restored individually. Under the simple recovery model, file backups are allowed only for read-only files. Using file backups can increase the speed of recovery by letting you restore only damaged files without restoring the rest of the database. For example, if a database is made up of several files physically located on different disks and one disk fails, only the file on the failed disk has to be restored. Please find the link for more information, see BACKUP (Transact-SQL).

Rules for Designing Files and File groups:

   The following rules pertain to files and file groups:

• A file or file group cannot be used by more than one database. For example, file sales.mdf and sales.ndf, which contain data and objects from the sales database, cannot be used by any other database.
• A file can be a member of only one file group.
• Transaction log files are never part of any file groups.

Recommendations:

Following are some general recommendations when you are working with files and file groups:

• Most databases will work well with a single data file and a single transaction log file.
• If you use multiple files, create a second file group for the additional file and make that file group the default file group. In this way, the primary file will contain only system tables and objects.
• To maximize performance, create files or file groups on as many different available local physical disks as possible. Put objects that compete heavily for space in different file groups.
• Use file groups to enable placement of objects on specific physical disks.
• Put different tables used in the same join queries in different file groups. This will improve performance, because of parallel disk I/O searching for joined data.
• Put heavily accessed tables and the nonclustered indexes that belong to those tables on different file groups. This will improve performance, because of parallel I/O if the files are located on different physical disks.
• Do not put the transaction log file or files on the same physical disk that has the other files and file groups.