Original link: https://www.example.com
Author: Jon P Smith
Practical application of EFCore's model in production
这是使用 EF Core 迁移数据库的系列文章中的第二篇。本文着眼于将迁移应用于数据库，并从第 1 部分开始，该部分介绍了如何创建迁移脚本。如果您还没有阅读第 1 部分，那么本文的某些部分将毫无意义，因此这里是第 1 部分的快速回顾。
- Two types of migrations can be applied to databases:
- Adding new tables, columns, etc. is called uninterrupted changes (simple).
- Changing a column/table and needing to copy the data is called a major change (difficulty).
- There are two main ways to apply migration to a database
- Use EF Core migration capabilities
- Create a migration using EF Core, and then modify the migration manually.
- Use a third-party migration builder to write migrations in C#.
- Use the SQL Database Compare tool to compare databases and output SQL change scripts.
- Write your own SQL migration script by copying EF Core SQL.
So, now that you now know how to create migration scripts, I will examine the different ways in which migration can be applied to production databases, and the pros and cons of these ways.
TL; DR-Executive Summary
Note: Click the link to go directly to the section that covers this point.
- You have the types of applications that affect the migration method can use.·You must consider possible mistakes and make plans.·There are four ways to apply migration to a database
- Context. Database. Migrate () Very Easy is called at startup, but there are some serious issues that limit its usefulness.
- Context.Database. Migrate ()-Easy is called through the console application and works well, especially in the deployment pipeline
- Output the EF Core migration as a SQL script and execute the script on the target database-Hard, but provides good control.
- Use the database migration application tool to apply your own SQL scripts-Hard, but you have great control.
- Three different levels of application migration.
- Stopping applications while migrating databases is the safest option, but it is not always possible.
- While the application is running, you can apply some, but not all, uninterrupted changes to the database.
- For continuous service applications (services that run 7* 24 hours a day), applying major changes requires five steps.
Scenario Analysis-What kind of application is your product?
In Part 1, we focus on creating "effective" migrations and whether the migration is an ongoing change or a major change (see the quick definition at the beginning of this article, or this link in Part 1.
Right now, we are considering applying migration to the database, but the options we have depend on the application or applications that are accessing the database. This is something you need to consider.
Is there only one application accessing the database, or is your application a scaled-out web application, that is, multiple versions of an application running simultaneously? If your application is horizontally scaled out, one of the options will be removed.
Can you stop the application when applying the migration to the database, or does your application provide continuous service 7* 24 hours a day? Updating continuous service applications presents some challenges in applying major changes.
When migrating production databases, it is okay to be a little paranoid.
As I said at the end of Part 1-the scariest part comes when you apply migration to a production database. Changes contain critical business data requirements (requirements!) , please plan and test carefully. You need to consider if (when!) What to do if the migration fails due to an error.
When considering different approaches to application migration, you should have "What would happen if there were mistakes?" in mind. This may prompt you to adopt a more complex migration method because it is easier to test or restore. I can't provide you with rules or suggestions because every system is different, but being a little paranoid about glitches is not a bad thing. I should let you build a more robust system for migrating applications and their databases.
Part 2: How to apply migration to a database.
The following list provides different ways to apply migration to a database. I listed three options for the EF Core case: the first is the simplest, but it has limitations that the other two options do not have. SQL migration has no practical limitations, but it does require a database migration application tool to apply SQL scripts in the correct order.
This is a list of methods you can apply migration.
EFCore migration
Call context. Database. Migrate () at startup
Call context.Database. Migrate () through a console application or administrative command
Use the migration output as SQL script output and execute the script on the target database.
SQL migration
Use the database migration application tool.
Finally, how migration is applied depends on the type of migration (interrupted or non-interrupted) and the type of application being updated (single application, multiple applications running in parallel, or applications that must be stopped). This is a chart of all these permutations.
The outer dark blue color indicates that SQL migration can be applied in all situations, while the inner lighter box indicates that different types of EF Core migration can be added to it. The following are some clarifications about the map:
- The figure shows a standard EF migration and a manually modified EF migration, but when I talk about application migration, there is no difference between the two-we simply apply EF Core migration.·The "Five-Phase Application Update" red boxes in the figure represent the complex stages you need to make major changes to an application that cannot be stopped. I will introduce it at the end of the article.
Now, I will go through each method of application migration in detail.
1a。Call context. Database. Migrate () at startup
This is by far the easiest way to migrate applications, but it has one big limitation-you should not run multiple instances of the Migrate method at the same time. This can happen if you scale your Web application horizontally. To quote Andrew Lock: "
我们不能保证这会给您带来麻烦，但是除非您非常谨慎地确保幂等更新和错误处理，否则您很可能会陷入困境
” –请参阅他的帖子的这一部分“ 在 ASP.NET Core 中的应用启动时运行异步任务”。
| recommendations | detailed |
|---|---|
| benefits | ·Relatively easy to implement (see Tips)·Ensure that the database is up to date before the application runs. |
| harm | ·You must not run two or more Migrate methods in parallel.·If there are errors in the migration, your application will not be available.·Difficulty diagnosing startup errors |
| limitations | Not applicable to continuous service systems |
| prompt | 我非常喜欢 Andrew Lock 的文章中的在启动时运行迁移的 选项 。我在一些使用内存数据库的演示系统中使用了类似的方法，这些数据库需要初始化（请参见本示例） |
| my advice | If you are running a single web application or similar web application and can update the system without anyone using it, this may be useful for you. I don't use horizontal scaling like many systems I use. |
1b。Call context.Database. Migrate () through a console application or administrative command
If you cannot run multiple Migrate methods in parallel, one way to ensure this method is to call the Migrate method within a separate application designed to execute only the Migrate method. You can add a console application project to your main web application solution that can access DbContext and call Migrate. You can either run it yourself or have your deployment system run it (note for EF6.x users-this is equivalent to running Migrate.exe, but with the application dll compiled in it).
| recommendations | detailed |
|---|---|
| benefits | It applies to all situations.·Works well with the deployment system. |
| harm | There is more work. |
| limitations | None-, but please note the ongoing five-phase application updates |
| prompt | If your console application uses a connection string to define which database to apply the migration to, it will be easier to use in the deployment pipeline. |
| my advice | If you have a deployment pipeline, this is a good choice because you can execute console applications during deployment. If you are applying migration manually, there is the command Update-Database. |
1c。Convert EF Core migration into scripts and apply it to the database
EF Core converts specific migrations or all migrations by default to SQL scripts by using the script migration command. You can then apply this method using a method that can execute SQL on the specific database you want to update. You can execute SQL manually in SQL Server Management Studio, but usually there is something in your release pipeline that can be executed at the right time.
| recommendations | detailed |
|---|---|
| benefits | It applies to all situations.·Works well with deployment systems that can use SQL scripts.·You can check SQL to see if it is normal before running it. |
| harm | More work than console applications (1b)·You need some application to apply scripts to the right database. |
| limitations | None-, but please note the ongoing five-phase application updates |
| prompt | SQL contains code to update migration history, but you must include the idepotent option in the Script-Migration command to get a check that prevents application migrations twice. |
| my advice | If you want to use EF Core's Migrate method, then I recommend using console application 1b. It is as safe as using scripts and performs the same work. However, if your pipeline is already ready to change scripts using SQL, this is perfect for you. |
2a。Apply SQL scripts using migration tools
If you create a series of SQL migration scripts, you need the following steps: a) apply them in the correct order, and b) apply them only once. Migration for EF Core includes code to enforce the "correct order" and "one-time" rules, but when we wrote our own migration scripts, we needed a tool that could provide these capabilities.
I and many others use an open source library called DbUp that provides these features (and more) and also supports multiple database types. I arranged the migration scripts in alphabetical order, such as "Script 0001-Initial Migration" and "Script 0002-Add Seed Data" for DbUp to apply. Just like EF Core migrations, DbUp uses a table to list which migrations have been applied to the database and only applies them if there are no migrations in the table.
There are also other migration tools available, such as Octopus Deploy and various RedGate tools (but I haven't used them, so please check that they have the correct functionality).
| recommendations | detailed |
|---|---|
| benefits | It applies to all situations. Works well with the deployment system. |
| harm | You must manage scripts. |
| limitations | None-, but please note that five phases of application updates are ongoing |
| Tip*(applies to DbUp) | I made a console application that accepted the connection string and then ran DbUp so I could use it in the deployment pipeline.·For testing, I made the method of running DbUp, which correctly migrated the local database using my CompareEfSql tool, available to my unit test assembly in the "Run in Debug Mode Only" unit test (see the section on test migration in Part 1 of this series. |
| my advice | This method is used on projects that use EF Core. |
Applications and application migration
When applying a migration to a database, you can stop the application, or in some cases, you can apply the migration while the migration is running. In this section, I will introduce the different options available to you.
1. Stop the application while migrating the database
This is the safest option and can be used with both major and non-disruptive changes, but your users and your business may not be so satisfied. I call it a "maintenance site". In the "site shutdown" method, you don't want to stop the application while users enter data or complete orders. This is how you or your company gains a bad reputation.
I had this problem back in 2015, and I created a way to warn people that the site was about to close, and then stop everyone except the administrator from accessing the application. I chose this approach because it is less costly for the Web application being used than supporting disruptive changes while keeping the Web application running (I'll talk about interrupting continuous service applications later). Usually on weekends and evenings, you may encounter "this site is closed for maintenance" for the service you use.
Note: I wrote an article titled "How to Make ASP.NET MVC Sites 'Shutdown for Maintenance'" that you may want to take a look at-the code is for ASP.NET MVC 5, so it takes some work to make it work properly. NET Core, but the idea still works.
Apply uninterrupted migration while the application is running
In theory, with uninterrupted changes, you can apply old applications to the database while they are running, but some issues may disappoint you. For example, if you add a new non-empty column from old software that has no SQL default values and is unaware of the new column, and try to insert a new row, you will receive a SQL error because the old software did not provide a value for the non-empty column.
However, if you know that there is no problem with uninterrupted migration, applying migration while old applications are running will provide continuous service to your users. There are multiple ways to do this, depending on which method you choose to migrate your applications, and what comes to mind are Azure's staging slots (which have been around for a long time) and updated Azure Pipelines.
Apply major changes to continuously running applications: Five-phase application updates.
The most difficult task is making major changes to a constantly running application. In the chart showing the different methods, a red box named "Five-Phase Application Update" appears at the top right. The name comes from the fact that you need to migrate in stages, usually five stages, as shown in the following figure.
Note: Andrew Lock praised the "adding non-nullable columns" problem I described in the previous section for being handled in three stages: a) adding a new column that can be nullable, b) deploying new software that knows the column, and c) changing the column to be non-nullable.
This is the chart from Section 11.5.3 of my book EFCore, which shows the five stages required to add major changes that divide the existing CustomerAndAddress table into two tables, Customers and Addresses.
As you can see, updates like this are complex to create and complex to apply, but that's the cost of running a continuous system. There are no real alternatives to these five phases, except that you should never apply major changes to a continuously running system (I've heard people say this is their approach).
注意：我在我的书“ Entity Framework Core in Action ”的 11.5.3 节中介绍了持续的，五个阶段的应用程序更新，您还可以在 Neil Ford 的“ Building Evolutionary Architectures ” 一书的第 5 章中找到有关此内容的内容。等。
conclusion
If the availability of data and services in the database is important to your organization, you must take database migration seriously. In Part 1, I covered different ways to create migration scripts, and this article describes how to apply these migrations to production databases. The purpose of this series of articles is to provide you with various options, as well as their pros and cons, so that you can make informed decisions about how to handle migration.
就像我在第一篇文章中所说的那样，我与 EF 迁移的第一个磨合是使用 EF6。我非常了解 EF6，并且写过《 Entity Framework Core in Action》一书我对 EF Core 的了解甚至更好。围绕迁移从 EF6 到 EF Core 的变化代表了 EF Core 中整个方法的变化。
EF6 does a number of "magic" actions to make it easier to use-automatic migration at startup is one of them. The problem is that when the EF6 's "magic" effect is not good, it is difficult to sort through it. EF Core's migration method is for you to decide how to where and how to use it-there is no automatic "magic". Many of the other small changes in the EF Core migration come from users listening to EF4 through 6.
Therefore, migration on production databases is scary. I have provided you with some insights on options, but this is only the minimum requirement for making changes to your production database. Backup, policies, pre-product testing and deployment pipelines need to be added as needed to build a reliable system.
I wish you the joy of coding!