Post reply

Database Design: Should we have Multiple Data files and Multiple File Groups – SQL Server 2005

  • December 30, 2010 at 6:39 am

    #141984

    Database Design: Should we have Multiple Data files and Multiple File Groups – SQL Server 2005?

    Dear Experts, I am designing a small database in SQL Server 2005. I am convinced with the reason that our database should have secondary data files, because of various reasons like:

    For availability reasons it's always best to keep only system data in your primary data file (with Sql2k5 and up, so long as the primary data file is available, the database can be brought online, allowing you to repair/restore/etc. non-system data while having as much online as possible).If we can separate out that system catalog data in the primary data file, and put our user data into a secondary file, the primary file is smaller, gets a lot less updates and inserts, and thus the chance of corruption by e.g. a bad disk sector is minimized.

    My dilemma is how we can restrict user data from NOT getting into Primary data-file. The only way it appears possible to me is as below:

    - Keeping only the Primary data-file in Primary File-group

    - Creating a Secondary file-group with secondary data files and creating my physical objects like tables/indexes etc on this secondary file-group.

    So, please suggest:

    1.Is it always advisable to have a secondary file-group with secondary data-files and leave the Primary file-group with only primary data-file in it? Please suggest otherwise.

    2.With the above configuration, is there any performance impact for small databases say, less than 10 GB in size?

    Thanks in advance!

    - Chayan.

  • January 10, 2011 at 5:24 am

    #1271322

    Any opinion or thought on this will be helpful.

    Thanks.

  • January 11, 2011 at 4:37 am

    #1271801

    You are right in what you say about separating the system data and user data, and many experts in SQL Server database design say this is a good idea.

    However in practice, most people do not bother doing this.

    If you do want to split your system data and user data, then set up the second filegroup as you suggest. Then mark the second filegroup as the Default filegroup (Google can find the right command), and all further database objects will automatically be put in the second filegroup.

    If the CREATE statement for a new object explicitly requests it goes into the Primary filegroup, there is nothing you can do to prevent this, but if no data placement clause is given (which is the normal situation), then it will automatically go into the second filegroup.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • January 12, 2011 at 2:28 am

    #1272235

    Thanks for the opinion Ed.

  • January 12, 2011 at 8:51 am

    #1272421

    For a small database I wouldn't worry about it.

    But if you want to keep user objects out of the Primary data file, consider the use of DDL triggers to check if a user object is being created on the Primary file and roll it back.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • January 12, 2011 at 9:00 am

    #1272429

    What edition of SQL Server is this? If it's not Enterprise, you can't restore Primary and then get people working while you restore other files.

    I have heard this advice before, and while it's sound, unless you have a large database and are looking for separating files for performance or better protection of some data, I wouldn't bother.

  • January 12, 2011 at 11:34 pm

    #1272817

    Thanks All of you.

    The edition is SQL Server 2005 Enterprise and the database is expected to grow huge in size over time. I had already incorporated the database with secondary file-group as default file-group with all user data placed in the secondary file-group.

    Needed some thoughts from the community. Thanks for your input.

    - Chayan.

  • January 13, 2011 at 10:29 am

    #1273171

    Ed has good suggestions for you.

    I might also schedule a job that checks for new objects in the primary filegroup every night. Let it run and email you if there is anything created. Good to have a backup check.

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

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