We use Red Gate to generate scripts for deployment and to control versioning.
"Deployment" and "versioning" are separate issues for SQL code.
Important to note: your production database is master with all it's data. So arrange of regular copies to a test server and use this as a baseline. A database generated by NUnit every night with basic data (seen it, had a laugh) is generally useless. What if you have a billion rows and need to test a query against it?
Versioning: You can use the Red Gate tools to generate a schema as a baseline and then compare this to this copy (or your QA or whatever). The Red Gate tools allows comparison to a folder, which is under SVN control in our case and is updated every release. So we have full history of every object
Deployment: we apply our development scripts (also in SVN) against a clean "build" DB and compare to another clean DB. This becomes our deployment script.
This is quite simplified of course.
The pro version offers an API to synch and compare so you can integrate into your tool chain if needed. No GUI needed. Incidently, we use this to provide a one click synch of some special user sandboxes complete with client code.
As Remus mentioned, they aren't foolproof for some operations. If you are changing stuff on 1.5TB tables, I'd lovingly handcode my script. Another irritation is that Red gate's tool has a habit of dropping SCHEMABINDING on a related view or udf for a simply check constraint change.
我的方法是始终使用 升级脚本，将部署的架构从 v. N 移动到 v. N+1。这样我就可以准确知道升级是如何完成的，并且如果无法执行某个操作（需要持续 2 周的数据大小复制操作......），那么我知道我不能这样做它和我相应地计划为 v.Next 的发布更改我的代码。
I don't trust diff based tools for deployment. And that includes vsdbcmd .schema files, since they are also diff based. Last time I tried to use a diff tool it joyfully offered to change a 1.5 TB table via copy/drop/rename...
My approach is to always use upgrade scripts that move the deployed schema from v. N to v. N+1. This way I know exactly how is the upgrade done, and if an operation is not possible (it would require a size-of-data copy operation lasting 2 weeks...) then I know I cannot do it and I plan my code changes for the release of v. Next accordingly.
我有一个开源（在 LGPL 下许可）工具集项目，它试图解决与（及更多）SQL Server (2005/2008/Azure) 的正确数据库架构版本控制相关的问题，bsn ModuleStore。
基本上，该工具集的独立部分将数据库模式的 SQL Server DB 对象编写为应用了标准格式的文件，这样文件内容仅在对象确实发生更改时才更改（与 VS 完成的脚本形成鲜明对比） ，它还编写了一些脚本日期等，将所有对象标记为已更改，即使它们实际上是相同的）。
但如果您使用 .NET，该工具集的功能远不止于此：它允许您将 SQL 脚本嵌入到库或应用程序中（作为嵌入资源），然后将嵌入的脚本与数据库中的当前状态进行比较。与表无关的更改（根据 Martin Fowler 的定义，这些更改不是“破坏性更改”）可以自动或根据请求应用（例如，创建和删除视图、函数、存储过程、类型、索引等对象），并且更改脚本（尽管需要手动编写）也可以在同一进程中应用；还会创建新表，并可选择与其设置数据一起创建。更新后，数据库架构会再次与脚本进行比较，以确保在提交更改之前数据库升级成功。
请注意，整个脚本和比较代码无需 SMO 即可工作，因此在应用程序中使用 bsn ModuleStore 时，您不会遇到令人痛苦的 SMO 依赖关系。
根据您想要访问数据库的方式，该工具集提供了更多功能 - 它实现了一些 ORM 功能，并提供了一种非常好的且有用的基于接口的方法来调用存储过程，包括对带有本机 .NET XML 类的 XML 的透明支持，以及TVP（表值参数）为 IEnumerable。
I have an open-source (licensed under LGPL) toolset project which tries to address the issues related to proper DB schema versioning for (and more) SQL Server (2005/2008/Azure), the bsn ModuleStore.
Basically, the standalone part of the toolset scripts the SQL Server DB objects of a DB schema into files with a standard formatting applied, so that the file contents only changes if the object really did change (very much in contrast to the scripting done by VS, which scripts some scripting date etc. as well, marking all objects as changed even if they are in fact identical).
But the toolset goes beyond that if you use .NET: it allows you to embed the SQL scripts into the library or application (as embedded resources) and then have it compare the embedded scripts with the current state in the database. Non-table-related changes (those that are not "destructive changes" as per Martin Fowler's definition) can be applied automatically or on request (e.g. creating and removing objects such as views, functions, stored procedures, types, indexes), and change scripts (which need to be written manually though) can be applied in the same process as well; new tables are also created, optionally along with their setup data. After the update, the DB schema is again compared against the scripts in order to ensure a successful DB upgrade before the changes are committed.
Note that the whole scripting and comparison code works without SMO, so that you don't have the painful SMO dependency when using the bsn ModuleStore in applications.
Depending on how you want to access the database, the toolset offers even more - it implements some ORM capabilities and offers a very nice and useful interface-based approach to invoke stored procedures, including transparent support for XML with native .NET XML classes and also for TVPs (Table-Valued Parameters) as IEnumerable<PocoClass>.
SQL Compare can either generate a SQL migration script that can be independently reviewed before applying it, but also gives the option to execute the script within the tool. Red Gate recommends using the former method when deploying to production databases.
For database versioning, SQL Source Control support most source control systems (eg, SVN, TFS, etc, although VSS support has been deprecated). There is, in v3, an option to link to a working folder, allowing you to use your own version control client if desirable.
We use a compare tool as part of our deployment process to see if anything needing a script is missing and then go discuss it with the developer if so (usually it is a differnce that isn't checked in to the deplyment location because it shouldn't be moved to go to prod). But we deploy from scripts that are in source control always. If you rely on SQL Compare or any other compare tool, you may find yourself moving things that should not yet be moved.
发布评论
评论（6）
我们使用 Red Gate 生成部署脚本并控制版本控制。
对于 SQL 代码来说，“部署”和“版本控制”是不同的问题。
需要注意的是：您的生产数据库是其所有数据的主数据库。因此，将常规副本安排到测试服务器并使用它作为基线。一个NUnit每天晚上用基本数据生成的数据库（看到了，笑了）一般没什么用。如果您有十亿行并且需要针对它测试查询怎么办？
版本控制：您可以使用 Red Gate 工具生成架构作为基线，然后将其与此副本（或您的 QA 或其他内容）进行比较。 Red Gate 工具允许与文件夹进行比较，在我们的例子中该文件夹受 SVN 控制，并且每个版本都会更新。因此，我们拥有每个对象部署的完整历史记录
：我们将开发脚本（也在 SVN 中）应用于干净的“构建”数据库，并与另一个干净的数据库进行比较。这成为我们的部署脚本。
当然，这是相当简单的。
专业版提供用于同步和比较的 API，以便您可以根据需要集成到您的工具链中。无需图形用户界面。顺便说一句，我们使用它来提供一些特殊用户沙箱与客户端代码的一键同步。
正如莱姆斯提到的，它们对于某些操作来说并不是万无一失的。如果您要更改 1.5TB 表上的内容，我会很乐意手工编写我的脚本。另一个令人恼火的是，Red Gate 的工具习惯于将 SCHEMABINDING 放在相关视图或 udf 上，以进行简单的检查约束更改。
我还建议阅读 Martin Fowler 的“进化数据库设计”以获得一些灵感
We use Red Gate to generate scripts for deployment and to control versioning.
"Deployment" and "versioning" are separate issues for SQL code.
Important to note: your production database is master with all it's data. So arrange of regular copies to a test server and use this as a baseline. A database generated by NUnit every night with basic data (seen it, had a laugh) is generally useless. What if you have a billion rows and need to test a query against it?
Versioning: You can use the Red Gate tools to generate a schema as a baseline and then compare this to this copy (or your QA or whatever). The Red Gate tools allows comparison to a folder, which is under SVN control in our case and is updated every release. So we have full history of every object
Deployment: we apply our development scripts (also in SVN) against a clean "build" DB and compare to another clean DB. This becomes our deployment script.
This is quite simplified of course.
The pro version offers an API to synch and compare so you can integrate into your tool chain if needed. No GUI needed. Incidently, we use this to provide a one click synch of some special user sandboxes complete with client code.
As Remus mentioned, they aren't foolproof for some operations. If you are changing stuff on 1.5TB tables, I'd lovingly handcode my script. Another irritation is that Red gate's tool has a habit of dropping SCHEMABINDING on a related view or udf for a simply check constraint change.
I also recommend reading Martin Fowler's "Evolutionary Database Design" for some inspiration
我也更喜欢脚本——易于存储在源代码管理（CVS、Git 等）中，这样您就可以比较以查看何时进行了更改。
I'd prefer scripts as well -- easy to store in source control (CVS, Git, etc) so you can diff to see when changes were made.
我不信任基于差异的部署工具。其中包括 vsdbcmd .schema 文件，因为它们也是基于 diff 的。上次我尝试使用 diff 工具时，它很高兴地提出通过复制/删除/重命名来更改 1.5 TB 表...
我的方法是始终使用 升级脚本，将部署的架构从 v.
N移动到 v.N+1。这样我就可以准确知道升级是如何完成的，并且如果无法执行某个操作（需要持续 2 周的数据大小复制操作......），那么我知道我不能这样做它和我相应地计划为 v.Next 的发布更改我的代码。I don't trust diff based tools for deployment. And that includes vsdbcmd .schema files, since they are also diff based. Last time I tried to use a diff tool it joyfully offered to change a 1.5 TB table via copy/drop/rename...
My approach is to always use upgrade scripts that move the deployed schema from v.
Nto v.N+1. This way I know exactly how is the upgrade done, and if an operation is not possible (it would require a size-of-data copy operation lasting 2 weeks...) then I know I cannot do it and I plan my code changes for the release of v. Next accordingly.我有一个开源（在 LGPL 下许可）工具集项目，它试图解决与（及更多）SQL Server (2005/2008/Azure) 的正确数据库架构版本控制相关的问题，bsn ModuleStore。
基本上，该工具集的独立部分将数据库模式的 SQL Server DB 对象编写为应用了标准格式的文件，这样文件内容仅在对象确实发生更改时才更改（与 VS 完成的脚本形成鲜明对比） ，它还编写了一些脚本日期等，将所有对象标记为已更改，即使它们实际上是相同的）。
但如果您使用 .NET，该工具集的功能远不止于此：它允许您将 SQL 脚本嵌入到库或应用程序中（作为嵌入资源），然后将嵌入的脚本与数据库中的当前状态进行比较。与表无关的更改（根据 Martin Fowler 的定义，这些更改不是“破坏性更改”）可以自动或根据请求应用（例如，创建和删除视图、函数、存储过程、类型、索引等对象），并且更改脚本（尽管需要手动编写）也可以在同一进程中应用；还会创建新表，并可选择与其设置数据一起创建。更新后，数据库架构会再次与脚本进行比较，以确保在提交更改之前数据库升级成功。
请注意，整个脚本和比较代码无需 SMO 即可工作，因此在应用程序中使用 bsn ModuleStore 时，您不会遇到令人痛苦的 SMO 依赖关系。
根据您想要访问数据库的方式，该工具集提供了更多功能 - 它实现了一些 ORM 功能，并提供了一种非常好的且有用的基于接口的方法来调用存储过程，包括对带有本机 .NET XML 类的 XML 的透明支持，以及TVP（表值参数）为
IEnumerable。I have an open-source (licensed under LGPL) toolset project which tries to address the issues related to proper DB schema versioning for (and more) SQL Server (2005/2008/Azure), the bsn ModuleStore.
Basically, the standalone part of the toolset scripts the SQL Server DB objects of a DB schema into files with a standard formatting applied, so that the file contents only changes if the object really did change (very much in contrast to the scripting done by VS, which scripts some scripting date etc. as well, marking all objects as changed even if they are in fact identical).
But the toolset goes beyond that if you use .NET: it allows you to embed the SQL scripts into the library or application (as embedded resources) and then have it compare the embedded scripts with the current state in the database. Non-table-related changes (those that are not "destructive changes" as per Martin Fowler's definition) can be applied automatically or on request (e.g. creating and removing objects such as views, functions, stored procedures, types, indexes), and change scripts (which need to be written manually though) can be applied in the same process as well; new tables are also created, optionally along with their setup data. After the update, the DB schema is again compared against the scripts in order to ensure a successful DB upgrade before the changes are committed.
Note that the whole scripting and comparison code works without SMO, so that you don't have the painful SMO dependency when using the bsn ModuleStore in applications.
Depending on how you want to access the database, the toolset offers even more - it implements some ORM capabilities and offers a very nice and useful interface-based approach to invoke stored procedures, including transparent support for XML with native .NET XML classes and also for TVPs (Table-Valued Parameters) as
IEnumerable<PocoClass>.SQL Compare 可以生成可在应用之前独立审查的 SQL 迁移脚本，还可以提供在工具内执行脚本的选项。 Red Gate 建议在部署到生产数据库时使用前一种方法。
对于数据库版本控制，SQL 源代码控制支持大多数源代码控制系统（例如，SVN、TFS 等，尽管 VSS 支持已被弃用）。在 v3 中，有一个链接到工作文件夹的选项，允许您在需要时使用自己的版本控制客户端。
SQL Compare can either generate a SQL migration script that can be independently reviewed before applying it, but also gives the option to execute the script within the tool. Red Gate recommends using the former method when deploying to production databases.
For database versioning, SQL Source Control support most source control systems (eg, SVN, TFS, etc, although VSS support has been deprecated). There is, in v3, an option to link to a working folder, allowing you to use your own version control client if desirable.
我们使用比较工具作为部署过程的一部分，以查看是否缺少任何需要脚本的内容，然后与开发人员讨论（如果有）（通常这是一个未签入部署位置的差异，因为它不应该）不会被感动去生产）。但我们始终从源代码控制中的脚本进行部署。如果您依赖 SQL Compare 或任何其他比较工具，您可能会发现自己正在移动不应该移动的内容。
We use a compare tool as part of our deployment process to see if anything needing a script is missing and then go discuss it with the developer if so (usually it is a differnce that isn't checked in to the deplyment location because it shouldn't be moved to go to prod). But we deploy from scripts that are in source control always. If you rely on SQL Compare or any other compare tool, you may find yourself moving things that should not yet be moved.