Post reply

Create database and change default filegroup

  • June 23, 2009 at 4:20 am

    #139193

    Hi there everyone,

    I read somewhere that when creating a DB. it's good practice to create another filegroup and make this the default. All tables, indexes etc should be created on this. This is suppose to:

    1. Seperate system files from user data

    2. Allow for piecemeal restores

    3. Bit more flexibility for backup / and restore

    Does the above sound right, is there other advantages for doing this? please correct me if i'm wrong. 🙂

    Cheers

  • June 23, 2009 at 5:29 am

    #1013841

    If you have a very active database you can certainly benefit from the fact that you have more filegroups.

    You're correct about the points you wrote in your post. Another advantage is that you can place your files on different disks what will make your I/O span over multiple disks.

    I would only admit to create multiple filegroups in the following cases:

    - You have a very active database that has a lot of writes

    - You want to seperate certain departments in your company like all the tables for sales en development.

    - You want to be able to restore files online

    - You want to archive old data to a different file by using partitioning.

    There are a few more benefits to it. You can use partitioning together with filegroups to split your data over multiple files. In many cases that increases your performance.

  • June 23, 2009 at 7:41 am

    #1013922

    In my experience, you really need separate physical disks to take advantage of filegroups from a performance perspective...

  • June 23, 2009 at 2:42 pm

    #1014264

    So does this mean it's best practice to setup each db in this way.

    Data_file1.mdf on primary

    Log_file on FG_UserData

    Data_file2.ndf on FG_UserData

    So there should be 3 files for every database?

    cheers

  • June 23, 2009 at 6:19 pm

    #1014358

    I think you will find most DBAs will use 2 files only (in their default filegroups) unless the database needs additional filegroups for performance (or other reasons).

    We have used a separate filegroup for indexes and tables that have intensive reads/writes to load balance IO, but this would not be the case for all databases.

  • June 24, 2009 at 12:01 pm

    #1014824

    Free,

    You can't specify a filegroup for a transaction log file.

    MJ

  • June 24, 2009 at 2:55 pm

    #1014948

    oops, yes - my typo.

  • June 24, 2009 at 11:57 pm

    #1015096

    If you want to use filegroups I would adviose you to also look at partitioning your tables and indexes because that can improve your performance dramatically.

    Look at this article to read more about partitioning: http://msdn.microsoft.com/nl-nl/library/ms345146(en-us,SQL.90).aspx

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply