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

Answer 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

Answer 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>());            
    }

Answer 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.

Answer 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.

Answer 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.

Answer 6

You can get the scripts using EF commands (update-database -script) or you can write the script manually. This is not the most important thing about updating the database in production environment. For me, the most important thing is to be sure that all the scripts were run correctly and they have affected records as expected. In my opinion, you should have a preproduction environment and the database should be a copy of the production environment. This way, you can run the scripts and deploy the application in a pretty similar environment and see if there are any problems. Sometimes the scripts are executed correctly in DEV environment but they fail in production environment. To avoid a headache, you should simulate the production environment in a preproduction environment. Regarding the scripts, if the team has more than one developer I prefer to categorize the scripts in structure scripts and data scripts. Structure scripts alters the structure of the database (add a table, add a column to a table, etc.) and data scripts inserts/updates/deletes records. Also, each script should specify its dependencies so they cannot be executed in the wrong order. A data script that inserts rows in table A cannot be executed until table A has been created. This is what I do: -Define a table for registering the executed scripts. For example: ExecutedScriptsHistory. -Each script has a number and a name. -After a script is executed, a new row is inserted in table ExecutedScriptsHistory. -Before a script is executed, it checks its dependencies. In order to do that, it checks if the scripts have been executed (exists in table ExecutedScriptsHistory).

After you have run the scripts, you can check if all the scripts have been executed checking ExecutedScriptsHistory. This strategy is similar to the one chosen by Microsoft in EF Migration but you have full control of it.

Answer 7

just to give everyone the simple answer.

This is the "Update-Database" In your Migrations folder, Configuration.cs:

    internal sealed class Configuration : DbMigrationsConfiguration<projectname.Models.dbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true; // Update-Data -Force (deletes columns etc)
    }

And to "Enable migrations" in the first place on a remote server, add this to your Global.asax.cs file:

        protected void Application_Start()
    {
        ....
        System.Data.Entity.Database.SetInitializer(new MigrateDatabaseToLatestVersion<dbContext, Migrations.Configuration>());