在SQL Server中存储数据库模式版本的最佳实践是什么？

Question

我有一个应用程序将部署在带有SQL Server的生产计算机上。我想要能够在我的数据库中存储和检索模式的版本。

我对能够实现以下主要目标的最佳实践感兴趣：

1. 能够存储并轻松检索数据库的版本号。
2. 由客户端隐藏或更难找到和操纵。
3. 在创建新版本时可以进行编辑/更改。
4. 备份数据库或检测数据库会保留版本号以供取证。

我希望有一种方法可以将“版本”存储在元数据或非常规表中，并可以通过系统存储过程进行访问/设置。

有任何想法或最佳实践吗？

编辑：我发现的一个选项可能很有前途，那就是使用SQL Server扩展属性将键值分配给具有“Schema_Version”和版本号的DB。它没有加密（但值可以加密），也不是隐藏的，但至少从实际的DB结构中删除了一些我们的用户和现场人员浏览的内容（让我很沮丧！ :) ）

- pearcewg

---

你为什么要关心客户端是否可以访问该字段？如果他们直接编辑它，不是会搞乱应用程序，然后这将是他们自己的错吗？ - mellamokb

@mellamokb - 很好的提醒，但我们需要这个特别是因为现场的人正在混淆数据库。我们希望这是用于取证调查，因此隐藏它（例如在SQL元数据中）只会降低修改的可能性。 - pearcewg

@pearcewg 如果你想要取证，那么你就进入了安全领域，而且你的需求也错了。你不应该试图保护数据库免受拥有它的人的攻击 - 这被称为“过度保护设计”。如果你深入思考，你会发现根本没有办法知道一个拥有SA密码的人是否在操纵数据库。这就是整个问题所在。让解决方案“更难找到和操纵”只会让你感觉到受到了保护，但实际上并没有。 - Zoran Horvat

Answer 1

我是Red Gate的SQL Source Control和SQL Compare产品经理。我们不得不解决这个问题，因为我们的工具需要知道数据库所在的版本，以选择适当的迁移脚本来构建完整的部署脚本。

我们考虑了一个版本表，这是最常见的自制解决方案。然而，根据我们的研究，用户希望保持一组数据库对象的“无污染性”，因此我们选择了数据库级扩展属性。我们将其附加到脚本中，如下所示：

IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
  EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL

当将数据库加载到 SQL Compare 中时，它会执行一个检查以确保其声称的版本与源代码控制中存储的版本相符。

希望这可以帮到你！

- David Atkinson

---

大卫：这是一个很好的答案，基本上是我在更多的研究后想到的解决方案。谢谢！ - pearcewg

没问题。很高兴能帮忙！当然，这并不能防止客户对数据库进行更改，这将使其与您标记的版本号不一致。这被称为数据库漂移，是一个相当常见的问题。 - David Atkinson

Answer 2

我已经实现了一个基于单个表的解决方案，该表跟踪四段式模式版本（主要、次要、构建和修订）及其有效性开始和结束的时间戳。只有一行的结束时间戳为NULL，即当前版本。

此外，还有一堆支持此版本控制系统的存储过程，并且所有数据库更改必须使用这些过程来测试和更新模式版本，只要它们对数据库模式进行任何更改（例如添加表、删除列等）。

请注意，完整的更改历史记录存储在跟踪数据库版本的表中。如果出现任何问题，解决方案非常灵活。例如，如果更改在执行过程中失败，它成功完成的所有更改都将在版本表中记住，因为在每个步骤之后它都增加了修订号。您可以改进更改并再次运行它，它将自动跳过已成功完成的步骤，并从上次未完成的第一步继续。

还有一个（可选）技巧——我使用奇数构建号作为中间版本，偶数构建号作为已完成的发布版。通过这种方式，只要更改开始，它就首先将构建号更改为下一个奇数值，然后再执行所需操作。如果您随时看到奇数构建号（版本号的第三段），则可以确定某些更改未完成！一旦更改完成其最后一步，它就会再次更改模式版本，这次是下一个偶数构建号，只是为了通知每个人它已完成。

您可以在本文中查看整个源代码、手册和示例：如何维护SQL Server数据库模式版本。

- Zoran Horvat

---

我喜欢使用奇数来表示正在进行的数据库升级的想法。 - Steve Hiner

Answer 3

坦白说，我们只是在每个数据库中存储模式编号。我们在数据库中有一个表格，仅由软件配置管理团队使用，告诉我们当前版本，以便我们可以快速查看每个环境中的版本。我不会担心把它放到数据库之外，因为这只会使问题更加复杂。

我想，如果您真的想要安全性，您始终可以创建一个存储过程，并将值硬编码在其中。然后，您可以加密存储过程，以便他们无法查看/篡改它而不让您知道。当您更改版本时，只需更新存储过程即可。您还可以进入系统并从系统表中删除已编译的存储过程代码，但我真的不建议这样做，因为这只会导致问题。

- kemiller2002

Answer 4

在我之前的公司所做的是将版本号存储在一个带有几个字段的表中（主版本、次版本、构建和应用日期），以便我可以获得更新历史记录。在表上设置适当的权限就足以防止篡改。

如果您真的想让数据库管理员也难以阅读，可以将这些值作为加密字符串存储在表中。这样只有您才知道如何解码它们。

- Diego