C# 可以使用 EF 迁移更新生产数据库吗？

Is it OK to update a production database with EF migrations?

提问by Adrian Grigore

According to this blog postmost companies using EF Migrations are supposedly not updating the database schema of production databases with EF migrations. Instead the blog post's author recommends to use Schema update scripts as part of the deployment process.

根据这篇博客文章，大多数使用 EF 迁移的公司应该不会使用 EF 迁移更新生产数据库的数据库架构。相反，博客文章的作者建议在部署过程中使用架构更新脚本。

I've used Schema update scripts for a few years now and while they work, I was planning to use EF migrations instead in the future for the following reasons:

我已经使用架构更新脚本几年了，当它们工作时，我计划在将来使用 EF 迁移，原因如下：

• Faster deployment, less downtime
• A simpler deployment procedure
• Much easier migration of existing data than it would be possible with T-SQL
• A more comprehensible syntax of the changes waiting to be applied (DbMigration class with clean C# syntax vs. clunky T-SQL Migration script in a traditional environment).
• There is an easy and fast downgrade path to the old db schema if the deployment of the new software version should fail

• 更快的部署，更少的停机时间
• 更简单的部署过程
• 现有数据的迁移比使用 T-SQL 容易得多
• 等待应用的更改的更易于理解的语法（具有干净 C# 语法的 DbMigration 类与传统环境中笨重的 T-SQL 迁移脚本）。
• 如果新软件版本的部署失败，有一个简单快速的降级路径到旧的数据库模式

One reason I can think of that would prohibit the use of EF to migrate a production DB would be if the DB schema was only altered by the DBAs as opposed to the Developers. However, I am both DBA and Developer, so this does not matter in my case.

我认为禁止使用 EF 迁移生产数据库的一个原因是，如果数据库架构仅由 DBA 而不是由开发人员更改。但是，我既是 DBA 又是开发人员，所以这对我来说并不重要。

So, what are the risks of updating a production database using EF?

那么，使用 EF 更新生产数据库有哪些风险？

Edit: I would like to add that, as solomon8718 already suggested, I am always pulling a fresh copy of the production database to my staging server and test the EF Migrations to be applied on the staging server before applying them to a production server. IMO this is essential for any schema update to a production system, whether I'm using EF migrations or not.

编辑：我想补充一点，正如 solomon8718 已经建议的那样，我总是将生产数据库的新副本拉到我的登台服务器，并在将它们应用到生产服务器之前测试要应用于登台服务器的 EF 迁移。IMO 这对于生产系统的任何架构更新都是必不可少的，无论我是否使用 EF 迁移。

采纳答案by DrewJordan

Well, I'll try and answer anyhow. I would say No, there's no reason not to use Code First Migrations in production. After all, what's the point of this easy to use system if you can't take it all the way?

好吧，无论如何我都会尝试回答。我会说不，没有理由不在生产中使用 Code First 迁移。毕竟，如果您不能一路接受，那么这个易于使用的系统有什么意义呢？

The biggest problems I see with it are all problems that you can have with any system, which you've noted already. As long as the whole team (DBA included if applicable) is on board with it, I think allowing EF to manage the schema through migrations is less complex, and hence less error-prone than traditional script-based management. I would still take a backup before performing a migration on a production system, but then you'd do that anyhow.

我看到的最大问题是您已经注意到的任何系统都可能遇到的所有问题。只要整个团队（包括 DBA，如果适用）都参与其中，我认为允许 EF 通过迁移来管理架构并不那么复杂，因此比传统的基于脚本的管理更不容易出错。在生产系统上执行迁移之前，我仍然会进行备份，但无论如何你都会这样做。

There's nothing that says a DBA can't perform a migration from Visual Studio, either. The access could still be locked down with privileges at the database level, and he/she could review the migration (in a helpful SQL export format using -Script, if desired) before performing the actual operation. Then they're still in control, but you can use code-first migrations. Hell, they might even end up liking it!

也没有什么说 DBA 不能从 Visual Studio 执行迁移的。访问仍然可以通过数据库级​​别的特权锁定，并且他/她可以-Script在执行实际操作之前查看迁移（如果需要，可以使用有用的 SQL 导出格式）。然后它们仍然处于控制之中，但您可以使用代码优先迁移。见鬼，他们甚至可能会喜欢它！

Update: since SPROCs and TVFs were brought up, we handle those in migrations as well, although they are actually done with straight-up SQL statements using a DbMigration.Sql()call in the Up(), and the reverse of them in the Down()(You can also use CreateStoredProcedureand DropStoredProcedurefor simple SPROCs, but I think you still have to define the body itself in SQL). I guess you could say that's a caveat; there isn't yet a way for an entire, comprehensive database to be written purely in C#. However, you canuse migrations which include SQL scripts to manage the entire schema. One benefit we've found from this process is you can use the C# config file for schema object names (different server names for production vs dev for example) with a simple String.Format, combined with XML Transformation for the config files themselves.

更新：由于 SPROC 和 TVF 被提出，我们也在迁移中处理它们，尽管它们实际上是使用 中DbMigration.Sql()调用的直接 SQL 语句完成的Up()，而它们在 中的反向调用Down()（您也可以使用CreateStoredProcedure和DropStoredProcedure用于简单的SPROC，但我认为您仍然必须在 SQL 中定义主体本身）。我想你可以说这是一个警告；目前还没有办法完全用 C# 编写一个完整的综合数据库。但是，您可以使用包含 SQL 脚本的迁移来管理整个架构。我们从这个过程中发现的一个好处是，您可以使用 C# 配置文件作为模式对象名称（例如，用于生产与开发的不同服务器名称）通过一个简单的String.Format，结合配置文件本身的 XML 转换。

回答by Yashvit

I use it in production for a couple of projects. Once you get the hang of it I think it's fine.

我在几个项目的生产中使用它。一旦你掌握了它，我认为这很好。

During development you can keep auto migrations on but at the end you can connect to the live db right from package manager console and generate a migration. It will give you one migration for all the changes.

在开发过程中，您可以保持自动迁移，但最后您可以直接从包管理器控制台连接到实时数据库并生成迁移。它将为您提供所有更改的一次迁移。

But always always always use the -scriptoption with update-databaseand fire the SQL yourself.

但始终始终使用-scriptwith 选项update-database并自己触发 SQL。

I would also advice not using the update db option from web deploy. That way there is no way to tell how much of the migration has already been fired on error. I've ran into trouble with that a few times. So best to get the SQL and fire it manually.

我还建议不要使用 web deploy 中的 update db 选项。这样就无法知道有多少迁移已经被错误触发了。我遇到过几次这样的麻烦。所以最好获取 SQL 并手动触发它。

回答by MplsAmigo

Yes there are good reasons notto use an automated system such as Code First Migrations to make productiondatabase changes. But as always there are exceptions to the rules.

是的，有充分的理由不使用诸如 Code First Migrations 之类的自动化系统来更改生产数据库。但与往常一样，规则也有例外。

1. One reason which has been mentioned would be access permissions, which would be directly related to your organization's change management rules and security policies.

2. Another reason would be your level of trust in the Migrations tool itself. Are we sure the tool doesn't have a bug in it? What happens if the tool fails midway through? Are you certain you have up-to-date backups and a process to roll-back if need be?

3. The change scripts may execute unexpected or inefficient scripts. I've experienced cases where the sql generated copied the data into a temp table, dropped the original table, then recreated the original table for things like adding a new column if you accidentally (or purposefully) change the order in which the column appears, or when you rename the table. If millions of records are involved this could cause serious performance issues.

1. 提到的一个原因是访问权限，这与您组织的变更管理规则和安全策略直接相关。

2. 另一个原因是您对迁移工具本身的信任程度。我们确定该工具没有错误吗？如果工具中途出现故障怎么办？您确定您有最新的备份和在需要时回滚的过程吗？

3. 更改脚本可能会执行意外或低效的脚本。我遇到过这样的情况，即 sql 生成的数据将数据复制到临时表中，删除原始表，然后重新创建原始表用于添加新列，如果您不小心（或故意）更改列出现的顺序，或者当您重命名表时。如果涉及数百万条记录，这可能会导致严重的性能问题。

My recomendation:

我的建议：

Assuming you have a Staging database that mirrors your production schema, use the Migrations tool to generate its change scripts against that system. We usually restore our stage database from a fresh production copy before running. We then examine the change scripts manually to check for issues. After that we run the scripts against our stage database to make sure it executes properly and that all the changes expected took place. Now we are sure that the scripts are both safe to run in production and perform the expected changes. This process would address all three issues I listed above.

假设您有一个反映生产模式的临时数据库，请使用迁移工具针对该系统生成其更改脚本。我们通常在运行之前从新的生产副本恢复我们的阶段数据库。然后我们手动检查更改脚本以检查问题。之后，我们针对我们的 stage 数据库运行脚本以确保它正确执行并且所有预期的更改都发生了。现在我们确信脚本在生产环境中运行是安全的，并且可以执行预期的更改。这个过程将解决我上面列出的所有三个问题。

回答by Adrian Grigore

One other caveat I found: If you have several websites using the same data context, you need to make sure that all of them are updated at the same time. Otherwise there might be a constant database update / downgrade fight between the websites. Other than that, it worked fine for me.

我发现的另一个警告：如果您有多个网站使用相同的数据上下文，则需要确保所有网站同时更新。否则，网站之间可能会发生持续的数据库更新/降级斗争。除此之外，它对我来说效果很好。

EDIT: My own perspective one year after starting to use EF Migrations in production:

编辑：在开始在生产中使用 EF Migrations 一年后，我自己的观点：

EF Migrations is actually pretty cool, even for production use, provided that you

EF Migrations 实际上非常酷，即使用于生产用途，前提是您

1. Test the migrations on a staging system. I test all migrations by migrating all the way down and up again on my CI server before running integration tests.
2. Do not trigger migrations automatically, but with a batch file that is launched by an admin. This is essentially the same as running the sql for a migration manually in SSMS.

1. 在登台系统上测试迁移。在运行集成测试之前，我通过在 CI 服务器上一直向下和向上迁移来测试所有迁移。
2. 不要自动触发迁移，而是使用由管理员启动的批处理文件。这本质上与在 SSMS 中手动运行 sql 进行迁移相同。