Integrate Database Migrations with Octopus Deploy

Summary


Prior to Octopus, we had a proprietary way of deploying database changes. Moving to Octopus Deploy forced us to find an alternative approach. I had used Fluent Migrations on an earlier project and I was excited about using it again.

Benefits

  • Database source is treated as first class citizen and remains in the project.
  • Full text search can easily be done to find each time a field or sproc was changed.
  • Stored Procedures can be easily compared between migrations.
  • Version History maintained in database (see screen capture below) showing exactly when each migration was applied (and Version, ITMS, PBI, or other info).
  • Multi-user Two people can be working on their own db changes at the same time, each in their own migration. (The normal rules that both of them should not be changing the exact same objects still apply).
  • Ability to apply multiple “iterations” during a sprint (instead of waiting until the end)
  • All Database changes are tracked together.
  • This approach eliminates the need to push a db change using email or some other method. In our current environment, we are forced to do that. For a simple change, it just doesn’t make sense to create a new branch of managed code, jump through the Fortify hoops and everything else that is involved in rolling a simple change, force all users out of the system, etc… With this approach, adding a simple migration should take less than 30 minutes and it is done.
  • Gives visibility to all changes, which can greatly assist troubleshooting down the road.
  • Fortify Tango not required. The current Implementation folder is not part of the Fortify process and this shouldn’t need to be either. There is no User component to this. This is not an application that is being installed for the user.
  • Database changes are not tightly coupled to managed code changes. Some people would list this as a Con, but having them “separate” has some benefits.
  • Best Practices (and possibly worst) are more easily followed for future devs on how to script an extended property or foreign key. The prior scripts are part of the project and can be searched and copied.
  • Developer is not forced to use migrations. The can still wait until right before the deploy, let a tool do the compares, generate the scripts, and then just copy them into the folder.
  • Deploy and rollback can easily be ran multiple times during development, EXACTLY as they will be ran during deployment.
  • Visual Studio 2015 new project – added by default
  • Screens

    Migration history in DB

    Migration01

    PowerShell script

    Migrate02

    Technology / Tools

    Print Friendly, PDF & Email