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

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

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.