Seamless EF Migrations from Staging > Production with Schema Change

Question

I have a simple web app. It consists of an Azure Web App with Staging and Production slots. When there are no DB migrations to consider, I can easily achieve a seamless update by:

1. Deploy App to Staging
2. Swap Staging <> Production Slots

This gets trickier when I have a DB migration to handle. Right now what I do is:

1. Deploy App to Staging
2. When deployment is ready, run update-database to Prod (no staging database)
3. Swap Staging <> Production Slots

This means that I still effectively have downtime as 2 + 3 don't happen simultaneously, which means that for some seconds, my users will experience imperfect behavior as the 'DB schema has changed'.

What is the simplest solution here? I'm thinking I may have to spin up a staging database too, but then I have to worry about replication and connection string management which adds a bit of overhead.

Answer 1

We had the same dilemma when moving our solution to continuous delivery model and wanted to avoid downtime.

You need to configure your EF to run Code-First on development environment and Database-First in production.

This makes it possible to push your changes to live in three stages:

Stage 1. Database Migrations

At this stage, you will use EF's migrate.exe utility (or simply script them before hand) to run your latest migrations against the live database. After migrations are applied your website in production still keeps functioning as nothing has happened (because it's configured to be database-first).

The important bit is that you need to make sure your migrations at this stage are additive, in the sense that it would'd change a let's say table or column that will cause the live site to crash. It may look scary, but if your project is mature enough, you soon will realise that most of changes to your schema are either completely additive or can be broken down into two stages. (see stage 3)

Stage 2. Update production website

At this stage do your normal Staging --> Production website deployment.

Stage 3. Database Migrations (part 2)

In those rare cases where you had for example a database table or column renamed, you will need to consider breaking it into two steps of:

• Add a new column (done in part 1)
• Remove old column and migrate data (done in part 2).

---

Appendices

EF Database-First only in production

In your Startup.cs or Global.asax.cs:

#if DEBUG
    Database.SetInitializer(new MigrateDatabaseToLatestVersion<AppDatabase, Migrations.Migrations.Configuration>());
#else
    Database.SetInitializer(new RequireDatabaseToBeUpToDate<AppDatabase, Migrations.Migrations.Configuration>());
#endif

This does exactly what it says on the tin:

• On Local: Migrates it's database to latest migration.
• In Production: Ensures that the database migrations is NOT AHEAD of the model assembly it is using. -- this is a safety measure making sure even if we ever accidentally deployed web before database, it stops the site from firing up.

---

public class RequireDatabaseToBeUpToDate<TContext, TMigrationsConfiguration> : IDatabaseInitializer<TContext>
    where TContext : DbContext 
    where TMigrationsConfiguration : DbMigrationsConfiguration, new()
{
    public void InitializeDatabase(TContext context)
    {
        var migrator = new DbMigrator(new TMigrationsConfiguration());
        var migrations = migrator.GetPendingMigrations().ToList();
        if (migrations.Any())
        {
            var message = "There are pending migrations that must be applied (via a script or using migrate.exe) before the application is started.\r\n" +
                $"Pending migrations:\r\n{string.Join("\r\n", migrations)}";
            throw new MigrationsPendingException(message);
        }
    }
}

Running migrations against live database

$migrate = "<path>\migrate.exe"
$migrateConfig = "<path>\migrate.exe.config"
$connectionString = <your-live-connection-string>
& $migrate <your-project-migration-assembly> /startupConfigurationFile=$migrateConfig <your-migration-configuration-type-name> /connectionString=$connectionString /connectionProviderName=System.Data.SqlClient /verbose