源代码管理中的存储过程 - 自动化构建/部署过程
我的公司提供大型.NET 面向服务的解决方案。服务层与由数百个表和存储过程组成的 T-SQL 后端交互。我们的 C# 代码采用版本控制 (SVN)，但我们的存储过程和架构则不然。
经过上层管理人员的多次游说，我被允许审查我们的（不存在的）构建/部署过程，以实现以下目标：
- 将架构和存储过程置于源代码控制之下。
- 自动化构建/部署过程。
我想按照这篇文章中接受的答案的策略继续，但还有其他问题：
我想使用 Hudson 作为我的构建服务器。对于 C#/SQL 解决方案来说，这是一个合理的选择吗？我应该探索哪些更好的替代方案？
假设我在源代码控制下拥有所有触发器、存储过程、模式等，并且它们被编写为单个文件的脚本，我如何生成一个构建脚本来考虑这些项目之间的依赖关系/引用？ （SQL Server 自动执行此操作，但它会生成一个巨大的脚本）
在客户端执行更新的工作流程是什么样的？即我必须保留现有的表数据。如何回滚架构更改？
我是唯一的程序员。其他几位伪技术人员喜欢直接在 SQL Management Studio 内进行更改。期望其他人遵守此解决方案是否现实 - 我如何强制执行此解决方案？
预先感谢您的帮助。
编辑：
不幸的是我们将无法使用 TFS。不过，我们确实有 Visual Studio 2008/2010 和可用的数据库项目组件，所以看起来我必须拼凑出一个基于脚本的解决方案。任何建议/更新表示赞赏..
My company provides a large .NET service-oriented solution. The services layer interact with a T-SQL back-end consisting of hundreds of tables and stored procedures. Our C# code is in version-control (SVN) but our stored procedures and schema are not.
After much lobbying of expedient upper-management, I was allowed to review our (non-existent) build/deployment process to accomplish the following goals:
- Place schema and stored procedures under source-control.
- Automate the build/deployment process.
I would like to proceed per the accepted answer's strategy in this post but have additional questions:
I would like to use Hudson as my build server. Is this a reasonable choice for a C#/SQL solution? What better alternatives should I explore?
Assuming I have all triggers, stored-procedures, schema, etc... under source control, and that they are scripted to individual files, how do I generate a build script which will take into account dependencies/references between these items? (SQL Server does this automatically, but it generates one giant script)
What does the workflow of performing an update at the client look like? i.e. I have to keep existing table data. How do I roll-back schema changes?
I am the only programmer. Several other pseudo-technical staff like to make changes directly inside SQL Management Studio. Is it realistic to expect others to adhere to this solution -- how can I enforce this?
Thank you in advance for your help.
Edit:
Unfortunately we will not be able to use TFS. We do have Visual Studio 2008/2010 with the Database Project components available, though, so it looks like I'll have to hack together a script-based solution. Any suggestions/updates are appreciated..
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Microsoft T-SQL 部署堆栈的典型示例是 Visual Studio 数据库项目部署过程。在此过程中，您的数据库架构、过程、正确分配和几乎所有其他内容都存储为 VSDB 项目的一部分，这意味着它们存储为 SQL 定义文件，并在源代码控制下进行检查（SVN 很好）。 “构建”过程提供一个 .dbschema 文件，该文件包含整个 VSDB 项目的综合（是一个美化的 XML 文件）。然后，.dbschema 文件被发送到部署服务器（开发服务器、QA 验证服务器，甚至生产服务器）并进行“部署”。部署是通过 vsdbcmd 工具完成的，该工具将在部署服务器和 .dbschema 文件，并根据目标数据库/服务器中存在的内容，使用适当的 CREATE/ALTER/DROP 语句将服务器与 .dbschema 文件的内容“对齐”。
连续集成流程将启动夜间构建，将 .dbschema 与其他可交付成果一起删除到测试 SQL 服务器上，部署 .dbschema，然后运行构建验证测试，如果最终一切顺利，将删除完全构建并进行 QA 验证可交付成果，每日“下降”。完全集成到部署到生产中是可能的，但通常会避免，因为存在中央生产服务器意外停机的风险。然而，完全集成和部署到生产中通常是多服务器环境的常态，其中“生产”意味着数百/数千台已部署的服务器。
现在你说你想使用 Hudson 进行部署，这一切都很好，除了你必须重新创建我在上面的步骤中描述的所有内容作为 Ants 构建步骤，并且你将在接下来的 10 年里重新发明 VS DB 项目概念，例如.dbschema 文件和 vsdbcmd 等工具。我不是那个愿意投资购买基于 VSDB 和 TFS 的构建服务器许可证的人，但我想说的是，我不知道 OSS 中是否有可用的端到端解决方案。我相信，在 VS 2010 中，数据库项目是标准版。使用 VS 2008，您需要高端许可证。
当用户从 SSMS 进行更改时：您可以使用 DDL 来阻止他们触发器，您可以使用事件通知来跟踪它们，或者您可以使用C2 合规审核来全面审核它们。
The canonical example on the Microsoft stack for T-SQL deployment is the Visual Studio Database Project deployment process. In this process, your database schema, procedures, right assignment and pretty much all else are stored as pieces of a VSDB project, which means that they are stored as SQL definition files, and checked under source control (SVN is fine). The 'build' process delivers a .dbschema file, which is a file that contains a synthesis of the entire VSDB project (is a glorified XML file). The .dbschema file is then shipped into the deployment server (development server, QA validation server, even produciton server) and 'deployed'. Deployment is done via the vsdbcmd tool which will run a sophisticated diff between the deployment server and the .dbschema file and 'align' the server to the content of the .dbschema file, using CREATE/ALTER/DROP statements as appropriate, based on what exists in the target database/server.
A contiguously integrated process would start a nightly build, drop the .dbschema along with other deliverable on the test SQL server, deploy the .dbschema, then run build validation tests, and if all good in the end will drop a fully build and QA validated deliverable, the daily 'drop'. Fully integration all the way to deployment into production is possible, but usually avoided due to risk of unexpected downtime on the central, production, server. However, fully integration and deployment into production is usually the norm for multi-server environments, where 'production' means hundreds/thousands of deployed servers.
Now you say that you want to deploy using Hudson, which is all good, except that you have to recreate everything I describe in the steps above as Ants build steps and you'll spend the next 10 years reinventing the VS DB project concepts, like a .dbschema file and a tools like vsdbcmd. I'm not the one that can make the call to invest into buying a VSDB and TFS based build server license, but I'm saying that I'm not aware of an end-to-end solution available in OSS. With VS 2010, the Database Projects are in Standard Edition, I believe. With VS 2008 you'd need the high end license.
As of users doing changes riding shot-gun from SSMS: you can prevent them using DDL triggers, you can track them using Event Notifications, or you can fully audit them using C2 compliant audit.