SQL Server: When to create Multiple Files & Multiple Filegroups for a Database?

SQL Server maps a database over a set of operating-system files. Data and log information are never mixed in the same file, and individual files are used only by one database. One DB can have multiple filegroups and\or multiple files in a filegroup. The use of Filegroup is limited to Data Files only. Database objects and files can be grouped together in filegroups for allocation and administration purposes. Now before making changes to files or filegroups you should have a clear understanding of each of them.

Let’s discuss them one by one:

Why to use Multiple Files in a Single Filegroup

Multiple files are created in a single filegroup to achieve better performance provided you place each file on a different disk, since SQL Server uses proportional fill method the data will get split and then written to Files.

Note: In most cases, using the striping capabilities of RAID provides much of the same performance gain.

Why to use Multiple Filegroups in a DB

Multiple Filegroups are created to have better Data Placement Control.

There are few other benefits also like:

  1. Effective Backup Restore strategy.
  2. Provides a good way to physically separate data (System tables, User Tables & Indexes) with different access needs.
  3. Provide manageability and some performance benefits.

Hope this will help you.

 

Regards

Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

3 Comments on “SQL Server: When to create Multiple Files & Multiple Filegroups for a Database?”

  1. Hello Sarabpreet,

    Nice article. I have one query. My database has one additional filegroup along with primary file group. Can I back up this filegroup and restore in new database.I do not want to back up and restore full back up. In short I want tables in additional filegroup in a seperate database. If I copy tables one by one, it is slow because my each table has millions of rows.

    Thanks in advance,

    Naveen

  2. Hi Naveen,

    Yes it is possible to backup a single filegroup. But if you want to restore only 1 filegroup that is not possible until and unless that filegroup is Primary (default) File group.

    The question you asked is “can i restore in a seperate database?” The answer is NO.

    Let me explain you with the help of an example:

    If your database has 3 filegroups Primary, FG1, FG2. Now you want to restore FG2 on a diff server you can achieve this but for this you need to restore Primary Filegroup first and then initiate restore of FG2. You can restore this only if you are restoring the filegroups as a new database but if you’ll try to restore a Filegroup from databaseA to databaseB that is not at all possible.

    To get more info read Piecemeal Restore of Only Some Filegroups. With example.

    Thanks,

    Sarabpreet Singh

  3. Hi Anand,

    Thanks for the post about Multiple Files & Multiple FileGroups. I have a question.

    If we create Multiple Data Files one Multiple Filegroups that is spanning around 4 data drives, in this case whether we can gain both the advantages ?

    i.e. performance gain due to Multiple Data files on various drives and keeping it on various filegroups, so that better Data placement control ?

    I have asked a similar question in the following thread, can you please shed some light in this regard, thanks in advance.

    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/50d2e592-9bf1-42fb-9e53-27792a4597d4

Leave a Reply

Your email address will not be published. Required fields are marked *