Post reply

Filegroups

  • September 13, 2007 at 9:18 am

    #118264

    I have a shared SQL server running on a Active/Passive Cluster using SAN Storage.  We currently host databases for different companies about 5-6 databases 6-7gb each.  Would it be better to split the databases into differnet filegroups for each company?

    Thanks

  • September 13, 2007 at 9:43 am

    #733512

    For that size, I would *not* bother with all the extra maintenace overhead.


    * Noel

  • September 13, 2007 at 10:05 am

    #733524

    Thank you, What sort of size would it be benificial, these databases are expected to grow quickly and more groups of databases are likley to be added.

    Thanks

  • September 13, 2007 at 10:41 am

    #733548

    The answer to that question is really tricky but here are some guidelines:

    1. If your hardware can handle the Backup-Restore requirements of the entire content based on your SLAs then you would NEVER need to do it

    2. If your recovery times (after a failure) starts to get tight you should consider creating separate filegroups and move whatever minimum number of tables (and indices) you need to be online. Typically this consists on splitting the DB into sections like 'system', 'onlinedata', 'archivetables','audit'. Then create a recovery plan for the structure you comeup with.

    3. On some cases tables grow pretty large ( hundreds or thousands ) millions of rows and the IO path of a single LUN or RAID Volume is not enough then you should create a separated filegroup on a separated physical media to allow for more IO bandwith.

    4. Last and not least. I have managed many large databases in which none of the above has been required, so before jumping into the filegroup bandwagon make sure you have no other problem that is really affecting you.

    hth


    * Noel

  • September 13, 2007 at 2:43 pm

    #733624

    I agree with Noel. 

    Only time multiple filegroups have ever proven helpful to me in past was when I had medium sized DB (100-250GB), and had to temporarily take it offline & copy to another box every week.  We found it was about 2x faster for us to copy 4 smaller files in parallel than to copy a single large file.  I don't even know if that experience can be generalized to other environments.  All other DBs I've ever worked with, I saw no real benefit to multiple filegroups. 

    And unless you're DB is hosted on a workstation, you should be able to rely on your RAID adapter or SAN to spread IOs across multiple disks--no need to try to handle this manually with filegroups.

  • September 14, 2007 at 3:31 am

    #733697

    Ok, thank you both for your help

  • September 14, 2007 at 5:58 pm

    #733929

    I think the decision has more to do with your SLA than it does with necessity.  There is always the possibility that one company could impact the whole system.  If you need to isolate one to protect the others, maybe, but can't say I think you would benefit from a performance perspective.

    Are your controllers balanced, multi pathed, etc.  If not, then it probably has no benefit at all. 

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

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