EPiServer SQL scripts and continuous integration with Octopus deploy

In a quite recent blog post by Per Bjurström of EPiServer he wrote about a new database version for EPiServer CMS where they actually include the database schema changes in the nuget update package.

This is an awesome step in the right direction where everything that is needed to upgrade an EPiServer site is included in the nuget package, but… How do we integrate this with our continuous integration process?

Turns out it’s not so difficult at all. Looking at this blog post by Paul Stovell, the man behind Octopus Deploy, we can get some inspiration as to how this could be achieved.

Lets start by adding a console application project to our solution and add the DbUp nuget package. This will be the project that contains all of our database schema changes, in most cases probably only the SQL scripts from EPiServer, but we could of course have different databases that also need to update their schema from time to time.

We then update our EPiServer nuget packages. If we try to run our site now we’ll get the old familiar yellow screen of death saying: “The database has not been updated to the version 7007.0, current database version is 7006.0.”

This is easily remedied by running the “update-epidatabase” command from the Nuget Package Manager Console, however this will only fix our development database. The database will still be out of sync when we deploy this release to any other environment. We need to extract the SQL script from the nuget package by using the “export-epiupdates” command:

PM> Export-EPiUpdates

 
An Export package is created C:\EPiServer\AlloyDemo\wwwroot\EPiUpdatePackage

Exporting  epiupdates into EPiUpdatePackage\EPiServer.CMS.Core.7.8.2\epiupdates

This will create a folder in our project root where we can find the sql scripts being run: EPiUpdatePackage\EPiServer.CMS.Core.7.8.2\epiupdates\sql

We’ll grab the 7.8.0.sql file from the folder and include it in our console application and make sure to set the build action to “embedded resource” to make the sql script a part of the generated .exe file.

Next we’ll add our episerver database connection string to our App.config.

It doesn’t really matter what you call the connectionstring here and you could of course have several if you have multiple databases that need to be updated.

Then we write som code to trigger the update by DbUp in the main method of our console app. This code is taken directly from Paul Stovells blog post and works just perfectly.

DbUp will automatically wrap all the calls in a transaction that will only be committed if all of the calls are successful. This is really nice as it means that we won’t have to worry about leaving our database in a half-upgraded state of some kind.

static int Main(string[] args)
        {
            //Grab a reference to our connectionstring
            var connectionString = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ConnectionString;

            //DeployChanges is a fluent builder for creating databases.
            //There are lots of options other than executing scripts embedded in the assembly, 
            //including from a specified file location or manually created scripts.
            var upgrader =
                DeployChanges.To
                    .SqlDatabase(connectionString)
                    .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
                    .LogToConsole()
                    .Build();

            var result = upgrader.PerformUpgrade();

            //If the result is unsuccessful we'll change the fore color to red and display the error

            if (!result.Successful)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(result.Error);
                Console.ResetColor();
                return -1;
            }

            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("Success!");
            Console.ResetColor();
            return 0;
        }

DbUp is also clever enough to add a simple database table storing every script that has already been executed so we do not need to worry about unnecessarily running scripts multiple times.

We could run this application right now, as is, and it would upgrade our database for us, but that’s not our goal right now. We want Octopus Deploy (or whichever deployment service you use) to be able to run this app and upgrade our databases automatically. That’s why we also add a simple powershell script that Octopus deploy will run as part of the deployment process. We put this in a Deploy.ps1 file and include it in our console project, making sure to put Copy to output directory to Copy if newer. This file will be automatically picked up by Octopus and run when the project has been deployed.

The Deploy.ps1 file looks like this (again courtesy of Paul Stovell):

& .\OctoSample.Database.exe | Write-Host

As I mentioned in a previous blog post I currently use TeamCity as my build server of choice and let TeamCity run Octopack to package my projects into nuget packages that Octopus deploy then grabs and deploys. To be able to let TeamCity pack our console application we need to install the OctoPack nuget package. Then when we check in our changeset TeamCity will pick it up and run OctoPack automatically, creating a nuget package for every project with OctoPack installed.

The last step to get the whole process to work is configuring Octopus Deploy. Simply add a step to the deployment process called “Update database” or something similar where you fetch the nuget package “EpiDbUp”, deploy it and execute the resulting .exe file to update the database.

The complete process now looks something like this:

Include the .sql file we want to run in our EPiDbUp-project (making sure to embedd it into the .exe). Check it in to source control. Which triggers a build on our TeamCity Build server which upon completion triggers the creation of a release in Octopus Deploy. Choose which environment we wish to deploy the current release to. The deployment process runs in two steps, first updating the database through our console project, then deploying our web application. And that’s it! The next time a database schema change is included in an EPiServer update we’ll simply add the script to our database project and all our environments will be automatically updated on next deploy.