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!