MSBuild：自动收集数据库迁移脚本？
环境总结。
- Asp.net Web 应用程序（源存储在 svn 中）
- SQL Server 数据库。 （存储在 svn 中的数据库模式（表/存储过程））
- 数据库版本与 Web 应用程序程序集版本同步。 （存储在表“CurrentVersion”中）
- CI Hudson 服务器从存储库中检出 Web 应用程序并运行自定义 msbuild 文件来发布/打包应用程序。
我的 msbuild 脚本会在每次构建时更新 Web 应用程序的程序集版本 (Major.Minor.Revision.Build)。 “修订版”设置为当前签出的 svn 修订版，“构建”设置为 hudson 构建号（在每次自动构建时递增）。
这样我就可以将应用程序与特定的主干版本相匹配，还可以从 hudson 版本号获取其他版本统计信息。
我想自动收集迁移脚本（更新的存储过程等）以添加到 zip 包中。 我想通过将尚未部署的数据库的 svn 修订版与正在部署的修订版进行比较，我可以找到自上次部署到该数据库/环境以来主干中更改了哪些数据库文件。
通过手动调用 svn diff -r REVNO:REVNO 命令来列出已更改的 .sql 文件，可以轻松实现此目的。然后，必须手动将这些文件添加到包中。 如果这可以自动化，那就太好了。
首先，我想我必须编写一个自定义任务来检查尚未部署的数据库的版本。之后我就很不确定了。 有谁对如何通过现有或自定义的 msbuild 任务来实现这一点有任何建议吗？
最后，我必须自动生成一个脚本以添加到更新数据库版本表的包中，以便与应用程序同步。
Summary of environment.
- Asp.net web application (source stored in svn)
- SQL Server database. (Database schema (tables/sprocs) stored in svn)
- db version is synced with web application assembly version. (stored in table 'CurrentVersion')
- CI hudson server that checks out web app from repo and runs custom msbuild file to publish/package app.
My msbuild script updates the assembly version of the web app (Major.Minor.Revision.Build) on each build. The 'Revision' is set to the currently checked out svn revision and the 'Build' to the hudson build number (incremented on each automated build).
This way i can match the app to a specific trunk revision also get other build stats from the hudson build number.
I'd like to automate the collecting of migration scripts (updated sprocs etc) to add to the zip package.
I guess by comparing the svn revision of the db that has yet to be deployed to, to the revision being deployed, i can find what db files have changed in the trunk since the last deployment to that database/environment.
This could easily be achieved by manually calling the svn diff -r REVNO:REVNO command to list changed .sql files. These files could then manually have to be added to the package.
It would be great if this could be automated.
Firstly i'd imagine I'll have to write a custom task to check the version of the db that has yet to be deployed to. After that I'm quite unsure.
Does anyone have any suggestion on how this would be achieved through an msbuild task either existing or custom?
Finally I'll have to autogen a script to add to the package that updates the database version table so as to be in sync with the application.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
将 SQL 更改集成到自动构建/部署过程中非常困难。我知道，因为我已经尝试过几次但收效甚微。你想做的事情大致是在正确的轨道上，但我认为它实际上有点太复杂了。在您的提案中，您建议收集需要在构建/打包时应用于数据库的特定 SQL 脚本。相反，您应该将所有增量脚本（针对数据库的整个历史记录）打包到您的项目中，并计算部署时实际需要应用的增量 - - 这样，您的可部署包可以部署到具有不同版本数据库的环境中。要实现此目的，您需要两个实现部分：
1) 您需要将增量打包到可部署包中。请注意，您应该打包增量 - 而不是在当前状态下创建架构的静态文件。这些增量脚本应该在源代码管理中。也可以将静态模式保留在源代码管理中，但您必须使其与增量保持同步。实际上，您可以使用 Red Gate 的 SQLCompare 或 VS 数据库版本等工具从静态模式生成（大多数）增量。要将增量添加到可部署包中，并且考虑到您正在使用 svn，您可能需要研究 svn:externals 作为将增量脚本“软链接”到您的 Web 项目中的方法。然后，您的构建脚本可以将它们简单地复制到您的可部署包中。
2）您需要一个可以读取增量文件列表，将它们与现有数据库进行比较，确定哪些增量需要应用于该数据库，然后应用增量的系统（并更新簿记信息，例如数据库版本） 。有一个名为 dbdeploy 的开源项目（由 ThoughtWorks 赞助）可以完成此任务。我个人在使用该工具方面取得了一些成功。
祝你好运——这是一个很难破解的难题（正确地）。
Integrating SQL changes into an automated build/deploy process is HARD. I know, because I've tried to to it a couple times with limited success. What you're trying to do is roughly on the right track, but I would argue that it's actually a bit too complicated. In your proposal, you suggest collecting the specific SQL scripts that need to be applied to your DB at build/package time. Instead, you should package all your delta scripts (for the entire history of your database) with your project, and calculate the deltas that actually need to be applied when you deploy -- that way, your deployable package can be deployed to environments with databases of differing versions. There are two implementation pieces you need to achieve this:
1) You need to package your deltas into your deployable package. Note that you should package deltas -- not static files that create the schema in its current state. These delta scripts should be in source control. It's okay to keep the static schema in source control as well, but you will have to keep it in sync with the deltas. You can actually use a tool like Red Gate's SQLCompare or the VS Database version to generate (most) deltas from the static schema. To get the deltas into your deployable package, and given that you're using svn -- you may want to look into svn:externals as a way to "soft link" the delta scripts into your web project. Your build script can then simply copy them into your deployable package.
2) You need a system that can read the list of delta files, compare them to an existing database, determine which deltas need to be applied to that database, and then apply the deltas (and update the bookkeeping information, like the database version). There is an open-source project (sponsored by ThoughtWorks) called dbdeploy that accomplishes this. I've had some success with that tool personally.
Good luck -- this is a tough nut to crack (correctly).
查看 SQL 数据库项目。在 VS 2010 中，它们得到了很大的增强，并且内置了部署功能，可以将您的 DEV 数据库同步到其他环境。
以下是一些关于 vs 2010 中的 DB 项目的好链接：
http: //msmvps.com/blogs/deborahk/archive/2010/05/02/vs-2010-database-project-building-and-deployment.aspx
http://weblogs.asp.net/gunnarpeipman/archive/2009/07/29 /visual-studio-2010-database-projects.aspx
Have a look at SQL database projects. In VS 2010 they have been enhanced quite a bit and have built in deployment capabilities that can sync your DEV database to other environments.
Here are a few good links about DB projects in vs 2010:
http://msmvps.com/blogs/deborahk/archive/2010/05/02/vs-2010-database-project-building-and-deployment.aspx
http://weblogs.asp.net/gunnarpeipman/archive/2009/07/29/visual-studio-2010-database-projects.aspx
尝试 SQL 检查器：
http://www.sqlaccessories.com/Howto/Version_Control.aspx
您可以自动化使用 SQL Examiner 命令行工具收集脚本。
Try SQL Examiner:
http://www.sqlaccessories.com/Howto/Version_Control.aspx
You can automate script collecting with SQL Examiner command-line tool.
目前针对 .NET/SQL Server 堆栈的可用解决方案有：
自动生成脚本）
后一种产品是我们在 Redgate 积极开发的产品。
The solutions available today that target a .NET/SQL Server stack are:
auto-generation of scripts)
The latter product is one that we're actively developing here at Redgate.