Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Design: Should we have Multiple Data files and Multiple File Groups – SQL Server 2005 Expand / Collapse
Author
Message
Posted Thursday, December 30, 2010 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 4:51 AM
Points: 6, Visits: 70
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.
Post #1040859
Posted Monday, January 10, 2011 5:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 4:51 AM
Points: 6, Visits: 70
Any opinion or thought on this will be helpful.
Thanks.
Post #1045223
Posted Tuesday, January 11, 2011 4:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:50 AM
Points: 2,854, Visits: 3,174
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 29 May 2014: now over 29,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1045799
Posted Wednesday, January 12, 2011 2:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 4:51 AM
Points: 6, Visits: 70
Thanks for the opinion Ed.
Post #1046332
Posted Wednesday, January 12, 2011 8:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1046548
Posted Wednesday, January 12, 2011 9:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 2:28 PM
Points: 33,062, Visits: 15,174
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1046556
Posted Wednesday, January 12, 2011 11:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 4:51 AM
Points: 6, Visits: 70
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.
Post #1047017
Posted Thursday, January 13, 2011 10:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 2:28 PM
Points: 33,062, Visits: 15,174
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1047434
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse