C# 数据库部署策略 (SQL Server)

Database Deployment Strategies (SQL Server)

提问by cgreeno

I am looking for a way to do daily deployments and keep the database scripts in line with releases.

我正在寻找一种方法来进行日常部署并使数据库脚本与版本保持一致。

Currently, we have a fairly decent way of deploying our source, we have unit code coverage, continuous integration and rollback procedures.

目前，我们有一种相当不错的方式来部署我们的源代码，我们有单元代码覆盖率、持续集成和回滚程序。

The problem is keeping the database scripts in line with a release. Everyone seems to try the script out on the test database then run them on live, when the ORM mappings are updated (that is, the changes goes live) then it picks up the new column.

问题是使数据库脚本与版本保持一致。每个人似乎都在测试数据库上尝试脚本，然后在实时运行它们，当 ORM 映射更新（即更改生效）时，它会选择新列。

The first problem is that none of the scripts HAVE to be written anywhere, generally everyone "attempts" to put them into a Subversion folder but some of the lazier people just run the script on live and most of the time no one knows who has done what to the database.

第一个问题是，没有一个脚本必须在任何地方编写，通常每个人都“尝试”将它们放入 Subversion 文件夹，但一些懒惰的人只是在现场运行脚本，大多数时候没有人知道谁做了什么数据库。

The second issue is that we have 4 test databases and they are ALWAYS out of line and the only way to truly line them back up is to do a restore from the live database.

第二个问题是我们有 4 个测试数据库，它们总是不合时宜，真正将它们对齐的唯一方法是从实时数据库中进行还原。

I am a big believer that a process like this needs to be simple, straightforward and easy to use in order to help a developer, not hinder them.

我坚信这样的过程需要简单、直接且易于使用，以帮助开发人员，而不是阻碍他们。

What I am looking for are techniques/ideas that make it EASY for the developer to want to record their database scripts so they can be ran as part of a release procedure. A process that the developer would want to follow.

我正在寻找的是使开发人员想要记录他们的数据库脚本变得容易的技术/想法，以便它们可以作为发布过程的一部分运行。开发人员希望遵循的流程。

Any stories, use cases or even a link would helpful.

任何故事、用例甚至链接都会有所帮助。

采纳答案by Ben Scheirman

For this very problem I chose to use a migration tool: Migratordotnet.

对于这个问题，我选择使用迁移工具： Migratordotnet。

With migrations (in any tool) you have a simple class used to perform your changes and undo them. Here's an example:

使用迁移（在任何工具中），您有一个简单的类来执行您的更改并撤消它们。下面是一个例子：

[Migration(62)]
public class _62_add_date_created_column : Migration
{
    public void Up()
    {
       //add it nullable
       Database.AddColumn("Customers", new Column("DateCreated", DateTime) );

       //seed it with data
       Database.Execute("update Customers set DateCreated = getdate()");

       //add not-null constraint
       Database.AddNotNullConstraint("Customers", "DateCreated");
    }

    public void Down()
    {
       Database.RemoveColumn("Customers", "DateCreated");
    }
}

This example shows how you can handle volatile updates, like adding a new not-null column to a table that has existing data. This can be automated easily, and you can easily go up and down between versions.

此示例展示了如何处理易失性更新，例如向具有现有数据的表添加新的非空列。这可以轻松自动化，您可以轻松地在版本之间上下移动。

This has been a really valuable addition to our build, and has streamlined the process immensely.

这对我们的构建来说是一个非常有价值的补充，并且极大地简化了流程。

I posted a comparison of the various migration frameworks in .NET here: http://benscheirman.com/2008/06/net-database-migration-tool-roundup

我在这里发布了 .NET 中各种迁移框架的比较：http: //benscheirman.com/2008/06/net-database-migration-tool-roundup

回答by Hamish Smith

Read K.Scott Allen's series of posts on database versioning.
We built a tool for applying database scripts in a controlled manner based on the techniques he describes and it works well.
This could then be used as part of the continuous integration process with each test database having changes deployed to it when a commit is made to the URL you keep the database upgrade scripts in. I'd suggest having a baseline script and upgrade scripts so that you can always run a sequence of scripts to get a database from it's current version to the new state that is needed.
This does still require some process and discipline from the developers though (all changes need to be rolled into a new version of the base install script and a patch script).

阅读K.Scott Allen 关于数据库版本控制的系列文章。
我们基于他描述的技术构建了一个以受控方式应用数据库脚本的工具，并且运行良好。
然后，这可以用作持续集成过程的一部分，当对保留数据库升级脚本的 URL 进行提交时，每个测试数据库都部署了更改。我建议使用基线脚本和升级脚本，以便您始终可以运行一系列脚本来将数据库从当前版本转换为所需的新状态。
尽管如此，这仍然需要开发人员的一些过程和纪律（所有更改都需要滚动到新版本的基本安装脚本和补丁脚本中）。

回答by Robert S.

You should consider using a build tool like MSBuild or NAnt. We use a combination of CruiseControl.NET, NAnt, and SourceGear Fortress to handle our deployments, including SQL objects. The NAnt db build task calls sqlcmd.exe to update scripts in our dev and staging environments after they're checked into Fortress.

您应该考虑使用诸如 MSBuild 或 NAnt 之类的构建工具。我们使用 CruiseControl.NET、NAnt 和 SourceGear Fortress 的组合来处理我们的部署，包括 SQL 对象。NAnt db 构建任务调用 sqlcmd.exe 来更新我们的开发和登台环境中的脚本，然后将它们签入 Fortress。

回答by Clyde

Go here:

到这里：

https://blog.codinghorror.com/get-your-database-under-version-control/

https://blog.codinghorror.com/get-your-database-under-version-control/

Scroll down a bit to the list of 5 links to the odetocode.com website. Fantastic five-part series. I would use that as a starting point to get ideas and figure out a process that will work for your team.

向下滚动到 odetocode.com 网站的 5 个链接列表。神奇的五部分系列。我会以此为起点来获得想法并找出适合您团队的流程。

回答by Mike

If you are talking about trying to keep database schemas in sync, try using Red Gate SQL Comparison SDK. Build a temp database based on a create script (newDb) - this is what you want your database to look like. Compare newDb against your old database (oldDb). Get a change set from that comparison and apply it using Red Gate. You could build this upgrade process into you tests, and you can try and get all the devs to agree that there is one place where the create script for the database is kept. This same practice works well for upgrading your database across several versions and running data migration scripts and processes between each step (using an XML doc to map the create and data migration scripts)

如果您正在谈论尝试保持数据库模式同步，请尝试使用Red Gate SQL 比较 SDK。基于创建脚本 (newDb) 构建临时数据库 - 这就是您希望数据库的外观。将 newDb 与旧数据库 (oldDb) 进行比较。从该比较中获取更改集并使用 Red Gate 应用它。您可以将这个升级过程构建到您的测试中，并且您可以尝试让所有开发人员同意在一个地方保存数据库的创建脚本。同样的做法适用于跨多个版本升级数据库以及在每个步骤之间运行数据迁移脚本和流程（使用 XML 文档来映射创建和数据迁移脚本）

Edit: With Red Gate technique, you only are concerned with create scripts, not upgrade scripts since Red Gate comes up with the upgrade script. It will also let you drop and create indexes, stored procedures, functions, etc.

编辑：使用 Red Gate 技术，您只关心创建脚本，而不是升级脚本，因为 Red Gate 提供了升级脚本。它还可以让您删除和创建索引、存储过程、函数等。

回答by Mike K.

We've been using SQL Compare from RedGate for a few years now:

几年来，我们一直在使用 RedGate 的 SQL Compare：

http://www.red-gate.com/products/index.htm

http://www.red-gate.com/products/index.htm

The pro version has a command line interface that you could probably use to setup your deployment procedures.

专业版有一个命令行界面，您可能可以使用它来设置部署过程。

回答by Todd Smith

We use a modified version of the database versioning described by K. Scott Allen. We use the Database Publishing Wizardto create the original baseline script. Then a custom C# tool based on SQL SMO to dump the stored procedures, views and user functions. Change scripts which contain schema and data changes are generated by Red Gatetools. So we end up with a structure like

我们使用K. Scott Allen描述的数据库版本控制的修改版本。我们使用数据库发布向导来创建原始基线脚本。然后是一个基于 SQL SMO 的自定义 C# 工具来转储存储过程、视图和用户函数。包含架构和数据更改的更改脚本由Red Gate工具生成。所以我们最终得到一个结构

Database\
    ObjectScripts\ - contains stored procs, views and user funcs 1-per file
    \baseline.sql - database snapshot which includes tables and data
    \sc.01.00.0001.sql - incremental change scripts
    \sc.01.00.0002.sql
    \sc.01.00.0003.sql

The custom tool creates the database if necessary, applies the baseline.sql if necessary, adds a SchemaChanges table if necessary and applies the change scripts as necessary based on what's in the SchemaChanges table. That process occurs as part of a nant build script each time we do a deployment build via cc.net.

自定义工具在必要时创建数据库，在必要时应用基线.sql，在必要时添加 SchemaChanges 表，并根据 SchemaChanges 表中的内容根据需要应用更改脚本。每次我们通过 cc.net 进行部署构建时，该过程都会作为 nant 构建脚本的一部分发生。

If anyone wants the source code to the schemachanger app I can throw it up on codeplex/google or wherever.

如果有人想要 schemachanger 应用程序的源代码，我可以将它放在 codeplex/google 或任何地方。

回答by Philip Kelley

There are a bunch of links in these posts that I'll want to follow up on (I "rolled my own" system years ago, have to see if there are similarities). One thing you will need, and that I hope is mentioned in these links, is discipline. I don't quite see how any automated system can work if anyone can change anything at any time. (Your question implies that this can happen on your production systems, but obviously that can't be true.)

这些帖子中有很多链接我想跟进（我几年前“推出了自己的”系统，必须看看是否有相似之处）。您将需要的一件事是纪律，我希望在这些链接中提到这一点。如果任何人都可以随时更改任何内容，我不太明白任何自动化系统如何工作。（您的问题暗示这可能发生在您的生产系统上，但显然这不可能。）

Having one person (the fabled "database administrator") dedicated to the task of managing changes to databases, particularly production databases, is a very common solution. As for maintaining consistency across X development and testing databases: if it/they are used by many users, once again you are best served by having an individual act as a "clearing house" for changes; if everyone has their own database instance, then they're responsible for keeping it in order, and having a central consistent database "source" will be critical when they need a refreshed baseline database.

让一个人（传说中的“数据库管理员”）专门负责管理对数据库（尤其是生产数据库）的更改是一种非常常见的解决方案。至于在 X 开发和测试数据库之间保持一致性：如果它/它们被许多用户使用，那么最好让个人充当更改的“信息交换所”；如果每个人都有自己的数据库实例，那么他们有责任将它保持有序，当他们需要更新的基线数据库时，拥有一个中央一致的数据库“源”将是至关重要的。

Here's a recent Stack Overflow post that may be of interest: how-to-refresh-a-test-instance-of-sql-server-with-production-data-without-using

这是最近的 Stack Overflow 帖子，您可能会感兴趣：how-to-refresh-a-test-instance-of-sql-server-with-production-data-without-using

回答by Gus

The book Refactoring Databasesaddresses many of these issues at a conceptual level.

《重构数据库》一书在概念层面解决了许多这些问题。

As far as tools go, I know that DB Ghostworks well for SQL Server. I have heard that the Data Dudeedition of Visual Studio has really been imporved upon in the latest release but I don't have any experience with it.

就工具而言，我知道DB Ghost适用于 SQL Server。我听说Visual Studio的Data Dude版本在最新版本中确实得到了改进，但我没有任何经验。

As far as really pulling off continuous integration style database development, it gets really resource instensive really fast because of the number of database copies you need. It is very doable when the database can fit on a developer workstation but impractical when the database is so large that it needs to be deployed across a grid. To do it you bacically need 1 copy of the database per developer [developers who make DDL changes, not just changes to procs] + 6 common copies. The common copies are as follows:

就真正实现持续集成风格的数据库开发而言，由于您需要大量的数据库副本，它会非常快速地消耗大量资源。当数据库适合开发人员工作站时，这是非常可行的，但当数据库太大以至于需要跨网格部署时，这是不切实际的。要做到这一点，您基本上需要每个开发人员 1 个数据库副本 [进行 DDL 更改的开发人员，而不仅仅是对 proc 的更改] + 6 个公共副本。常见的副本如下：

1. INT DEV --> Developers "check in" their refactoring to INT DEV for integration testing. When integration testing passes, this database is copied over to DEV.
2. DEV --> This is the "official" development copy of the database. INT DEV is refreshed regularly with a copy of DEV. Developers working on new refactorings get a fresh copy of the database from DEV.
3. INT QA --> Same idea as INT DEV except for the QA team. When integration tests pass here, this database is copied over to QA and to DEV*.
4. QA
5. INT PROD --> Same idea as INT QA except for production. When integration tests pass here, this database is copied over to PROD, QA*, and DEV*
6. PROD

1. INT DEV --> 开发人员将他们的重构“签入”到 INT DEV 以进行集成测试。当集成测试通过时，这个数据库被复制到 DEV。
2. DEV --> 这是数据库的“官方”开发副本。INT DEV 会定期更新 DEV 的副本。从事新重构的开发人员从 DEV 获得数据库的新副本。
3. INT QA --> 除了 QA 团队之外，与 INT DEV 的想法相同。当集成测试在这里通过时，这个数据库被复制到 QA 和 DEV*。
4. 质量保证
5. INT PROD --> 除了生产之外，与 INT QA 的想法相同。当集成测试通过这里时，这个数据库被复制到 PROD、QA* 和 DEV*
6. 生产线

*When copying databases across DEV/QA/PROD lines, you will also need to run scripts to update test data relevant to the particular environment (e.g. setting up users in QA that the QA team uses to test but that don't exist in production).

*当跨 DEV/QA/PROD 行复制数据库时，您还需要运行脚本来更新与特定环境相关的测试数据（例如，在 QA 中设置用户，QA 团队用来测试但在生产中不存在的用户） ）。

回答by Jerry Bullard

We use Visual Studio for Database Professionalsand TFS to version and manage our database deployments. This allows us to treat our databases just like code (check out, check in, lock, view version history, branch, build, deploy, test, etc.) and even include them in the same solution files if we wish.

我们使用Visual Studio for Database Professionals和 TFS 来控制和管理我们的数据库部署。这允许我们像对待代码一样对待我们的数据库（签出、签入、锁定、查看版本历史、分支、构建、部署、测试等），甚至如果我们愿意，甚至可以将它们包含在相同的解决方案文件中。

Our developers can work on local databases to avoid stepping on each other's changes in a shared environment. When they check database changes into TFS, we have continuous integration to build, test and deploy to our integrated dev environment. We have separate builds on release branches to create differential deployment scripts for each subsequent environment.

我们的开发人员可以在本地数据库上工作，以避免在共享环境中踩到彼此的更改。当他们将数据库更改检查到 TFS 时，我们有持续集成来构建、测试和部署到我们的集成开发环境。我们在发布分支上单独构建，为每个后续环境创建差异化部署脚本。

Later, if a bug is discovered in a release, we can go to a release branch and hotfix the code and database at the same time.

之后，如果在发布中发现错误，我们可以转到发布分支并同时修复代码和数据库。

This is a great product, but its adoption was hindered early on due to a Microsoft marketing blunder. It was originally a separate product under Team System. This meant in order to use features of the developer edition and database edition at the same time, you were required to step up to the much more expensive Team Suite edition. We (and many other customers) gave Microsoft grief about this, and we were very happy they announced this year that DB Pro has been folded into the developer edition, and that immediately anyone licensed with developer edition can install the database edition.

这是一款很棒的产品，但由于微软的营销失误，它的采用很早就受到了阻碍。它最初是 Team System 下的一个单独产品。这意味着为了同时使用开发者版和数据库版的功能，您需要升级到更昂贵的 Team Suite 版。我们（和许多其他客户）为此让 Microsoft 感到悲痛，我们很高兴他们今年宣布DB Pro 已合并到开发者版中，并且任何获得开发者版许可的人都可以立即安装数据库版。