EF Code First Migrations to Deploy Older Version

Question

I'm using TFS Release Management to do continuous integration and deployment.

I'm using migrate.exe to perform the database migration during deployment, and this works great when you're going from an older version to a newer version. When you want to deploy an older version of the application, however, it gets more muddy.

Basically, the assembly that holds your migrations for a context must know how to go from say version 3 to version 2. Normally, you use the assemblies you're about to deploy as the source of your migrations, but in this case, you must use the already deployed assemblies as they're the only ones who know how to get from v3 down to v2. (Version 2 has no idea that v3 even exists.)

My current plan is to somehow compare the two assemblies during deployment. If the assembly in the installation directory contains "newer" migrations than the one in the deployment director, I would first need to get the "newest" available migration in the assembly in the deployment directory, and then execute:

migrate.exe AssemblyInInstallationDir /targetMigration NewestFromAssemblyInDeploymentDir

Where as in a "normal" deployment scenario where you are upgrading to a newer version, you can just do:

migrate.exe AssemblyInDeploymentDir

Is this a legit approach? I have yet to look into using EF libraries to evaluate what migrations are available in each assembly. There is also the challenge of the fact that each of these assemblies are the "same" just different versions. I'll probably have to load them into separate app domains and then use cross-app domain communications to get the information I need.

EDIT

I created a proof of concept app that allows me to list the available migrations to two different versions of the same assembly. This was critical to this entire process, so I figured it's worth documenting.

The app uses reflection to load each of the assemblies and then uses the DbMigrator class from System.Data.Entity.Migrations to enumerate the migration meta data. The names of the migrations are prefixed with the timestamp information, thereby allowing me to order them and see which assembly contains the "newer" set of migrations.

static void Main(string[] args)
{
    const string dllName = "Test.Data.dll";
    var assemblyCurrent = Assembly.LoadFile(Path.Combine(System.Environment.CurrentDirectory, string.Format("Current\\{0}", dllName)));
    var assemblyTarget = Assembly.LoadFile(Path.Combine(System.Environment.CurrentDirectory, string.Format("Target\\{0}", dllName)));

    Console.WriteLine("Curent Version: " + assemblyCurrent.FullName);
    Console.WriteLine("Target Version: " + assemblyTarget.FullName);

    const string contextName = "Test.Data.TestContext";
    const string migrationsNamespace = "Test.Data.Migrations";
    var currentContext = assemblyCurrent.CreateInstance(contextName);
    var targetContext = assemblyTarget.CreateInstance(contextName);

    var currentContextConfig = new DbMigrationsConfiguration
    {
        MigrationsAssembly = assemblyCurrent,
        ContextType = currentContext.GetType(),
        MigrationsNamespace = migrationsNamespace
    };

    var targetContextConfig = new DbMigrationsConfiguration
    {
        MigrationsAssembly = assemblyTarget,
        ContextType = targetContext.GetType(),
        MigrationsNamespace = migrationsNamespace
    };

    var migrator = new DbMigrator(currentContextConfig);
    var localMigrations = migrator.GetLocalMigrations(); //all migrations

    Console.WriteLine("Current Context Migrations:");
    foreach (var m in localMigrations)
    {
        Console.WriteLine("\t{0}", m);
    }

    migrator = new DbMigrator(targetContextConfig);
    localMigrations = migrator.GetLocalMigrations(); //all migrations

    Console.WriteLine("Target Context Migrations:");
    foreach (var m in localMigrations)
    {
        Console.WriteLine("\t{0}", m);
    }

    Console.ReadKey();
}

}

The output of the application looks like:

Curent Version: Test.Data, Version=1.3.0.0, Culture=neutral, PublicKeyToken=null
Target Version: Test.Data, Version=1.2.0.0, Culture=neutral, PublicKeyToken=null

Current Context Migrations:
    201403171700348_InitalCreate
    201403171701519_AddedAddresInfoToCustomer
    201403171718277_RemovedStateEntity
    201403171754275_MoveAddressInformationIntoContactInfo
    201403181559219_NotSureWhatIChanged
    201403181731525_AddedRowVersionToDomainObjectBase
Target Context Migrations:
    201403171700348_InitalCreate
    201403171701519_AddedAddresInfoToCustomer
    201403171718277_RemovedStateEntity

Answer 1

The way I typically approach this is to (almost) never make breaking changes to my database schema. It's basically a controlled form of technical debt.

For example, let's say I'm replacing ColumnX with ColumnY. The typical approach is "copy all the data from ColumnX to ColumnY, remove ColumnX from schema". This kills your ability to roll back to the previous version, because ColumnX is gone.

The rollback-friendly way of tackling this is to add ColumnY, copy the data over, and add triggers to keep both columns in sync with one another. This is is not intended to be a permanent state! A user story for "Remove ColumnX and associated triggers" immediately goes on the backlog, for a future iteration, when we're certain we'll never be rolling back to a version that depends on ColumnX.

Rollback can still involve publishing the previous version of the DACPAC, with the caveat that you have to make sure you don't drop items present in the database not in the schema. This way, if you updated a bunch of stored procedures to pull from ColumnY, you can publish the old version that pulls from ColumnX and the old version is blissfully unaware that the schema has changed.

Answer 2

We actually solved this problem and have been using our tooling for over a year now to do fully continuous database deployments into production. No humans involved. :)

We've made some of this public on GitHub: https://github.com/GalenHealthcare/Galen.Ef.Deployer

You can make "breaking" changes, but generally, we avoid that as well - but mostly because our applications remain live during upgrades. We treat the data tier as an independently deployable component - and as a result, it has an "interface" that needs to remain compatible.

We will often use a multi-phased upgrade approach where we deploy an intermediate version that's backwards/forwards compatible, upgrade our various application services, and then finally upgrade the database tier to remove the legacy compatibility.

Even in that scenario, we have the ability to automatically go from/to any version of our schema and data. In fact, we've added unit tests that verify this every single time we build for every single database version. It basically walks up/down the chain of schema iterations and validates that the upward and downward migrations always work and maintain data consistency and compatibility. You can see these tests in the GitHub project. Here is an example:

https://github.com/GalenHealthcare/Galen.Ef.Deployer/blob/master/Galen.Ci.EntityFramework.Deployer/Galen.Ci.EntityFramework.Testing/MigrationTestRunner.cs