需要更好的方式来管理数据库模式更改 [英] Need a better way to manage database schema changes
问题描述
目前，我们使用一系列带编号的脚本来管理数据库（SQL Server）更改。一个文件夹包含只能运行一次的脚本（表更改，数据初始化等）。另一个保存所有可以多次运行的脚本，而不用担心它们会破坏任何内容（存储过程，函数等）。
Currently, we manage database (SQL Server) changes with a series of numbered scripts. One folder holds scripts that can only be run once (table alters, data initialization, etc). Another holds all scripts that can be run multiple times without fear of them destroying anything (stored procedures, functions, etc).
通常，当我们需要更改数据库，我们将为更改创建一个脚本，对它编号，将其粘贴在相应的文件夹中，然后提交。当我们推送时，我记下运行的最后一个数字，并运行所有后续的脚本。我知道有一个更好的方法来做到这一点，但我想得到你的意见，对你的方法。我可以使用商业解决方案，但开放源码或免费的解决方案是首选。
Typically, when we need to make a change to the database, we'll create a script for the change, number it, stick it in the appropriate folder, and commit it. When we do pushes, I keep notes on the last number that was run, and run all of the subsequent scripts. I know there's a better way to do this, but I wanted to get your opinion on methods that work for you. I'm OK with a commercial solution, but an open source or free one would be preferred.
我的动机是，我不想要考虑关于什么发生了变化，我想尽可能多地减少人为错误的过程。
My motivation for this is that I don't want to have to think about what's changed, and I want to cut as much human error out of the process as possible.
编辑：为了澄清，这是一个ASP.NET应用程序， SQL Server。我们目前用Subversion做版本控制。
Just to clarify, this is an ASP.NET app, with SQL Server. We currently do version control with Subversion.
推荐答案
这里有几个人提到SQL diff工具。如果您试图解除处于未知状态的两个数据库之间的差异，这些可以是救命者，但是它们用于管理数据库迁移的有用性非常有限，在某些情况下不可靠。我写了一个博客条目
There have been several people here mention SQL diff tools. These can be a lifesaver if you're trying to unpick the difference between two databases in an unknown state, but their usefulness for managing database migrations is very limited and in some cases unreliable. I wrote a blog entry giving a critique of this approach some time ago.
这似乎是你现有的数据库迁移脚本的方法，是一个相当不错的方法。我的建议是构建某种类型的脚本运行器，以将它们全部应用到空白（或引用）数据库作为单元测试套件的一部分：然后如果任何人检查破碎的脚本，它打破构建。你需要做的另一件事是在数据库本身保存一个记录，其中已经运行了更改脚本。
It sounds like your existing approach, of database migration scripts, is a fairly good one. My recommendation is to build some kind of script runner to apply them all to a blank (or reference) database as part of your unit test suite: then if anyone checks in a broken script, it breaks the build. Another thing you need to do is keep a record in the database itself of which change scripts have been run.
我建议你签出这个系列的帖子K Scott Allen：
I'd recommend that you check out this series of posts by K Scott Allen:
- Three rules for database work
- The Baseline
- Change Scripts
- Views, Stored Procedures and the Like
- Branching and Merging
（通过Jeff Atwood，获取数据库的版本控制）
(via Jeff Atwood, Get Your Database Under Version Control)
我还推荐Scott Ambler和Pramod J Sadalage的书Refactoring Databases（Addison-Wesley签名系列），以便更全面地处理整个数据库生命周期管理过程。
I'd also recommend the book "Refactoring Databases" by Scott Ambler and Pramod J Sadalage (Addison-Wesley Signature Series) for a more in-depth treatment of the whole database lifecycle management process.
这篇关于需要更好的方式来管理数据库模式更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
