Is it OK to update a production database with EF m

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.

Answer 1

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.

Answer 2

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.

Answer 3

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 is actually pretty cool, even for production use, provided that you

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.

Answer 4

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 -script option with update-database and fire the SQL yourself.

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.