Is it possible to update the production database using EF migrations?

Question

According to this blog post , most companies using EF Migrations do not presumably update the database schema of production databases with EF migrations. Instead, the blog author recommends using schema update scripts as part of the deployment process.

I have been using Schema update scripts for several years, and while they work, I planned to use EF migrations instead in the future for the following reasons:

• Faster deployment, less downtime
• Simple Deployment Procedure
• Migrating existing data is much easier than would be possible with T-SQL.
• A more understandable syntax for pending changes (DbMigration class with pure C # syntax or awkward T-SQL script migration in a traditional environment).
• There is a quick and easy way to upgrade to the old db scheme if deploying a new version of the software should fail.

One of the reasons I can think of banning the use of EF to migrate a production database would be because the DB schema was changed only by database administrators, not developers. However, I am both a DBA and a developer, so in my case it does not matter.

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

Edit: I would like to add that, as solomon8718 already said, I always pull out a new copy of the production database on my intermediate server and test EF Migrations for use on the intermediate server before applying them to the production server. IMO is important for any schema update for the production system, regardless of whether I use EF migrations or not.

Answer 1

Ok, I’ll try to answer anyway. I would say "No", there is no reason not to use the "First Code Migration" in production. After all, what's the point of this easy-to-use system if you cannot completely overcome it?

The biggest problems that I see are all the problems that can arise with any system that you have already talked about. As long as the entire team (DBA is enabled, if applicable) is on board, I think that allowing EF to manage the circuit using migrations is less complex and therefore less error prone than traditional script-based management. I would still take a backup before migrating to the production system, but then you do it anyway.

There is nothing that says the database administrator cannot migrate from Visual Studio. Access can still be locked with privileges at the database level, and he / she could view the migration (in the case of a useful SQL export format using -Script , if necessary) before performing the actual operation. Then they are still under control, but you can use the first code transformations. Damn, they can even love him!

Update: since SPROC and TVF were created, we also process them in migrations, although they are actually executed using SQL statements using the DbMigration.Sql() call in Up() , and vice versa in Down() (You can also use CreateStoredProcedure and DropStoredProcedure for simple SPROCs, but I think you still need to define the body in SQL). Perhaps you could say this reservation; there is as yet no opportunity for a complete, comprehensive database written exclusively in C #. However, you can use migrations that include SQL scripts to manage the entire schema. One of the advantages we found in this process is to use the C # configuration file for the names of the schema objects (for example, the names of different servers for production against dev) using a simple String.Format in combination with XML Transformation for the configuration files themselves.

Answer 2

Yes, there are good reasons not to use an automated system such as First First Migrations to modify a production database. But, as always, there are exceptions to the rules.

• One of the reasons mentioned is access rights, which will be directly related to the organization’s change management rules and security policies.

• Another reason may be your level of trust in the Migrations tool. Are we sure that there is no error in this tool? What happens if the instrument does not go halfway? Are you sure you have updated backups and a rollback process if necessary?

• Change scripts may execute unexpected or ineffective scripts. I experienced cases where sql generated the copied data in a temp table, deleted the original table, and then recreated the original table for things like adding a new column, if you accidentally (or purposefully) change the display order of the column, or when renaming the table. If millions of records are involved, this can cause serious performance problems.

My recommendation:

Assuming you have an intermediate database that reflects your production flow chart, use the Migrations tool to create change scripts for this system. We usually restore our scene database from a new production copy before launch. Then we check the change scripts manually to check for problems. After that, we run scripts with our scene database to make sure that it runs correctly and that all expected changes have occurred. Now we are confident that the scripts are safe to run in production and implement the expected changes. This process will focus on all three issues listed above.

Answer 3

Another caveat I discovered is that if you have multiple sites using the same data context, you need to make sure that they are all updated at the same time. Otherwise, between the sites there may be a constant update of the database / downgrade. Other than that, it worked great for me.

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

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

• Check migration in the intermediate system. I test all migrations by going all the way to the CI server back and forth before running integration tests.
• Do not start the migration automatically, but using a batch file launched by the administrator. This is essentially the same as starting sql for manual migration to SSMS.

Answer 4

I use it in production for several projects. As soon as you receive it, I think it is good.

During development, you can save automatic migration, but in the end you can connect to direct db directly from the package manager console and migrate. This will give you one migration for all changes.

But always always use the -script parameter with update-database and run SQL itself.

I would also advise against using the db update option from web deployment. Thus, it is not possible to determine how much of the migration has already been triggered by an error. I have come across a problem several times. Therefore, it is best to get SQL and run it manually.