既然我在管理数据库方面没有非常丰富的经验,我正在寻找存档旧数据的最佳方法.
信息
>数据库中总共有大约310’000’000条记录.
>数据库在硬盘上需要250 GB.
>服务器版本是sql Server 2008,兼容级别为sql Server 2005(90),但我们计划很快升级到sql Server 2012
我考虑过两种可能性：
新数据库
创建类似于生产服务器上的数据库,并将所有旧数据插入新数据库中.
>缺点：由于我们的环境中不允许链接服务器,因此如果需要,很难加入旧数据
历史架构
创建一个新的架构f.e. [hist]与生产数据库中的表相同.在新架构中的这些新表中插入所有旧数据.
>优势：如果将来需要旧数据,可以轻松加入
>您是否优先考虑其中一种解决方案？
>为什么？
>有没有更好的可能性？
>现有的工具是否可以轻松完成此任务？
>还有其他想法吗？
提前致谢
编辑
附加问题：
新创建的存档表是否还需要主键/外键？
或者他们应该只有列但没有键/约束？
解决方法
也许您的查询在整个事实表上执行表扫描,即使只需要日期范围的一小部分(例如,去年)？如果存在对优化最重要的特定查询,请考虑在另一个问题中发布架构,查询和实际执行计划,以查看是否可以对其进行优化.
Do you prefer one of the solutions over the other?
我通常更喜欢历史数据库,我认为Guy在his response中描述了很好的理由.
我在历史数据库(而不是模式)中看到的主要缺点是您不能再使用外键来存档表.这对你来说可能没什么问题,但这是值得注意的.
您列出的这种方法的缺点并不准确;您将能够轻松地在同一服务器上跨数据库进行查询,并且查询优化器通常可以很好地处理跨数据库查询.
Are there any better possibilities?
如果您需要定期查询存档数据,我可能会考虑partitioning the table by date.但是,这是一个很大的变化,可能带来很多性能影响,包括正面(例如,分区消除,更有效的数据加载)和负面(例如,较慢的单例寻求,并行查询中线程偏斜的可能性更大).因此,如果它是一个使用频繁的数据库,我不会轻易做出这个决定.
Would the newly created archive table also need primary / foreign keys?
Or should they just have the columns but without keys / constraints?我建议至少使用主键和唯一索引,以便您可以获得它们提供的数据完整性优势.例如,这将防止您意外地将一年的数据插入历史记录表两次.如果您确实需要查询历史记录表,那么它可以提高性能.
Any other thoughts?
由于您使用的是Enterprise Edition并计划升级到sql 2008,因此您可以考虑使用此表的data compression.压缩肯定会减少磁盘空间,但是根据服务器的磁盘和cpu资源,它还可以通过减少磁盘I / O和提高内存利用率来提高读取的查询性能(一次更多数据适合缓存).
