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
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
<?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\releasefolder. This is the default behaviour, but as we a using a custom
.nuspecwe have to explicitly tell OctoPack which files to include in the package.
- Include the
migrate.exetool 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
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:
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:
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.