数据库部署策略 (SQL Server) [英] Database Deployment Strategies (SQL Server)

问题描述

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

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.

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

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.

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

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.

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

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.

我坚信这样的过程需要简单、直接且易于使用，以便帮助而不是阻碍开发者.

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.

推荐答案

针对这个问题，我选择使用迁移工具:Migratordotnet.

For this very problem I chose to use a migration tool: 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.

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

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

这篇关于数据库部署策略 (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！