locked
Partition, Filegroup, and File Management RRS feed

  • Question

  • In our UAT environment, we have one disk drive.  In Production for a large client, we have numerous drives. 

    The DBPro project has one filegroup definition and 100's of tables reference it. 

    What is the best way to have multiple filegroup definitions (in the same project) for the Production configuration?  The hundreds of table definitions would ideally dynamically change to reference the appropriate filegroup for the prod config.  Make sense? 

    Saturday, April 16, 2011 3:16 PM

Answers

  • I do not see any easy way to do this, since the assignment of the object to the filegroup is part of the object defiintion.

     

    For example production you would want to say: T1 -> FG1 -> File1 (D:drive) and T2 -> FG2 -> File2 (E:drive) ?

     

    I see some problems with this approach:

    1. I personally do not believe in micro managing storage through filegroups this way. Inthe  many years of SQL Server development I have used file groups for "types" of data, where this is a generic grouping, mostly I found separation of blob, index and data structure (to drive parallel IO), or separation of load area and/or reporting data from transactional data, however these are high-level generic groupings which apply to all instances of your database. (As a side note: I also found when people use a SANs micro management of object placement and files / filegroups works against the distribution of load inside the SAN, while you are better of utilizing the economies of scale of the SAN, which will definitely be true in hosted database environments like SQL Azure, where you will simply not get this kind of control.) 
    2. What we do is therefore have the filegroups present in both definitions, UAT and production only the file settings are different.
    3. In UAT I most often use the ability to create file group without a file, so it is just a logical marker, nothing else, that way I can maintain a single source definition for the object definition, and only change the file definitions, which are different per installation in terms of locations, number of files, size etc.

     

    Hope that gives you some ideas,


    GertD @ www.DBProj.com
    Sunday, April 17, 2011 7:29 PM

All replies

  • I do not see any easy way to do this, since the assignment of the object to the filegroup is part of the object defiintion.

     

    For example production you would want to say: T1 -> FG1 -> File1 (D:drive) and T2 -> FG2 -> File2 (E:drive) ?

     

    I see some problems with this approach:

    1. I personally do not believe in micro managing storage through filegroups this way. Inthe  many years of SQL Server development I have used file groups for "types" of data, where this is a generic grouping, mostly I found separation of blob, index and data structure (to drive parallel IO), or separation of load area and/or reporting data from transactional data, however these are high-level generic groupings which apply to all instances of your database. (As a side note: I also found when people use a SANs micro management of object placement and files / filegroups works against the distribution of load inside the SAN, while you are better of utilizing the economies of scale of the SAN, which will definitely be true in hosted database environments like SQL Azure, where you will simply not get this kind of control.) 
    2. What we do is therefore have the filegroups present in both definitions, UAT and production only the file settings are different.
    3. In UAT I most often use the ability to create file group without a file, so it is just a logical marker, nothing else, that way I can maintain a single source definition for the object definition, and only change the file definitions, which are different per installation in terms of locations, number of files, size etc.

     

    Hope that gives you some ideas,


    GertD @ www.DBProj.com
    Sunday, April 17, 2011 7:29 PM
  • Hello DaveIII,

    I have marked Gert's reply as answer. If you found it does not help you, please feel free to unmark it and let me know.

    Thanks,


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 25, 2011 6:43 AM
    Moderator
  • Thank you for the useful feedback.  Just want to confirm two things...

    1.  Generally speaking - with a SAN - is the following design reasonable? 

    TData => FGData => File (D:drive)

    TIndex => FGIndex = File (E:drive)

    Log => FGLog => File (F:drive)

    2.  In terms of your point #3, are you suggesting the FileGroup definition look something like:

    ALTER DATABASE [$(DatabaseName)]
        ADD FILEGROUP [DATA];

    and then the file definition would look something like:

    ALTER DATABASE [$(DatabaseName)]
        ADD FILE (NAME = [Data0], FILENAME = '$(DataDrive)Data\$(DatabaseName)Data.ndf', FILEGROWTH = 10 %) TO FILEGROUP [Data];

    Then, the corresponding Database.sqlcmdvars would be chosen based on the configuration type? 

     

     

    Thursday, April 28, 2011 11:26 PM