Database Filegroups: Just Like Seatbelts But With Less Chafing

Years ago I left school after getting my graduate degree in Mathematics and got myself a real job, you know the one that had things like a pension and health insurance. Since it was only the cost of a co-pay I decided to visit a doctor to have a physical done because, well, isn’t that a good idea? When the doctor asked why I was there I told him that I thought to have a routine physical was just the right thing to do.

He gave me the physical and we talked over the results. Then he asked a very simple question:

“Tom, do you use a seatbelt?”

I thought the question odd. In our state (Massachusetts) the use of seatbelts was not made mandatory until after I had already been driving for many years. Wearing a seatbelt was not a habit for me. I simply said “no”.

He smiled a bit. Not a laughing kind of smile. More like a smile when you can’t understand why someone would be so stupid. It’s the same smile I give to folks who call me and say they need to recover all their data but don’t have any backups.

He then said, “You are young and in great health. At your age, the number one cause of death is a car accident, most likely from failure to wear a seatbelt. You should consider making that a habit and looking forward to a long life ahead.”

I started wearing my seatbelt that day.

Now, let’s pretend I was your “database doctor” and had a look at your database servers. I bet I can ask you a simple question: “Are you using filegroups for your databases?”

I bet the answer is “no”. That’s because filegroups are like seatbelts. They are right there, easy to use, and most people ignore them.

Filegroups help you in so many ways and are easy to use but hardly anyone is using them. I rarely come across a customer or client that has deployed filegroups. When I ask about them the response is usually “yeah, we know about them, but we don’t see why they are useful.”

Just like a seatbelt, you don’t know why it is useful until you need it, and then you’ll use it all the time.

 

Buckle Up: Using Filegroups For Safety

SQL Server allows for what is called “piecemeal restores”. The name is fairly descriptive; a piecemeal restore means you are able to restore pieces of your database. It is not necessary to have your entire database online and available. You have the ability to have only a portion of your database available for user connections. There is just one catch, however: you need to be using more than one filegroup.

Using multiple filegroups allows for a lot of benefits:

1. Separating out internal system data from user data
2. Placing larger tables/indexes on their own filegroup and/or dedicated set of disks for performance
3. Placing archive (or even read-only) data onto their own filegroups and dedicated set of disks to reduce maintenance overhead
4. Ability to bring parts of the database online quickly, no need to wait for a full restore

I recently had a client discuss their desire to reduce their database backup times. They had a 10TB database that was taking about 12 hours to do a full backup. The first thing I asked about was their use of filegroups along with their current archive strategy. About 80% of their data was data that would not be changing (or should not be changing).

By moving that data onto a dedicated filegroup they would reduce their backup time to less than two hours because they could stop taking weekly backups of data that was never changing. They could also mark the entire filegroup as read-only. This all would result in less time for backups as well as general maintenance.

 

Creating New Databases With Multiple Filegroups Defined

Besides my doctor reminding me about the use of seatbelts I had another reason to start using them: it’s the law. That got me thinking about filegroups and how they aren’t mandatory.

For example, you can create a new database in SQL Server Management Studio (SSMS) using this syntax:

CREATE DATABASE foo
 GO

You end up with a database created that has one database file, tied to one filegroup. That filegroup serves a dual purpose as both the default filegroup (so, all new objects get created as part of that filegroup) and the primary filegroup (where all the system objects are stored). Just as you can drive your car while not wearing your seatbelt you can create a database while not utilizing multiple filegroups.

I can understand that Microsoft would not require the use of additional filegroups. However, I would prefer that when creating a new database they prompted you with a syntax similar to the following:

CREATE DATABASE [foo]
 ON PRIMARY
 ( NAME = N'foo' , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.JAMBON\MSSQL\DATA\foo.mdf'),
 FILEGROUP [USER]
 ( NAME = N'foo_user' , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.JAMBON\MSSQL\DATA\foo_user.ndf')
 LOG ON
 ( NAME = N'foo_log' , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.JAMBON\MSSQL\DATA\foo_log.ldf')
 GO
 USE [foo]
 GO
 IF NOT EXISTS
 ( SELECT name
 FROM sys.filegroups
 WHERE is_default = 1 AND name = N'USER' )
 ALTER DATABASE [foo] MODIFY FILEGROUP [USER] DEFAULT
 GO

So, any new table or object created will get assigned to the USER filegroup, while the system objects will remain tied to the PRIMARY filegroup. using this syntax by default would be a great step forward towards helping administrators to understand and leverage the use of multiple filegroups.

Additional filegroups does result in some additional administrative overhead. For example, if you are using filegroups you will likely want to utilize file backups as part of your regular database backup strategy. You can read more about backing up database files at this MSDN entry, which also has a nice graphic to help you visualize this concept of database file backups.

 

Summary

The short of it is this: filegroups will save your ass, but only if you know enough to use them in advance.

I’d say that filegroups are one aspect of SQL Server that has nothing but upside. They are easy enough to implement, they allow for you to have greater flexibility when it comes to recovering data, and have a minimal amount of administrative overhead. They may not save your life, but there is a good chance they could at least save your job.

And they won’t wrinkle your clothes or chafe your neckline.

So why aren’t you using them?