Deploying SQL databases using Entity Framework migrations and Octopus Deploy

This is part one of a two part mini-series on deploying SQL server assets using Octopus Deploy. Today we'll be deploying databases using Entity Framework migrations. In part two we'll be deploying SQL Server Reporting Services reports.

The samples below use my Contoso University sample solution which I use for demos and talks. All the source code is available on GitHub.

Why use Entity Framework migrations?

Entity Framework migrations provide a great way to manage your database schema changes in code. It produces the same SQL each time a migration is run, which means it can be run reliably in each environment as you promote code. It also avoids the overhead of manually producing SQL migration scripts and either handing them off to DBAs to run at deploy time or using tools such as DbUp to run them.

But can't you only run EF migrations in Visual Studio?

In short, no. If you've used EF migrations, you'll be familiar with the PowerShell command Update-Database to run your migrations in Visual Studio.

EF also ships with a little known tool called migrate.exe which allows us to run migrations from the command line. Using this tool we can wire a database deployment step into our Octopus Deploy process.

Packing the database project

In order to deploy our database we need to create a NuGet package for Octopus to use. Simply add the OctoPack NuGet package to the project which contains your DbContext and migrations.

The Deploy.ps1 hook

Octopus has a convention where if a package contains a Deploy.ps1 file, it will be run at deploy time. This is the hook we'll use to invoke migrate.exe. In my sample solution I have a Deploy.ps1 file which looks like this:

$connectionString = $OctopusParameters["SQL.ContosoUniversity.DeployConnectionString"]

.\migrate.exe ContosoUniversity.Data.dll /connectionString="$($connectionString)" /connectionProviderName="System.Data.SqlClient"

The important parts here are:

Grab the deployment connection string from a Octopus variable. The SQL user in this connection string should have permissions to create databases and alter their schemas.
The name of the assembly containing your DbContext and migrations

Set database permissions script

Once the database is deployed, you'll need to set some permissions on it. I like to include a SQL script to do this in my database project. In my sample solution I have a set-database-permissions.sql script which looks like this:

IF NOT EXISTS (
	SELECT name 
	FROM [sys].[database_principals ]
	WHERE name = 'ContosoUniversitySite')
BEGIN
	CREATE USER ContosoUniversitySite FROM LOGIN ContosoUniversitySite;
END

ALTER ROLE db_datareader ADD MEMBER ContosoUniversitySite;
ALTER ROLE db_datawriter ADD MEMBER ContosoUniversitySite;

You'll need to alter the database and user names, or better still parameterise the script so that Octopus can inject the correct values for each enviroment.

Customise the NuGet package

Out of the box, the Deploy.ps1 and custom SQL scripts won't be included in NuGet package produced by OctoPack. To include them we need to use a custom .nuspec file. In my sample solution I use this .nuspec:

<?xml version="1.0"?>
<package >
  <metadata>
    <id>ContosoUniversity.Data</id>
    <version>$version$</version>
    <authors>Kevin Kuszyk</authors>
    <description>ContosoUniversity.Data deplopyment package</description>
  </metadata>
  <files>
    <file src="bin\release\*.*"/>
    <file src="..\..\packages\EntityFramework.6.1.1\tools\migrate.exe" />
    <file src="*.ps1" />
    <file src="sql\*.sql" target="sql" />
  </files>
</package>

The important part here is the files section. It tells OctoPack to:

Include everything from the bin\release folder. This is the default behaviour, but as we a using a custom .nuspec we have to explicitly tell OctoPack which files to include in the package.
Include the migrate.exe tool from the Entity Framework NuGet package. Note the EF package version is hard coded, so you'll need to remember to change it when you upgrade EF.
Include all PowerShell scripts in the root folder of the project.
Include all SQL scripts in the sql folder.

The Octopus Deploy process

To deploy the database I make a process in Octopus which looks like this:

Deploy database step

The Deploy Database step is simply a deploy package step.

Set database permissions step

The set database permissions step uses the execute SQL script file step template from the Octopus Deploy Library. I have it configured like this:

Variables

The final piece in the jigsaw are the Octopus variables. I have a variable set with the following names and values for my demo deployments:

Wrap up

To summarise, by packing the migrate.exe tool into a NuGet package along with our Entity Framework migrations and any additional SQL scripts to set permissions etc we can automate our SQL database deployment.

Thats all for today. In part two we'll see how to deploy SQL Server Reporting Services reports using Octopus Deploy.

Menu