如何跟踪源代码管理中的数据库更改？
我们在大多数项目中使用 SQL Server 2000/2005 和 Vault 或 SVN。 我还没有找到一个合适的解决方案来捕获任一源代码控制系统中的数据库模式/过程更改。
我们当前的解决方案非常麻烦且难以执行（编写您更改的对象并将其提交到数据库）。
关于如何通过一些自定义开发来解决这个问题，我们有很多想法，但我宁愿安装现有的工具（付费工具也可以）。
那么：如何跟踪数据库代码更改？ 您有什么推荐的工具吗？
编辑：
感谢您的所有建议。 由于时间有限，我不想在这里自己动手。 大多数建议都存在缺陷，要求开发人员遵循某些程序。
相反，理想的解决方案是监视 SQL 数据库的更改并将任何检测到的更改提交到 SCM。 例如，如果 SQL Server 有一个附加组件，可以记录进行更改的用户的任何 DML 更改，然后将该对象的脚本提交到 SCM，我会很兴奋。
我们内部讨论了两个系统： 1. 在 SQL 2005 中，使用对象权限来限制您在执行“签出”之前更改对象。 然后，签入程序会将其编写到 SCM 中。 2. 运行计划作业以检测任何更改并将其（匿名）提交给 SCM。
如果我可以跳过用户操作部分并让系统自动处理所有这些，那就太好了。
We use SQL Server 2000/2005 and Vault or SVN on most of our projects. I haven't found a decent solution for capturing database schema/proc changes in either source control system.
Our current solution is quite cumbersome and difficult to enforce (script out the object you change and commit it to the database).
We have a lot of ideas of how to tackle this problem with some custom development, but I'd rather install an existing tool (paid tools are fine).
So: how do you track your database code changes? Do you have any recommended tools?
Edit:
Thanks for all the suggestions. Due to time constraints, I'd rather not roll my own here. And most of the suggestions have the flaw that they require the dev to follow some procedure.
Instead, an ideal solution would monitor the SQL Database for changes and commit any detected changes to SCM. For example, if SQL Server had an add-on that could record any DML change with the user that made the change, then commit the script of that object to SCM, I'd be thrilled.
We talked internally about two systems:
1. In SQL 2005, use object permissions to restrict you from altering an object until you did a "checkout". Then, the checkin procedure would script it into the SCM.
2. Run a scheduled job to detect any changes and commit them (anonymously) to SCM.
It'd be nice if I could skip the user-action part and have the system handle all this automatically.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（13）
如果您使用 .Net 并且喜欢 Rails 的迁移方法，那么我会推荐 Migrator.Net< /a>.
我发现了 很好的教程，介绍了如何在 Visual Studio 中进行设置。 他还提供了一个示例项目以供参考。
If you are using .Net and like the approach Rails takes with Migrations, then I would recommend Migrator.Net.
I found a nice tutorial that walks through setting it up in Visual Studio. He also provides a sample project to reference.
我们开发了一个自定义工具来更新我们的数据库。 数据库架构存储在与数据库无关的 XML 文件中，然后由该工具读取和处理该文件。 架构存储在 SVN 中，我们添加适当的注释来显示更改的内容。 它对我们来说效果很好。
虽然这种解决方案对于大多数项目来说绝对是大材小用，但它有时确实会让生活变得更轻松。
We developed a custom tool that updates our databases. The database schema is stored in a database-neutral XML file which is then read and processed by the tool. The schema gets stored in SVN, and we add appropriate commentary to show what was changed. It works pretty well for us.
While this kind of solution is definitely overkill for most projects, it certainly makes life easier at times.
我们的数据库管理员定期根据 SVN 中的内容检查产品并删除不受源代码控制的任何对象。 只需一次，开发人员就不会忘记再次将某些内容放入源代码管理中。
我们也不允许任何人在没有脚本的情况下将对象移动到生产环境，因为我们的开发人员没有生产权利，这很容易执行。
Our dbas periodically check prod against what is in SVN and delete any objects not under source control. It only takes once before the devlopers never forget to put something in source control again.
We also do not allow anyone to move objects to prod without a script as our devs do not have prod rights this is easy to enforce.
为了跟踪所有更改（例如插入、更新和删除），SVN 会产生大量开销。
最好只跟踪 ddl 更改，例如更改架构的（更改、删除、创建）。
您可以通过创建一个表和一个向该表插入数据的触发器来轻松执行此模式跟踪。
任何时候您都可以通过从该表查询来获取更改状态
这里有很多示例和此处
In order to track all the change like insert update and delete there will be a lot of overhead for the SVN.
It is better to track only the ddl changes like (alter, drop, create) which changes the schema.
You can do this Schema tracking easily by creating a table and a trgger to insert data to that table.
Any time you want u can get the change status by querying from that table
There are a lots of example here and here
使用 Visual Studio 数据库版本编写数据库脚本。 工作起来就像一个魅力，你可以使用任何源代码控制系统，当然最好是它有 VS 插件。 该工具还有许多其他有用的功能。 中查看它们
在这篇精彩的博客文章http://www.vitalygorn.com/blog/post/2008/01/Handling-Database-easily-with-Visual-Studio-2008.aspx
或查看 MSDN 获取官方文档
Use Visual studio database edition to script out your database. Works like a charm and you can use any Source control system, of course best if it has VS plugins. This tool has also a number of other useful features. Check them out here in this great blog post
http://www.vitalygorn.com/blog/post/2008/01/Handling-Database-easily-with-Visual-Studio-2008.aspx
or check out MSDN for the official documentation
使用各种第三方工具可以直接从 SSMS 跟踪数据库更改。 ApexSQL Source Control 自动编写版本控制中包含的任何数据库对象的脚本。 该工具无法自动执行提交。 相反，用户需要选择要提交哪些更改。
从存储库获取更改时，ApexSQL 源代码管理会识别 SQL 数据库引用完整性。 因此，它将创建一个同步脚本，其中包括将包装在事务中的所有依赖对象，因此，在没有遇到错误的情况下将应用所有更改，或者不应用任何选定的更改。 无论如何，数据库完整性不受影响。
Tracking database changes directly from SSMS is possible using various 3rd party tools. ApexSQL Source Control automatically scripts any database object that is included in versioning. Commits cannot be automatically performed by the tool. Instead, the user needs to choose which changes will be committed.
When getting changes from a repository, ApexSQL Source Control is aware of a SQL database referential integrity. Thus, it will create a synchronization scripts including all dependent objects that will be wrapped in a transactions so, either all changes will be applied in case no error is encountered, or none of the selected changes is applied. In any case, database integrity remains unaffected.
我不得不说，我认为 Visual Studio 数据库项目也是解决源代码控制困境的合理方案。 如果设置正确，您可以从 IDE 对数据库运行脚本。 如果您的脚本是旧的，请获取最新的脚本，然后针对数据库运行它。 如果需要的话，有一个可以重新创建所有对象的脚本，也必须手动将新对象添加到该脚本中，但只有当
我希望每个表、过程和函数都位于它自己的文件中时。
I have to say I think a visual studio database project is also a reasonable solution to the source control dilemma. If it's set up correctly you can run the scripts against the database from the IDE. If your script is old, get the latest, run it against the DB. Have a script that recreates all the objects as well if you need, new objects must be added to the this script as well by hand, but only once
I like every table, proc and function to be in it's own file.
一个穷人的解决方案是添加一个预提交挂钩脚本，将最新的数据库模式转储到一个文件中，并将该文件与代码一起提交到您的 SVN 存储库。 然后，您可以将数据库模式文件与任何修订版进行比较。
One poor man's solution would be to add a pre-commit hook script that dumps out the latest db schema into a file and have that file committed to your SVN repository along with your code. Then, you can diff the db schema files from any revision.
我只是将 SQL-alter-Statement 添加到完整的 SQL-CreateDB-statement 中。
I just commit the SQL-alter-Statement additional to the complete SQL-CreateDB-statement.
从头开始自己开发不太可行，但如果您使用像 Redgate SQL Compare SDK 为您生成更改文件，不需要很长时间即可将您想要的内容进行半滚动，然后只需将这些文件签入源代码管理即可。 我为自己推出了类似的东西，在短短几个小时内将更改从我们的开发系统更新到我们的实时系统。
Rolling your own from scratch would not be very doable, but if you use a sql comparison tool like Redgate SQL Compare SDK to generate your change files for you it would not take very long to half-roll what you want and then just check those files into source control. I rolled something similar for myself to update changes from our development systems to our live systems in just a few hours.
在我们的环境中，我们从不手动更改数据库：所有更改都是在发布时通过脚本完成的，并且脚本保存在版本控制系统中。 此过程的一个重要部分是确保所有脚本都可以针对同一个数据库再次运行（脚本是幂等的？）而不丢失数据。 例如，如果您添加一列，请确保在该列已存在的情况下不执行任何操作。
您关于“建议存在缺陷，要求开发人员遵循某些程序”的评论确实很能说明问题。 这不是缺陷，这是一个特点。 版本控制可以帮助开发人员遵循流程并减少流程的痛苦。 如果您不想遵循程序，则不需要版本控制。
In our environment, we never change the DB manually: all changes are done by scripts at release time, and the scripts are kept in the version control system. One important part of this procedure is to be sure that all scripts can be run again against the same DB the scripts are idempotent?) without loss of data. For example, if you add a column, make sure that you do nothing if the column is already there.
Your comment about "suggestions have the flaw that they require the dev to follow some procedure" is really a tell-tale. It's not a flaw, it's a feature. Version control helps developers in following procedures and makes the procedures less painful. If you don't want to follow procedures, you don't need version control.
在 SQL2000 中，将每个对象生成到它自己的文件中，然后将它们全部签入源代码管理中。 让您的源代码控制处理更改历史记录。
在 SQL 2005 中，您需要编写一些代码来将所有对象生成到单独的文件中。
In SQL2000 generate each object into it's own file, then check them all into your source control. Let your source control handle the change history.
In SQL 2005, you'll need to write a bit of code to generate all objects into separate files.
在一个项目中，我在设计时特别注意，数据库中的所有重要数据都可以从外部自动重新创建。 启动时，应用程序会创建数据库（如果丢失），并使用应用程序源代码中的架构（因此随应用程序进行版本控制）从外部数据源填充该数据库。 数据库存储名称（一个 sqlite 文件名，尽管大多数数据库管理器允许多个数据库）包含架构版本，每当提交架构更改时，我们都会增加架构版本。 这意味着当我们将应用程序重新启动到具有不同架构的新版本时，会自动创建并填充新的数据库存储。 如果我们必须将部署恢复到旧模式，那么旧版本的新运行将使用旧数据库存储，因此我们可以在出现问题时快速降级。
本质上，数据库的行为就像传统的应用程序堆，具有持久性、事务安全性、静态类型（因为我们使用Python而方便）和唯一性约束的优点。 然而，我们根本不担心删除数据库并重新开始，而且人们知道，如果他们在数据库中尝试一些手动破解，那么它将在下一次部署时恢复，就像进程状态的黑客将恢复一样在下次重新启动时。
我们不需要任何迁移脚本，因为我们只需切换数据库文件名并重新启动应用程序，它就会自行重建。 将应用程序实例分片为每个客户端使用一个数据库会很有帮助。 它还减少了数据库备份的需要。
如果从外部源构建数据库所需的时间比允许应用程序保持关闭状态的时间长，则此方法将不起作用。
In one project I arranged by careful attention in the design that all the important data in the database can be automatically recreated from external places. At startup the application creates the database if it is missing, and populates it from external data sources, using a schema in the application source code (and hence versioned with the application). The database store name (a sqlite filename although most database managers allow multiple databases) includes a schema version, and we increase the schema version whenever we commit a schema change. This means when we restart the application to a new version with a different schema that a new database store is automatically created and populated. Should we have to revert a deployment to an old schema then the new run of the old version will be using the old database store, so we get to do fast downgrades in the event of trouble.
Essentially, the database acts like a traditional application heap, with the advantages of persistence, transaction safety, static typing (handy since we use Python) and uniqueness constraints. However, we don't worry at all about deleting the database and starting over, and people know that if they try some manual hack in the database then it will get reverted on the next deployment, much like hacks of a process state will get reverted on the next restart.
We don't need any migration scripts since we just switch database filename and restart the application and it rebuilds itself. It helps that the application instances are sharded to use one database per client. It also reduces the need for database backups.
This approach won't work if your database build from the external sources takes longer than you will allow the application to be remain down.