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

24
2014-04
  • niico

    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

  • Answers
  • amhed

    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

    enter image description here

  • Sean Kenny

    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.

  • Khalid Abuhakmeh

    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.


  • Related Question

    entity framework 5 - Automatic Migrations for ASP.NET SimpleMembershipProvider
  • Randolf R-F

    So I tried to use automatic migrations with my new MVC 4 Project but somehow it isn't working. I followed this blog post step by step.

    I've added the changes to the UserProfile account model (the NotaryCode field):

    [Table("UserProfile")]
    public class UserProfile
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int UserId { get; set; }
        public string UserName { get; set; }
        public int NotaryCode { get; set; }
    }
    

    Then I wrote on the package manager console enable-migrations and a Configuration class appeared (inherits from DbMigrationsConfiguration<Web.Models.UsersContext>) then I fill the class as:

    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
    }
    
    protected override void Seed(Atomic.Vesper.Cloud.Web.Models.UsersContext context)
    {
        WebSecurity.InitializeDatabaseConnection(
                "DefaultConnection",
                "UserProfile",
                "UserId",
                "UserName", autoCreateTables: true);
    
        if (!Roles.RoleExists("Atomic"))
            Roles.CreateRole("Atomic");
    
        if (!Roles.RoleExists("Protocolista"))
            Roles.CreateRole("Protocolista");
    
        if (!Roles.RoleExists("Cliente"))
            Roles.CreateRole("Cliente");
    
        string adminUser = "randolf";
    
        if (!WebSecurity.UserExists(adminUser))
            WebSecurity.CreateUserAndAccount(
                adminUser,
                "12345",
                new { NotaryCode = -1 });
    
        if (!Roles.GetRolesForUser(adminUser).Contains("Atomic"))
            Roles.AddUsersToRoles(new[] { adminUser }, new[] { "Atomic" });
    }
    

    And then I tried to run update-database -verbose but this doesn't work. I mean, this is the output:

    There is already an object named 'UserProfile' in the database.

    PM> update-database -verbose
    Using StartUp project 'Web'.
    Using NuGet project 'Web'.
    Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
    Target database is: 'VesperCloud' (DataSource: .\SQLSERVER, Provider: System.Data.SqlClient, Origin: Configuration).
    No pending code-based migrations.
    Applying automatic migration: 201211051825098_AutomaticMigration.
    CREATE TABLE [dbo].[UserProfile] (
        [UserId] [int] NOT NULL IDENTITY,
        [UserName] [nvarchar](max),
        [NotaryCode] [int] NOT NULL,
        CONSTRAINT [PK_dbo.UserProfile] PRIMARY KEY ([UserId])
    )
    System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'UserProfile' in the database.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
       at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
       at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto)
       at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
       at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
       at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
       at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
       at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
       at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
       at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
       at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
    ClientConnectionId:a7da0ddb-bccf-490f-bc1e-ecd2eb4eab04
    **There is already an object named 'UserProfile' in the database.**
    

    I know the object exists. I mean, I'm try to use automatic-migrations to, precisely, modify and run without recreating manually the DB. But somehow this isn't working.

    I look the MSDN documentation and found the property:

    AutomaticMigrationDataLossAllowed = true;
    

    But setting it to true doesn't change anything. I guess I'm missing something but somehow doesn't find what. Any idea?


  • Related Answers
  • kdenney

    update-database -verbose doesn't work because your model has been changed after your data table already existed.

    First, make sure there are no changes to the UserProfile class. Then, run:

    Add-Migration InitialMigrations -IgnoreChanges

    This should generate a blank "InitialMigration" file. Now, add any desired changes to the UserProfile class. Once changes are added, run the update command again:

    update-database -verbose

    Now the automatic migration will be applied and the table will be altered with your changes.

  • lukew

    What it looks like happened here is that you enabled migrations, then ran the application. By running the application before using the UpdateDatabase command, EntityFramework would have created and populated the database but since when you enabled migrations the database didn't exist, it didn't create the InitialCreate migration. Migrations still thinks that you have an empty database and wants to create all of the objects in your model

    What you can try is to either re-enable migrations which will generate an InitialCreate migration that reflects the current state of the database. In this case I would save the changes you made to the seed method than run "Enable-Migrations -Force", this should recreate the migration and generate an IntialCreate migration. You can then repopulate your seed method and run the UpdateDatabase command.