SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
chayan.dutt 25280
chayan.dutt 25280
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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.
chayan.dutt 25280
chayan.dutt 25280
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 70
Any opinion or thought on this will be helpful.
Thanks.
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5942 Visits: 3866
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
chayan.dutt 25280
chayan.dutt 25280
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 70
Thanks for the opinion Ed.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12791 Visits: 8565
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64543 Visits: 19118
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
My Blog: www.voiceofthedba.com
chayan.dutt 25280
chayan.dutt 25280
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64543 Visits: 19118
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search