Deploy SQL Migrations with Octopus

Here is a brief summary and a to-do list for packing and deploying SQL migrations in combination with Octopus. The prerequisites are: .NET project, Fluent Migrator, Octopus Deploy.



The first step is to set-up database migrations using code (C#). For this,

- create a library project in the solution,

- add Fluent Migrator (eventually FluentMigrator.Tools and/or FluentMigrator.Runner but these are not required) NuGet package to it,

- create the first migration class containing the change (schema and/or data).



This will produce the migration binary .dll file on build operation. To get the SQL commands for the actual change, one needs to run the Migrator tool to generate the change commands based on the current version of the database. If this is the first migration it will also generate the commands for creation of the Migration_Info table.



- Create SQL generation script(s). There are a few useful scripts for checking the SQL output.

  - Script for previewing the current migration (just runs -preview),

  - Script for manually generating the SQL output, and

  - PowerShell script for automatic generation of the SQL file output during the automatic build process.



The PowerShell script will be executed after the build completes on an integration server. It will generate the SQL file from the built migrations .dll binary. To do that,



- add the post-build script that runs the Migrator and generates the output SQL file (this is the automatic script from above).



After this, it is time to generate the NuGet package.

-  create .nuspec file that includes the generated .sql file.

- adjust this script to create the NuGet package with the generated output.

- set the script to upload the NuGet package to a NuGet repository. This is the Octopus Library.



Once this is done, the only remaining step is the deployment.

- create the Deploy.ps1 script and include it in the generated NuGet package. This script should either execute the SQL file or copy/send it to the DBAs for examination and scheduled execution.



This solution provides the history of database changes (both schema and data). It is automatic and does not require developers to manipulate or send SQL commands. Besides the code migrations, it also creates an archive of all the migration packages, which is useful for the database administrators and possibly other parties. These packages can be safely stored along with the other binary packages for deployment.