Disable database initializer and use DbMigrator instead. See code snippet below or gist.
So for any given ASP.NET web app, we typically have this setup hosted on Azure:
- Dev web server / dev DB
- Automatically deployed to by master branch. Tested by developers.
- Staging web server / production DB
Manually deployed to by human. Briefly test the new code against real production data before we switch all our users over to the new code.
- Production web server / production DB
Manually deployed to by human. This is where all the traffic go, has multiple Azure web app instances to scale out.
- Add a property to my Person class
- Run `add-migration Add-Person-MyNewProp` to scaffold a migration script
- Commit and push
- TeamCity builds the app, runs tests and creates a release in Octopus Deploy
- Octopus deploys the web app to the dev server
- Dev server reboots and migrates the dev DB on startup
"Hold on a minute, it migrates on startup? Mine doesn't?"
(Or let's pretend that is what you were thinking.)
Yes, isn't that sweet? I added the following to my Application_Start() method:
Database.SetInitializer<ApplicationDbContext>(
new MigrateDatabaseToLatestVersion<ApplicationDbContext, Configuration>());
Ok, moving on. The new app is out in the cloud, you play with its test data and it's all good and ready to rock. Let's put it to the test on real production data, surely that will uncover some edge cases.
- Promote release from dev to staging in Octopus
- Octopus spins up the staging instance and deploys the exact same binaries
- Staging server reboots and migrates the production DB on startup
Awesome, this is the final test before the switch to production. You play around with the new stuff, it's all against real data and it kicks ass. Surely this release will go without a hitch. You go get the manager to show off the new cool feature, but he is busy in the phone and does not look happy.
Customers are calling about the server being down. You rush back to your [cubicle|dorm|ball pit], your inbox is flooded with system monitoring alerts. What is going on?!
Enough of the drama. The production server stopped working when our staging server migrated the production DB schema. The default behavior of Entity Framework code first is to fail - and fail hard - if the DB schema does not match the code. I can appreciate the safety check, but this was at least for me totally unexpected, since adding a new column should be backwards compatible.
It turns out, all of the database initializers that come bundled with Entity Framework, require the DB schema to match the code. So how can we then run a staging server against the production DB?
- Disable the default database initializer, which defaults to CreateDatabaseIfNotExists
- Use DbMigrator to programmatically migrate the DB
I don't know what is the official approach, and maybe this whole ordeal is way off, but at least this works for me!
// Call me on startup, such as in Application_Start() of Global.asax.cs private static void CreateOrMigrateDatabase() { // Disable automatic migrations as it prevents us from deploying DB changes without breaking the production web instance. Database.SetInitializer<ApplicationDbContext>(null); var migrator = new DbMigrator(new Configuration()); // Order by migration names to get oldest first, such as 201403221133523_AddSetting List<string> pendingMigrations = migrator.GetPendingMigrations().OrderBy(x => x).ToList(); List<string> localMigrations = migrator.GetLocalMigrations().OrderBy(x => x).ToList(); List<string> databaseMigrations = migrator.GetDatabaseMigrations().OrderBy(x => x).ToList(); // Sanity check, avoid trying to migrate a DB that is newer or has different history than our local migrations if (pendingMigrations.Any()) { bool migrationsCompatibleWithDb = localMigrations.StartsWith(databaseMigrations); if (!migrationsCompatibleWithDb) { var ex = new Exception("Unable to migrate database. Not compatible."); ex.Data["localMigrations"] = string.Join("\n", localMigrations); ex.Data["databaseMigrations"] = string.Join("\n", databaseMigrations); ex.Data["pendingMigrations"] = string.Join("\n", pendingMigrations); throw ex; } Trace.TraceInformation($"Migrating DB from {databaseMigrations.LastOrDefault() ?? "(empty)"} to {pendingMigrations.Last()}"); migrator.Update(pendingMigrations.Last()); } using (var db = new ApplicationDbContext()) { db.Database.CreateIfNotExists(); db.Database.Initialize(false); } }
And for my own convenience, I use this extension method:
using System; using System.Collections.Generic; using System.Linq; namespace MyApp.Extensions { public static class EnumerableExtensions { public static bool StartsWith<T>(this IEnumerable<T> source, IEnumerable<T> items) { if (source == null) throw new ArgumentNullException(nameof(source)); if (items == null) throw new ArgumentNullException(nameof(items)); IEnumerator<T> sourceEnumer = source.GetEnumerator(); // False if source is shorter than items or any source item // does not equal the item at the same index. return items.All(item => sourceEnumer.MoveNext() && Equals(sourceEnumer.Current, item)); } } }
