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

c# database-schema ef-migrations entity-framework

Question

According to this blog post most 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.

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:

  • 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

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.

So, what are the risks of updating a production database using 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.

1
63
4/20/2015 7:43:07 PM

Accepted Answer

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?

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.

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!

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 CreateStoredProcedure and DropStoredProcedure for 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 can use 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.

34
4/21/2015 1:23:07 PM

Popular Answer

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

  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.

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow