Implementing CI for databases IV: Configuring Octopus Deploy Environments

Now the Build Environment is configured to automatically create a new database build every time a change is checked in into source control, it’s time to configure the Deploy Environment so these builds can be deployed with the push of a button.


First I’d like to start with the overview of our Database Deployment Pipeline which I used in my last post in this series:

DTAP SC Build Deploy Overview

In that last post I focused on the Build Environment. I zoomed in on the Source Control and Build Environment parts and showed you how they relate to each other. I also showed you how to create build configurations in a TeamCity CI Server and how these are triggered to automatically create database builds after checking in changes into source control. What we want to achieve is that these builds can be deployed to the various environments with the push of a button. This is possible with Octopus Deploy, so in this post I’m going to focus on setting up the Deployment Environment.

Deployment Environment HL Overview 1

Note: The Octopus Deploy Agents (or Tentacles) are running on the Database Servers in this overview and they should be. However, it is possible to only run one tentacle on the Deployment Server and let it do deployments from there. I do not recommend this, but if you are running Windows Server 2003 (W2K3), you have no choice. Of course you shouldn’t be running W2K3 at all these days :-).

Collaborating with the Release Manager

As a DBA you’re probably not the administrator of your company’s release management tool and you shouldn’t be. You should be working with the person who is, this could well be the colleague with the function title of Release Manager. At this point, if you are following this series, you probably already collaborated with this colleague to install Redgate’s DLM Automation Suite on the several components. It should be installed on the Octopus Deployment Server and on the servers where the Octopus Agents run (the database servers where you want to deploy to).

Creating Environments and Machines in Octopus Deploy

To continue setting up the Deployment Environment, you need to configure Octopus Deploy Environments first. You’ll only have to do this once for every database in the same environment. In my case the D, T, A and P environments. To create these environments in Octopus, go to Environments in your Octopus Deploy web interface and click Add Environment:

OD Add Environment

In the Create an Environment step, enter the name of the environment you wish to create. In my case this is D-DTAP:

OD Create Environment

Create the other environments the same way, in my case these are T-DTAP, A-DTAP and P-DTAP. Next you have to add the machine which belong to every environment by clicking Add Machine:

OD Add Machine

In the next window you can enter the Hostname, which is the server your SQL Instance is running on:

OD Discover Machine

Since you already installed an Octopus Agent on the machine you are trying to add, you can click Discover and Octopus will find it automatically. After creating the machine, you’ll only have to change one Machine Setting, which is Roles:

OD Machine Settings

It’s important to choose a meaningful role name and use this role for every database server you create as a machine in Octopus Deploy. If it does not exist yet, it will be automatically created if you type it in here.

SQL Release

One of the tools in Redgate’s DLM Automation Suite is SQL Release, the Release Management tool. With this tool it is possible to automate the deployment of changes to your databases. It’s installed when you’ve installed the DLM Automation Suite. It provides Octopus Deploy step templates which can be used to set up Octopus Deploy projects. At the moment of this writing there are four:

  • Create Database Release
  • Deploy from Database
  • Deploy from Database Release
  • Deploy from Package

You can go to the Octopus Deploy Library, copy them to your clipboard (they’re scripts) and paste them into your own Octopus Deploy Library. To do that, you have to go to Library -> Step Templates -> Import. Like this:

SQL Release Import Templates

It’s pretty straightforward and now you can use the templates to create Octopus Deploy projects! I will show you how to do that in the next post, where I’ll also show how to create an Octopus Deploy Release and how to deploy this release to the desired environment.


In this post I showed you how simple it is to create an environment in Octopus Deploy as well as adding machines (your SQL Servers) to these environments. These steps only need to be done once for every environment and for every machine. I also showed you how to import SQL Release step templates, which I’m using in our Octopus Deploy Projects. In the next post I’ll show you how to set up Octopus Deploy Project.

Leave a Reply

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