Using Database Filegroups | Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

You can group data files for a database into filegroups for allocation and administration purposes. In some cases, you can improve performance by controlling the placement of data and indexes into specific filegroups on specific disk drives. The filegroup containing the primary data file is called the primary filegroup. There is only one primary filegroup, and if you don't specifically ask to place files in other filegroups when you create your database, all of your data files will be in the primary filegroup.

In addition to the primary filegroup, a database can have one or more user-defined filegroups. You can create user-defined filegroups by using the FILEGROUP keyword in the CREATE DATABASE or ALTER DATABASE statement.

Don't confuse the primary filegroup and the primary file:

The primary file is always the first file listed when you create a database, and it typically has the file extension .mdf. The one special feature of the primary file is that it has pointers into a table in the master database called sysfiles1 that contains information about all the files belonging to the database.
The primary filegroup is always the filegroup that contains the primary file. This filegroup contains the primary data file and any files not put into another specific filegroup. All pages from system tables are always allocated from files in the primary filegroup.

The Default Filegroup

One filegroup always has the property of DEFAULT. Note that DEFAULT is a property of a filegroup, not a name. Only one filegroup in each database can be the default filegroup. By default, the primary filegroup is the also the default filegroup. A database owner can change which filegroup is the default by using the ALTER DATABASE statement. The default filegroup contains the pages for all tables and indexes that aren't placed in a specific filegroup.

Most SQL Server databases have a single data file in one (default) filegroup. In fact, most users will probably never know enough about how SQL Server works to know what a filegroup is. As a user acquires greater database sophistication, she might decide to use multiple devices to spread out the I/O for a database. The easiest way to do this is to create a database file on a RAID device. Still, there would be no need to use filegroups. At the next level of sophistication and complexity, the user might decide that she really wants multiple filesperhaps to create a database that uses more space than is available on a single drive. In this case, she still doesn't need filegroupsshe can accomplish her goals using CREATE DATABASE with a list of files on separate drives.

More sophisticated database administrators might decide to have different tables assigned to different drives or to use the table and index partitioning feature in SQL Server 2005. Only then will they need to use filegroups. They can then use Object Explorer in SQL Server Management Studio to create the database on multiple filegroups. Then they can right-click on the database name in Object Explore and create a script of the CREATE DATABASE command that includes all the files in their appropriate filegroups. They can save and reuse this script when they need to re-create the database or build a similar database.

Why Use Multiple Files?

You might wonder why you would want to create a database on multiple files located on one physical drive. There's usually no performance benefit in doing so, but it gives you added flexibility in two important ways.

First, if you need to restore a database from a backup because of a disk crash, the new database must contain the same number of files as the original. For example, if your original database consisted of one large 12-GB file, you would need to restore it to a database with one file of that size. If you don't have another 12-GB drive immediately available, you cannot restore the database! If, however, you originally created the database on several smaller files, you have added flexibility during a restoration. You might be more likely to have several 4-GB drives available than one large 12-GB drive.

Second, spreading the database onto multiple files, even on the same drive, gives you the flexibility of easily moving the database onto separate drives if you modify your hardware configuration in the future.

Objects that have space allocated to them, namely tables and indexes, are created on a particular filegroup. If the filegroup is not specified, they are created on the default filegroup. When you add space to objects stored in a particular filegroup, the data is stored in a proportional fill manner, which means that if you have one file in a filegroup with twice as much free space as another, the first file will have two extents (or units of space) allocated from it for each extent allocated from the second file. I'll discuss extents in more detail later in this chapter.

You can also use filegroups to allow backups of parts of the database. Because a table is created on a single filegroup, you can choose to back up just a certain set of critical tables by backing up the filegroups in which you placed those tables. You can also restore individual files or filegroups in two ways. First, you can do a partial restore of a database and restore only a subset of filegroups, which must always include the primary filegroup. The database will be online as soon as the primary filegroup has been restored, but only objects created on the restored filegroups will be available. Partial restore of just a subset of filegroups can be a solution to allow very large databases (VLDBs) to be available within a mandated time window. Alternatively, if you have a failure of a subset of the disks on which you created your database, you can restore backups of the filegroups on those disks on top of the existing database. This method of restoring also requires that you have log backups, so I'll discuss it in more detail in Chapter 5.

A FILEGROUP CREATION Example

This example creates a database named sales with three filegroups:

The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increment for both of these files is specified as 15 percent.
A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.

A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.

CREATE DATABASE Sales ON PRIMARY ( NAME = SPri1_dat, FILENAME =     'c:\program files\microsoft sql server\mssql.1\mssql\data\SPri1dat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), ( NAME = SPri2_dat, FILENAME =     'c:\program files\microsoft sql server\mssql.1\mssql\data\SPri2dat.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), FILEGROUP SalesGroup1 ( NAME = SGrp1Fi1_dat, FILENAME =     'c:\program files\microsoft sql server\mssql.1\mssql\data\SG1Fi1dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = SGrp1Fi2_dat, FILENAME =     'c:\program files\microsoft sql server\mssql.1\mssql\data\SG1Fi2dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), FILEGROUP SalesGroup2 ( NAME = SGrp2Fi1_dat, FILENAME =     'c:\program files\microsoft sql server\mssql.1\mssql\data\SG2Fi1dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = SGrp2Fi2_dat, FILENAME =     'c:\program files\microsoft sql server\mssql.1\mssql\data\SG2Fi2dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log', FILENAME =     'c:\program files\microsoft sql server\mssql.1\mssql\data\saleslog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB );