最佳数据库变更控制方法
作为数据库架构师、开发人员和顾问，有很多问题可以回答。 其中之一，虽然我最近被问到，但仍然无法回答好，是......
“保持数据库更改记录、组织并能够在单开发人员或多开发人员环境中有效推出的最佳方法或技术之一是什么？”
这可能涉及存储过程和其他对象脚本，但尤其是模式 - 从文档到新的物理更新脚本，到推出，然后是完整的循环。 有一些应用程序可以实现这一点，但需要模式挂钩和开销。 我更想了解在没有大量额外第三方参与的情况下使用的技术。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我见过的最简单的方法是在不借助外部工具的情况下完成此操作，如果您愿意的话，可以创建一个“架构补丁”。 模式补丁只是一个简单的 t-sql 脚本。 模式补丁在脚本中被赋予一个版本号，并且该版本号存储在数据库的表中以接收更改。
对数据库的任何新更改都涉及创建一个新的架构补丁，然后您可以按顺序运行该补丁，然后检测数据库当前所在的版本并运行其间的所有架构补丁。 之后，模式版本表将使用执行补丁的任何日期/时间进行更新，以存储以供下次运行。
一本介绍此类细节的好书叫做重构数据库。
如果您想使用外部工具，可以查看 Ruby's Migrations 项目或类似项目名为 Migrator.NET 的 C# 工具。 这些工具通过创建具有“向前”和“向后”迁移的 c# 类/ruby 类来工作。 这些工具的功能更加丰富，因为它们知道如何在模式补丁中前进和后退。 然而，正如您所说，您对外部工具不感兴趣，但我想无论如何我都会为其他读者添加它。
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.
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.
我比较喜欢这个系列：
http://odetocode.com/Blogs/scott/archive/ 2008/02/03/11746.aspx
I rather liked this series:
http://odetocode.com/Blogs/scott/archive/2008/02/03/11746.aspx
就我而言，每次更改数据库时都会生成一个脚本，我将脚本命名为 00001.sql、n.sql，并且我有一个表，其中包含我执行的最后一个脚本的编号。 您还可以查看数据库文档
In my case I have a script generate every time I change the database, I named the script like 00001.sql, n.sql and I have a table with de number of last script I have execute. You can also see Database Documentation
只要您将列/表添加到数据库中，通过提前在 sql 文件中编写这些更改的脚本，这将是一项简单的任务。 你只需执行它们即可。 也许你有一些命令来执行它们。
一个好的解决方案是为每个表创建一个文件，以便在该表上工作的任何人都可以看到属于该表的所有更改（就像在类上工作一样）。 这同样适用于存储过程或视图。
更困难的任务（因此也许工具会更好）是退一步。 只要您刚刚添加了表/列，也许这不会是一个大问题。 但是，如果您在更新时删除了列，现在必须撤消更新，则数据将不再存在。 您将需要从备份中获取此数据。 但请记住，如果您有多个表，这可能是一项艰巨的任务，并且在正常情况下您应该非常快地撤消更新！
如果您可以恢复备份，那么此时就可以了。 但是，如果您在星期一更新，您的客户工作到星期三，然后他们发现某些数据丢失（您刚刚从表中删除），那么您就无法恢复旧数据库。
我心中有一种基于模型的方法（抱歉，目前尚未实现），其中模式更改是“建模的”（例如每个 xml），并且在更新期间处理器（例如 ac# 程序）创建所有必要的“sql ”，例如将数据移动到“dropDatabase”。 数据可以驻留在那里，如果由于某种原因我需要恢复一些丢失的数据，我可以使用处理器来完成。 我认为在一段时间内（几年）这种方法并没有那么糟糕，因为否则开发人员不会接触“旧”表，因为他们不再知道表或列是否真的有必要。 通过这种方法，即使您掉落某些东西，也不会冒太大的风险！
as long as you add columns/tables to your database it will be an easy task by scripting these changes in advance in sql-files. you just execute them. maybe you have some order to execute them.
a good solution would be to make one file per table, so that all changes belonging to this table would be visible to who-ever is working on the table (its like working on a class). the same is valid for stored procedures or views.
a more difficult task (and therefore maybe tools would be good) is to step back. as long as you just added tables/columns maybe this would not be a big issue. but if you have dropped columns on an update, and now you have to undo your update, the data is not there anymore. you will need to get this data from the backup. but keep in mind, if you have more then a few tables this could be a big task, and in the normal case you should undo your update very fast!
if you could just restore the backup, then its fine in this moment. but, if you update on monday, your clients work till wednesday and then they see that some data is missing (which you just dropped out of a table) then you could not just restore the old database.
i have a model-based approach in my mind (sorry, not implemented at the moment) in which schema-changes are "modeled" (e.g. per xml) and during an update a processor (e.g. a c# program) creates all necessary "sql" and e.g. moves data to a "dropDatabase". the data can reside there, and if for some reason i need to restore some of the dropped data, i can just do it with the processor. i think over some time (years) this approach is not as bad because otherwise developers don't touch "old" tables because they don't know anymore if the table or column is really necessary. with this approach you don't risk too lot if you drop something!
我所做的是：
诀窍在于，如果数据库无法通过添加列等方式重新创建，我需要进行两项更改，即 ALTER TABLE + 脚本中的修改。 需要做更多的工作，但从长远来看，它会获胜。
What I do is:
The trick is that, if the database cannot be brought down to recreate with, say, an added column, I have two changes to make, an ALTER TABLE + a modification in the script. A bit more work but, in the long term, it wins.