Basics Of Database Administration In SQL Server: Part 6

Article

Note: All screenshots are applied to SQL Server 2012 Enterprise Evaluation Edition.

SQL Server Storage Basics: Database Filegroups

In this part we will discuss some basic things about SQL Server File Groups. If you missed previous articles you can check them out from below link:

1. What is Filegroup

In SQL Server filegroup is a logical structure which contains objects like data file, tables and indexes. In other words we can say that a filegroup is a logical unit in which all database files are grouped together and simplifies database administration resulting into improved performance by controlling the placement of objects into specific filegroups on specific drive.

Figure 1: Pictorial representation of filegroups

2. Why Filegroups

Filegroups make administration easier for a DBA. Using multiple filegroups we can gain following benefits;

We can separate user data with internal system data using multiple filegroups.
We can overcome with maintenance overhead by putting archive (or even read-only) data onto their own filegroups and dedicated set of disks.
We can gain performance improvement by putting larger tables/indexes on their own filegroup and/or dedicated set of disks.
We can bring some parts of the database online quickly ( piecemeal restore ).

3. Types of Filegroups

There are two types of filegroups:

3.1. Primary Filegroup:

The filegroup which contains the primary data file and any other files that are not associated to another filegroup is termed as Primary filegroup.

3.2. User-defined Filegroup:

The Filegroups which we create from FILEGROUP keyword using CREATE DATABASE or ALTER DATABASE is termed as user-defined filegroups. This file is created by user or later modifies database by a user.

4. Filegroup Examples

I am using below query to create filegroup with new database:

CREATE DATABASE[FG]
ON PRIMARY
(NAME = N 'FG_data',
FILENAME = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG_data.mdf',
SIZE = 4096 KB, FILEGROWTH = 1024 KB)
LOG ON
(NAME = N 'FG_log',
FILENAME = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG_log.ldf',
SIZE = 1024 KB, FILEGROWTH = 10 % )
GO
ALTER DATABASE[FG] ADD FILEGROUP[FG2]
GO
ALTER DATABASE[FG] ADD FILEGROUP[FG3]
GO

Using above query we created database name ‘FG’ and primary filegroup which is the default filegroup and FG2 and FG3 two user-defined filegroups.

5. How to view filegroups

By executing below query we can view filegroups in a database:

use FG
go
select * from sys.filegroups
go

Figure 2: Viewing filegroups

6. Creating a file and assigning it to filegroup

To add file in a filegroup we execute ALTER DATABASE query.

ALTER DATABASE FG
ADD FILE
(NAME = FG3_data,
FILENAME = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG3_data.ndf')
TO FILEGROUP FG3

7. Changing default filegroup

Again ALTER DATABASE query will execute to change the default filegroup.

ALTER DATABASE FG
MODIFY FILEGROUP FG3 DEFAULT

Now check the default value for filegroup FG3 using sys.filegroups.

use FG
go
select * from sys.filegroups
go

Figure 3: Changing default filegroups

Now you can clearly see that the is_default value is 1 for FG3 filegroup. It means the default filegroup is FG3.

8. Filegroups Backup

We can take filegroups backup with two ways:

8.1. With SQL Server Management Studio (SSMS)
8.2. With T-SQL

8.1. With SQL Server Management Studio (SSMS):

To take backup of filegroups with ssms follow below steps:

Select database>>do right click.
Go to task >> click on backup,
Backup database window will appear, here choose option file and filegroups under backup component.
When you select file and filegroups another window will open which will show you all the filegroups for that database.
Click on check boxes to take backup of one or more filegroups according to your requirement.

Step 1 and step 2:

Figure 4: Showing how to reach backup option

Step3:

Figure 5: Choosing files and filegroups backup

Step 4 and step 5:

Figure 6: Selecting filegroups for backup

8.2. Filegroups backup with T-SQL:

You can execute below query to take backup of filegroups. In below example my database name is ‘FG’ and I am taking backup of all 3 filegroups i.e.

PRIMARY, FG2, FG3
BACKUP DATABASE[FG]
FILEGROUP = N 'PRIMARY',
FILEGROUP = N 'FG2',
FILEGROUP = N 'FG3'
TO
DISK = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\FG.bak'
WITH NOFORMAT, NOINIT, NAME = N 'FG-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Figure 7: Taking filegroups backup with t-sql

Points to remember:

All pages for the system tables are allocated in the primary filegroup.
Log files ( .ldf) does not use filegroups.
No file can be a member of more than one filegroup.

9. References:

Database file and filegroups. Technet Microsoft
Files and Filegroups Architecture. Technet Microsoft
Backup File and Filegroups. Technet Microsoft
Journey to SQL Authority with Pinal Dave
Microsoft SQL Server 2012 Internals By Kalen Delaney, Craig Freeman

10. Wrap Up

In this section we discussed some basic things and saw some examples how to perform the task about filegroups which is closely related with article Basics of Database Administration in SQL Server: Part 5.

We will discuss some other points about Database storage basics in upcoming articles.

For part 7 you can click on following link: Basics of Database Administration in SQL Server: Part 7

Keep sharing your knowledge, happy reading.