SQL Server Filegroups

Author by Nick Adams

Since SQL Server stores data in a model, that data needs to clearly go somewhere but where does it go?  This is where Filegroups come into play in SQL Server.  Filegroups are the physical files on your disc volumes that hold SQL Server’s data and can be used for backup and administrative management purposes.

The first thing to know are what types of files SQL Server uses:

  • Primary Data Files
  • Secondary Data Files
  • Log Files

 

The Primary Data File is the data file that is the initial data file holding data in SQL Server and points to other files within your database.  Primary Data Files have a .mdf extension on them.

Secondary Data Files are data files that hold all data that do not belong in the Primary Data File.  Data that doesn’t go into the Primary Data File is told to do so and set in a setting in SQL Server.  Secondary Files are optional.  These files should have a .ndf extension.

Log Files hold all logging actions in SQL Server.  Each database must have at least one logging file.  These files should have a .ldf extension.

 

To create a filegroup you must have a database first.  Once your database has been created you can create a filegroup and its files.

To create a new database, use the following TSQL:

USE MASTER
GO

CREATE DATABASE NewDB
 

Once the database code has been written, use the following code to specify your Primary Filegroup:

ON PRIMARY
(Name = ‘NewDB_Primary’,
-- Specify your file path to your instance folder and name your file
FILENAME = ‘E:\SQL Server 2014\Microsoft SQL Server (x86)\Files\NewDB_Primary.mdf’,
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB),

 

The next step in your code will be for your Secondary:

FILEGROUP NewDB_Secondary
(NAME = ‘NewDB_Secondary’
--As with your Primary, Specify your instance path and name your Secondary File
FILENAME = ‘E:\SQL Server 2014\Microsoft SQL Server (x86)\Files\NewDB_Secondary.ndf’,
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB),

 

Next, you will need a Log File:

LOG ON
(NAME = ‘NewDB_Logging’,
--As with your Primary and Secondary, specify your instance path and name your Log File
FILENAME = ‘E:\SQL Server 2014\Microsoft SQL Server (x86)\Files\NewDB_Logging.ldf’
SIZE = 1MB,
MAXSIZE = 20MB,
FILEGROWTH = 1MB),

 

Now, specify the new Filegroup as your default for the database using the following code:

ALTER DATABASE NewDB
MODIFY FILEGROUP NewDB_Primary DEFAULT

GO

 

You are now ready to start creating user defined tables on your database using the newly created Filegroups.  Your full code should look like this:

USE MASTER
GO

CREATE DATABASE NewDB

ON PRIMARY
(Name = ‘NewDB_Primary’,
FILENAME = ‘E:\SQL Server 2014\Microsoft SQL Server (x86)\Files\NewDB_Primary.mdf’,
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB),

FILEGROUP NewDB_Secondary
(NAME = ‘NewDB_Secondary’
FILENAME = ‘E:\SQL Server 2014\Microsoft SQL Server (x86)\Files\NewDB_Secondary.ndf’,
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB),

LOG ON
(NAME = ‘NewDB_Logging’,
FILENAME = ‘E:\SQL Server 2014\Microsoft SQL Server (x86)\Files\NewDB_Logging.ldf’
SIZE = 1MB,
MAXSIZE = 20MB,
FILEGROWTH = 1MB),

ALTER DATABASE NewDB
MODIFY FILEGROUP NewDB_Primary DEFAULT

GO

 

While this example puts all files on a single disc, you can specify individual paths per file and you have the capability to put Primary, Secondary, and Log files on different discs and locations so you can create a unique backup and infrastructure strategy.  Since Secondary Files are optional, you can have data stored on these files using RAID so you have redundancy and robust infrastructure for integrity.  Backup strategies will have a definite impact on how you create your file groups.  Since your Filegroups are also just files, you can use these files to import data to other instances or databases.

In the end, how you create your Filegroup is based upon how you want to store your data where.  To conclude, this visual example is how you can possibly arrange your Filegroup on different discs to have a strong infrastructure.

FIlegroup-structure-screenshot.jpg