Implementing CI for databases V: Creating Octopus Deploy Projects

In this series, every component needed to do database push-button deployments is set up. From setting up the source control repository, preparing the build environment to configuring the deployment environment. All that’s left to do is to create an Octopus Project to enable real database push-button deployments. In this part I’m going to explain how. 

Creating an Octopus Project Group

Before creating an Octopus project, it is wise to create an Octopus Project Group first. Why? Actually the documentation on the Octopus Deploy site says it all:

As your usage of Octopus grows, and you start to have many environments and many projects, project groups can be a useful way to organize your Octopus Deploy installation. A project group contains a list of projects. It also:

  • Groups the projects together on the dashboard
  • Allows the restriction of permissions to users for the grouped projects

If you can’t create Octopus Project Groups because you don’t have permissions, go to the person(s) in your company who can. This is probably the person with the function of Release Manager. If you’re following this series and tried to set up a database deployment pipeline like I did, most likely you already collaborated with that person to come this far.

One last thing before I continue: you will notice that I’m going to jump around during setup. That’s not me, it’s the tool. There’s just no clear linear path configuring Octopus, that’s all.

Creating an Octopus Lifecycle

Next to an Octopus Project Group, you also need to create an Octopus Lifecycle. With an Octopus Lifecycle, you control the way releases are deployed to your environments. In our case it controls how changes must first be deployed to Dev, then promoted to Test, Acceptance and finally Production. Here’s how you create an Octopus Lifecycle (again, you probably need to work together with your Release Manager on this). Go to Library > Lifecycles and click ‘Add lifecycle’:


In the next screen, give the lifecycle a name and click ‘Add phase’:


Your version of your database lives in one or more phases which defines its Lifecycle. In my case the phases are D-DTAP, T-DTAP, A-DTAP and P-DTAP. Yes, they map to the environments created earlier as you can in the next screen:


Here I name phase 1 ‘D-DTAP’ and I am adding the environment ‘D-DTAP’:


It’s possible to add more environments to a phase in a lifecycle, but for the purpose of this article I’ll leave it to one. Add the other phases for the other environments, click Save and you’ve created your Octopus Lifecycle for Databases!

Creating Octopus Variable sets

A variable set is a collection of variables which you can use in your projects. It actually makes it much easier to create your projects. You can create a variable set as follows. Go to Library > Variable sets and click ‘Add variable set’:


In the next window enter the name of the variable set and save it:


Now you can add variables to the set, starting with the servers for every environment:


Here you can see that for every variable, there can be different values. For the variable ‘Databases.Server’ you can add the names of your servers as a value. The beauty here is that you can define a value for every environment (Scope). The advantage will become more clear when creating an Octopus Project.

Creating an Octopus Project

Now it’s really time to create an Octopus Project for the Bookstore database. To create this Octopus Project, go to Projects and click ‘Add project’ in the Project Group (here it’s ‘Databases’) where you want to create it:

It’ll take you to the next screen:


Here you can fill in the name of the project and a description. You can also select the previously described Project group and Lifecycle. Click save and you’re done. You can now see it appeared in the Octopus Project Group:


Next, click on the project to configure it a bit more. The first tab you see is the Overview tab:


As you can see, I’ve already created and deployed some releases. Release, deployed all the way to production, and release are visible here. From the comment in release you can see that I’ve added a column to a table named [epane]. This command was actually executed against the database on my workstation and only committed to source control. From the automatically created build that followed, I created this Octopus Release and deployed the change to the environments shown here with the push of a button. I did not need to go to any of these environments to execute the command there. Isn’t that cool?!

Configuring the Octopus Project Process

In the process tab, you can see the steps I already created to do the deployment to the several environments.


Keep in mind that this is just one way to do it. How you design your process is entirely up to you and to be fair, for our production databases our process is a little bit different. In this process, to do push-button database deployments, steps 1, 2 and 6 are the bare minimum. Step 2 and 6 are based on Redgate’s SQL Release templates. Remember when I wrote about importing them? Here’s how I used them.

To create a step, click Add step. In the new window you can choose the step type:


(There are much more than the ones shown here. These are only Redgate’s SQL Release step types)

Download and extract NuGetPackage

In this post I explained how to create a build configuration and how such a build configuration, when run, creates a build of the database and that it stores it as a NuGet package in the Package Management Repository. With this step, Octopus Deploy pulls this package from its repository and extracts it. The configuration of this step looks like this:


As the target, choose the machine role that you created previously when creating machines in Octopus Deploy (here it’s ‘sql’). Select the NuGet feed (here it’s ‘Proget’) and enter the NuGet package ID (this ID was defined when creating the build configuration). Choose your preferred Download Option and enter the Environment(s) where you want this step to run. In my case I want Octopus Deploy to download and extract the Nuget Package every time I want to deploy to an environment. This is not strictly necessary if you don’t configure the next step for every environment.

Redgate – Create Database Release

The next step in the process is the Redgate – Create Database Release step. It’s based on the template imported before.


Again, you have to fill in the Machine Role. You also have to fill in the Export path, here it’s ‘D:\RedGate\Database releases’. This folder has to exist on every machine where your tentacles run, i.e. the database servers where you want to deploy to. It is in this folder where this step creates Redgate’s database releases. It also has to know the Database package step, so select ‘Download and extract NuGetPackage’. As the Target SQL Server instance if selected the previously made variable #(Databases.Server). You can also choose a username and password if you (want to) use SQL Authentication. Here I use the previously made variables for them to show that it’s possible. In reality I use Windows authentication and I leave those fields empty. Octopus then connects to the SQL instance with the credentials of the service account under which it’s tentacle runs. For security reasons I advise you to do the same and let the Octopus tentacle run under a domain user account. If you’re not a domain admin, ask your colleague(s) who are to create one.

Notify DBA Team

The next step in the process is the ‘Notify DBA Team’ step, created from the ‘Send an email’ Octopus template. I configured it to send an email to our ticketing system, which on arrival automatically creates a ticket. The subject of the email and thus the ticket is: ‘Review Database Change  #{Octopus.Project.Name} #{Octopus.Release.Number}’. As you can see, I use the Octopus Deploy variables #{Octopus.Project.Name} and #{Octopus.Release.Number}. Octopus Deploy automatically fills in the Octopus Deploy Project Name and the Octopus Release Number when creating the email. For the body of the email I used the text:

‘Please review this change here:{Octopus.Web.DeploymentLink}’.

As you can see, I make use of an Octopus variable again. This way, I only have to click the link which takes me directly to the Octopus Release. I can see the changes quickly by opening the Changes.html documents, which is included in the artifacts, and do the review. Much clearer than the sql script, isn’t it? As the release deployed to Production is the same as the one deployed to Acceptance, this step is only done when deploying to Acceptance (A-DTAP environment).

Review by DBA

Most DBA’s probably won’t let anything be deployed to Production before the changes are reviewed by them. With this step you can build in the ‘gatekeepers’ step. It is an Octopus Deploy step based on the ‘Manual intervention’ template and with it you stop the deployment until someone with the correct permissions explicitly approves the deployment. In this case the DBA. If the person approves the deployment, it will continue. In the project I use in this post, the manual intervention step ‘Review by DBA’ will be taken when a release is deployed to Acceptance. It’s totally up to you if and where you want this step.

Differential Backup

The ‘Differential Backup’ step is based on the custom step template ‘SQL – Execute Script’. You can download it from the Octopus Deploy Library and import it into your Octopus Deploy server.


Here I want the script to run against on the machines in the role ‘sql’ and only in the environment ‘P-DTAP’. You have to enter the connection string to let the step know against which database on which server and with which credentials (optional) it has to execute the query. In this step you can see I make use of variables again by letting the step connect to variable #{Databases.Server} with the credentials #{Databases.UserName} and #{Databases.UserPassword}. The SQL Script itself executes the DatabaseBackup stored procedure in the DBA database to do a differential backup (command not entirely visible). DBA’s may recognize I’m using Ola Hallengrens excellent Maintenance Solution for doing backups.

You can choose to add this step, but it’s not necessary to do deployments. It is an extra safety net next to your backup strategy (you have a backup strategy, don’t you?). You can also choose to do a full backup or a transaction log backup. Be aware that Octopus has a step timeout of 1 minute, so if you choose to implement a backup step, make sure your backup doesn’t take longer than 1 minute.

Redgate – Deploy from Database Release

The last step in the process of this Octopus Deploy project is the actual deployment itself. This step runs the SQL script created by the ‘Redgate – Create Database Release’ step. Again I make use of a SQL Release template named ‘Redgate – Deploy from Database Release’.


When you’ve read this post until this point, you should be pretty familiar with the settings you can configure, so I won’t bore you anymore explaining them again. That’s it, the Octopus Deploy project is created! Now it’s time to create an Octopus Deploy Release and deploy changes to the database with the push of a button! In the next post I will show you how to do that.


Configuring Octopus for database deployments involves a lot of steps, but you don’t need to do all of them for every Octopus project (i.e. database). Here’s a list of steps you must take once to configure Octopus:

  • Create an Octopus Project Group
  • Create an Octopus Lifecycle
  • Add Phases
  • Add Environments
  • Create Octopus Variable sets

For every database you need to create an Octopus Project. When configuring the project, you need to add steps. At least you should include steps to:

  • Download and extract the NuGet package
  • Create the database release
  • Deploy the release

You can add more steps, for example a step to take a backup before deploying to production.

Remember that there’s no clear linear path configuring Octopus, you have to jump around a little bit. Remember also the benefits you will reap once you’ve got it all set up!


Leave a Reply

Your email address will not be published. Required fields are marked *