多个数据文件和多个文件组 [英] Multiple Data files and Multiple File Groups
问题描述
尊敬的专家.我有一个关于 SQL Server 2005 中的多个文件组的问题.我确信我们的数据库应该有辅助数据文件，因为各种原因，例如:出于可用性原因，最好只在主数据文件中保留系统数据(使用 Sql2k5 及更高版本，只要主数据文件可用，数据库就可以联机，允许您进行修复/恢复等.非-系统数据，同时尽可能多地在线.如果我们能把主数据文件中的系统目录数据分离出来，把我们的用户数据放到一个辅助文件中，主文件更小，更新和插入少得多，因此腐败的机会，例如坏磁盘扇区被最小化.
Dear Experts. I have a question in mind regarding multiple file groups in SQL Server 2005. I am convinced 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:
- 只保留主文件组中的主数据文件
- 使用辅助数据文件创建辅助文件组，并在此辅助文件组上创建我的物理对象，如表/索引等.
所以，请提出建议:
- 是否始终建议使用带有辅助数据文件的辅助文件组，而让主文件组中只包含主要数据文件?请另有建议.
- 使用上述配置，对于小于 10 GB 的小型数据库是否有性能影响?
提前致谢！
推荐答案
不要费心分离系统和用户数据.它没有添加任何东西.在现实生活中，您的 MDF 要么存在，要么不存在.没有多少灰色阴影.
Don't bother separating system and user data. It doesn't add anything. In real life, either your MDF is there or it isn't. There aren't many shades of grey.
至于拆分，我不会打扰多个文件组，除非有一个或多个:
As for splitting, I wouldn't bother with multiple filegroups until one or more of:
- 我即将达到 TB 级
- 非常高的负载
- 不止一张大桌子(不仅仅是一张大桌子)
- 可能基于负载/大小/大表的单独索引
并且仅当我可以为每个文件使用单独的 LUN 或 RAID 阵列时.否则它毫无意义，因为你在更多文件之间划分有限的资源
And only if I can have separate LUNs or RAID arrays for each file. Otherwise it's pointless because you're dividing a finite resource between more files
总结:对于大多数数据库来说，不值得
Summary: for most databases, it isn't worth it
这篇关于多个数据文件和多个文件组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
