ASP.NET MVC 4, Migrations - How to run 'update-database' on a production server

Question

I can use package manager to run 'update-database -verbose' locally.

Probably a stupid question but I can't find it online - once my website is deployed - how can I run this manually on the server?

Secondarily - what other strategies would you recommend for deploying database migrations to production - and how would they be preferable?

Thanks

Solution 1

You have a couple of options:

• You could use update-database -script to generate the SQL commands to update the database on the server
• You could use the migrate.exe executable file that resides in the package folder on /packages/EntityFramework5.0.0/tools/migrate.exe. I've used it successfully in the past with Jet Brains' Team City Build Server to setup the migrations with my deploy scripts.
• If you're using IIS Web Deploy you can tell the server to perform the migrations after publish (see pic below)
• You could setup automatic migrations, but I prefer to be in control of when things happen :)

Update: Also, check out Sayed Ibrahim's blog, he works on the MsBuild Team at Microsoft and has some great insights on deployments

Solution 2

I know that the question is already answered, but for future reference:

One of the options is to put something like this in the constructor of your DB context class:

public MyDbContext()
    {
        System.Data.Entity.Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDbContext, Configuration>());            
    }

Solution 3

For us, the DBAs are the only group to have access to the production (and pre-production) environments. We simply use the Update-Database -Script package console command to get the Sql required to update the database. This gets handed off to them where they can validate it, etc.

Maybe a little too simplistic for some but it works.

HTH.

Solution 4

A simple solution: running Update-Database from your local Package Manager Console providing a connection string parameter with the production connection string. You also have to provide the connection provider name (SqlServer in this example code):

Update-Database -ConnectionString <your real remote server connection string here> -ConnectionProviderName System.Data.SqlClient

Instead of the connection string you can use a connection string name present in your app.config file connectionStrings section:

Update-Database -ConnectionStringName <your connection string name here>

You must have permissions to access that server from your local machine. For example, if you are able to connect to the server from a Sql Server Management Studio you can use this.

Note that this approach is not recommended for a real production system, you should use something like what is explained in the accepted answer. But it can help you with quick hacks in development remote servers, test environments, etc.

Solution 5

I personally like to setup automatic migrations that run every time the application's start method is called. That way with every deployment you make you have the migrations just run and update the application automatically.

Check out this post from AppHarbor. http://blog.appharbor.com/2012/04/24/automatic-migrations-with-entity-framework-4-3

The gist is basically you want to enable auto migrations then call the DatabaseInitializer from your code, either from the OnModelCreating method or from your Global.asax.

View more solutions

Comments

[Deilan]

Although this approach gets the job done, definitely this line of code doesn't need to be executed every time MyDbContext instantiates. So if one is going to use this approach, it's worth to consider to put this code into the static constructor of MyDbContext class. Or in Application_Start method in Global.asax.cs file. Or somewhere else close enough to the composition root of an application.

[Michel]

be aware that in some versions of EF this could lead to data loss. There are versions of EF which in case of a column rename, drop the old column and create the new one. When you apply your code (especially the 'DataLossAllowed = true' part) you will end up losing data.

[Michel]

To be in control I always use this one "You could use update-database -script to generate the SQL commands to update the database on the server" (although I prefer to not use automatic migrations at all, but that's a different topic)

[trees_are_great]

Thank you. This simple solution made my day.

[nmit026]

Should be "System.Data.SqlClient", as a string, and the connection string is also obviously a string. Thanks! :)