最佳数据库变更控制方法 [英] Best Database Change Control Methodologies

问题描述

作为数据库架构师、开发人员和顾问，有很多问题可以回答.一，虽然最近有人问我，还是答不上好，是...

As a database architect, developer, and consultant, there are many questions that can be answered. One, though I was asked recently and still can't answer good, is...

保持数据库更改记录、组织并能够在单开发人员或多开发人员环境中有效推出的最佳方法或技术之一或部分是什么."

"What is one of, or some of, the best methods or techniques to keep database changes documented, organized, and yet able to roll out effectively either in a single-developer or multi-developer environment."

这可能涉及存储过程和其他对象脚本，但尤其是模式 - 从文档到新的物理更新脚本，再到推出，然后是完整的循环.有一些应用程序可以实现这一点，但需要模式挂钩和开销.我更想了解所使用的技术，而无需过多的第三方参与.

This may involve stored procedures and other object scripts, but especially schemas - from documentation, to the new physical update scripts, to rollout, and then full-circle. There are applications to make this happen, but require schema hooks and overhead. I would rather like to know about techniques used without a lot of extra third-party involvement.

推荐答案

我所见过的在没有外部工具帮助的情况下完成此操作的最简单方法是创建架构补丁"(如果您愿意).模式补丁只是一个简单的 t-sql 脚本.脚本中的模式补丁被赋予一个版本号，该版本号存储在数据库的表中以接收更改.

The easiest way I have seen this done without the aid of an external tool is to create a "schema patch" if you will. The schema patch is just a simple t-sql script. The schema patch is given a version number within the script and this number is stored in a table in the database to receive the changes.

对数据库的任何新更改都涉及创建一个新的架构补丁，然后您可以按顺序运行该补丁，然后检测数据库当前所在的版本并运行其间的所有架构补丁.之后，使用补丁执行的任何日期/时间更新架构版本表以存储下次运行.

Any new changes to the database involve creating a new schema patch that you can then run in sequence which would then detect what version the database is currently on and run all schema patches in between. Afterwards the schema version table is updated with whatever date/time the patch was executed to store for the next run.

一本像这样详细介绍的好书叫做重构数据库.

A good book that goes into details like this is called Refactoring Databases.

如果您想使用外部工具，您可以查看 Ruby 的迁移 项目或 C# 中名为 Migrator.NET 的类似工具.这些工具通过创建具有向前"和向后"迁移的 c# 类/ruby 类来工作.这些工具的功能更加丰富，因为它们知道如何在模式补丁中前进和后退.但是，正如您所说，您对外部工具不感兴趣，但我想无论如何我都会为其他读者添加它.

If you wish to use an external tool you can look at Ruby's Migrations project or a similar tool in C# called Migrator.NET. These tools work by creating c# classes/ruby classes with an "Forward" and "Backward" migration. These tools are more feature rich because they know how to go forward as well as backwards in the schema patches. As you stated however, you are not interested in an external tool, but I thought I would add that for other readers anyways.

这篇关于最佳数据库变更控制方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！