用于归档过程的 SQL Server 数据库设计

Question

我目前正在开展一个大型项目，将 MS Access 应用程序迁移到 SQL 服务器。我们的团队一直在讨论设计新数据库部分的最佳方法，因此我认为我应该寻求反馈。

在当前的 MS Access 设计中，我们每天加载数据，然后在 UI 中进行处理。由于 Access 的大小限制为 2GB，因此我们执行以下操作。

1. 早上导入 - 首先删除前一天的数据，然后加载新数据
2. 白天，用户处理数据
3. 一天结束时，我们会拍摄数据库快照（基本上是 Access DB 的直接副本，供用户存储以便能够回溯进行研究）

在新版本中，插入到每个表中的所有数据都会被分配一个业务日期，即添加到表中的日期。然后在前端，UI 将按日期进行搜索以获取当天的记录。

目前我们正在考虑以下事项：

1. 创建 2 个数据库 - 一个用于当天，另一个用于存档。通常每天删除的数据将在每天早上移至存档数据库。对于前端，我们必须使用UNION2 个数据库来获取最终结果集
2. 在同一个数据库中创建 2 个架构。一个用于当前，另一个用于存档。我们再次需要union数据，但它将位于同一个数据库中
3. 只需将数据保留在当前表中，我们就不需要对 UI 搜索的数据进行联合。

让我补充一点，我们需要保存加载到数据库的至少 6 年的数据，因此表的大小会变得非常大。

我只是想找出解决此类问题的最佳方法。我相信我们并不是唯一处于这种情况的人。我愿意听取任何关于我们该如何进行的建议和想法。

Answer 1

每种方法都有可能的优点和缺点，而且可能存在您尚未考虑过的选项。以下是一些一般性意见，希望对您有所帮助。

1. 创建 2 个数据库
   • 维护变得稍微复杂一些，需要保持 2 个数据库同步，管理 2 个数据库的备份、检查性能等。
   • 前端访问变得稍微复杂一些，您需要对数据进行 UNION。我建议设置一个视图来处理这个问题，而不是将逻辑编码到前端。
   • 维护操作更加灵活：备份、重新索引、表锁等都可以在两个数据库中独立进行，并且您可以将它们分离到不同的存储上以获得性能优势。
2. 创建 2 个架构
   • 我认为这种方法没有其他两种方法所没有的优点
   • 这种方法的缺点是，你使访问变得更加复杂，但你却得不到任何(?) 作为回报，需要额外的维护或性能灵活性。
3. 保留单个数据库，简单模式：
   • 这具有简单的优点。如果你不知道长期需要什么，并且在 SQL Server 维护、设计等方面没有太多经验，那么这可能是最好的方法
   • 主要的风险是你最终会在单个地方存储过多的数据，但这可能可以通过适当的索引来抵消——对于一个数据库来说，如果真的难以管理，你可能需要存储数百 GB 的数据。
   • 如果您发现以后需要出于性能原因而拆分事物，您可以随时在以后实施选项 1 或选项 4（或者我猜甚至是 2）——通过视图，您可以“隐藏”前端的实施细节，甚至可能隐藏 ETL 流程的实施细节（尽管您可能不希望这样）
4. 额外选项使用多个文件组进行分区
   • 您可以将数据保存在一个数据库中，但可以将数据保存在不同的文件中，可能保存在不同的位置。
   • 这可能是处理非常大的数据集的“换入”和“换出”的最佳方式（也许是未来的事）
   • 您仍然需要创建一个视图来整合基础数据
   • 这仍然相当复杂，并不是你想轻易做的事情——如果你发现自己每天开始处理不合理的大量数据，你可能会研究一下。

关于分区的随机参考看起来相当不错（抱歉，没有彻底搜索参考资料，并且手边也没有任何资料）：http://www.sqlservercentral.com/articles/partition/64740/

Answer 2

您可以通过视图（date-math）向用户公开“当前”数据。单一数据库。