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:
- Effective Backup Restore strategy.
- Provides a good way to physically separate data (System tables, User Tables & Indexes) with different access needs.
- 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
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
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
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
Yes , you are right . You can get both the advantages.
For example :- Your database object A is assigned to one file group , all the read write operation to that particular object will be on that particular file group that is assigned to that object.
Hence, in this case SQL engine will not even look for other file groups. So of course your performance will be better.