MS SQL Server 2012 - DBA Articles

Database Files and Filegroups of SQL Server 2012

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 filegroups 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.
More Reference URL: data-e-education.com/DBA/Database_03.php
Alternatively, a more complex database named Sales_Data_Mart can be created that includes one primary file and four secondary files. The data and objects within the database spread across all five files and a log file that contains the transaction log information.
More Reference URL: data-e-education.com/DBA/Database_04.php
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.
Filegroups
Primary Filegroup
Every database has a PRIMARY Filegroup. This PRIMARY Filegroup contains System Files, Main Data File, Transaction Log File and optionally Secondary Data Files. When objects are created in the database without specifying which filegroup they belong to, they are assigned to the default filegroup.
More Reference URL: data-e-education.com/DBA/Database_03.php
User-defined Filegroups
User-defined Filegroups can be created to group data files together for administrative, data allocation, and placement purposes.
For example, four files, Sales_North.ndf, Sales_East.ndf, Sales_West.ndf and Sales_South.ndf, can be created on four disk drives, respectively, and assigned to the filegroup Sales_Data_Mart. A table can then be created specifically on the filegroup Sales_Data_Mart. Queries for data from the table will be spread across the four 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 filegroups let you easily add new files to new disks.
More Reference URL: data-e-education.com/DBA/Database_04.php
More Reference URL: http://msdn.microsoft.com/en-us/library/ms189563%28v=sql.110%29.aspx

* * * * *

Your Comment To AUTHOR