How I set up a POC Database Deployment Pipeline for Continuous Integration, part III

Curious about how to do a push-button deployment of a database change? This is the last part of this 3-part series on how I previously setup a proof of concept (POC) Database Deployment Pipeline, and I’ll show how I configured Octopus Deploy to make it happen.

Recap

In part I and part II of this series I showed you how I configured a source control repository using SVN, how I linked the Bookstore database to this repository using Redgate’s SQL Source Control (SOC) and how I configured a Team City build configuration for this database. Then I showed how the build server automatically created a build after a change is committed to the source control repository, and that it stored this build as a package in our NuGet server to be published to a NuGet feed. So far, that’s step 1 to 5 in this high-resolution visualization.

SQL CI POC Flow

The last steps to cover are how we configured our Octopus Release Management Server, which pulls the database build from the NuGet feed, and how we push-button deployed the first build to our build environment.

For the sake of clarity, I’m quickly going to run through a few concepts that I possibly should have mentioned at the start of this series.

Introducing NuGet Packages

Developers write and compile code, and for this compiled code to work, it probably does depend on other software modules (libraries). You can probably imagine that in order to install, upgrade, downgrade or uninstall a piece of software, it would be handy to have all these pieces together in a package! These packages need to be managed by a package management system, and Microsoft introduced NuGet as the flavor of package management for .NET, hence the name NuGet packages. There are also other package management systems, like ProGet, to handle NuGet packages. They not only wrap the pieces of software together into a package, but also add metadata like version numbers.

With a package management system and additional tools like a release management tool, it is much easier to automate the installation, upgrade or configuration of software, but not only that: the same is now also possible for databases!

Introducing Release Management tools

Release Management is the process of managing software releases from the development stage through to software release. There are tools like Chef, Puppet Labs and Octopus Deploy that make this process easier by automating most parts of the operations and release environment, and which enable so-called “push-button deployments”. We use Octopus Deploy as our release management tool, and you can read all about the key concepts of Octopus Deploy from their documentation pages.

With Octopus Deploy we are able to easily pull packages from our ProGet package management system and deploy them to the various environments. As you’ve hopefully guessed by now, in this series I’m showing how we can now also do this for database deployments!

NuGet package repository

We use ProGet as our package management server. It takes care the NuGet packages that are created by one of the build steps (see part II of this series), and which then are stored in a repository and published to a feed for Octopus to “pull” from. Fortunately, I did not have to install or configure anything as this server was already part of our infrastructure. Later in this post I’ll show you how a build ‘magically’ appears in the Octopus configuration of the Bookstore database when creating a new release.

Installation of the DLM Automation Suite

Of course our Octopus Deploy server had to be set up as well. To do that, I first installed Redgate’s DLM Automation Suite (back then named Automation Pack) on our Octopus deployment server, and Redgate have some instructions on how to do that. Next I had to ask my colleague to install a so-called Octopus “tentacle” on the shared development server in the build environment (That’s the SQL Server we used to do shared development work on before we implemented SQL Continuous Integration.)

Tentacle is a secure, lightweight agent service that Octopus uses to deploy software. Tentacle runs as a Windows Service, and is installed on all of the machines that you plan to deploy software to, such as your application and web servers. In essence, Tentacle is a job runner. It waits for Octopus to give it a job (deploy a package, run a script), and then it executes it, reporting the progress and result back to the Octopus server. (Source: Octopus Deploy Installing Tentacles)

The Octopus tentacle is an agent installed on this server, which the Octopus deployment server communicates with to deploy releases. The agent does the actual deployment using the DLM Automation Suite, which also needed to be installed in the build environment.

Configuring the deployment configuration

The only thing left to do was to configure the Octopus deployment tool for the Bookstore database, so our Release Manager created a project group named ‘Databases’ and a project named ‘Bookstore’.

In Octopus Deploy, a project is a collection of deployment steps that you want Octopus and specific tentacles to run, and the specific variables you use to configure it. A project is like a recipe – you define how your software is deployed, and then you create many releases to deploy different versions of that software. (Source: Octopus Deploy Projects)

A project group contains a list of projects. project groups can be a useful way to organize your Octopus Deploy installation. (Source: Octopus Deploy Project Groups)

In this project, under the ‘process’ option, he created a deployment step and named it ‘Deploy SQL’.

Octopus Bookstore Deployment process

In the ‘step details’, we selected ‘sql’ as machine role, Proget as the NuGet package repository, and entered Bookstore.Database as the NuGet package ID:

Octopus Deploy SQL

Machine roles allow you to “tag” machines with a specific keyword which you can later on use to scope deployment steps and/or variables. (Source: Octopus Deploy Machine Roles)

Look if you want to know how to get Proget into the Octopus Deploy ‘NuGet feed’ dropdown box, there’s some guidance on the Octopus documentation pages.

We chose the download option ‘Octopus Server will download the package, then securely upload it to the tentacles’, and then the last step was to configure were the Post-deployment script and the Conditions.

Octopus Post-deployment script

For the Post-deployment script I used a PowerShell script from Alex Yates’ blog. At the time of writing that post is obsolete because Redgate has released SQL Release, a product which I am going to write about later on! Still, the PowerShell script he posted back then proved to be very useful, and here is the somewhat edited version of the script we currently use:

As you can see in the script, there are Octopus configuration variables declared: $databaseName, $databaseServer, $databaseUsername and $databasePassword. Obviously the variable $databaseName is ‘Bookstore’, but for the other three variables I used $OctopusParameters substitutes. These parameters are configured in Octopus Deploy under the Library > Variable Sets > Databases menu option, and you can see the Octopus Deploy variables documentation page for more info. You can configure the server name and the username and password for every environment, so you only have to configure them once. Be aware that the username and password entered here are for SQL Authentication and should be marked as sensitive. For Windows Authentication, which I prefer, you can simply ignore these fields and Octopus will use the account under which the tentacle is running.

Under Conditions I selected D-DTAP as the environment (an environment which was already configured in Octopus Deploy.)

In Octopus, an environment is a group of machines that you will deploy to at the same time; common examples of environments are TestAcceptance,Staging or Production. (Source: Octopus Deploy Environments)

D-DTAP is the name of our database development environment where our developers can test their software.

Create release and deploy from Octopus

Now that everything was configured, it was time to create a release and deploy it to the build environment.

In Octopus, a release is a snapshot of the deployment process and variables, with a set of packages selected. That release is then deployed to multiple environments, typically to one, then promoted to the next environment if successful. Each time you have a new candidate build that is ready to test, you’ll create a release. When you apply a release to an environment, that is a deployment. (Source: Octopus Deploy Key Concepts)

In the Octopus Deploy configuration for the Bookstore database, I clicked Create Release and the next screen appeared:

Octopus Deploy Create Release

Octopus automatically fills in the Version number based on the latest package stored in the ProGet feed. I entered a meaningful Release note, clicked Save and a new release was being created. Octopus jumped to the next page:

Octopus Deploy Release page

By clicking Deploy to D-DTAP I then got to the page where I could do the actual deployment:

Octopus Deploy Deploy Release

I simply click Deploy now, and the result looked like this:

Octopus Deploy Task Summary

If things went wrong, I could have gone to the Task log tab, but it didn’t. That was it: I did an actual push-button deployment for a database change!

Conclusion

In this blogpost series I showed how I set up a Proof of Concept for SQL database Continuous Integration. To set this up I used the Bookstore database and:

  • Created a source control repository in SVN
  • Source controlled this database
  • Installed Redgate’s DLM Automation Suite on our Team City Build Server
  • Configured a Team City build configuration
  • Installed Redgate’s DLM Automation Suite on our Octopus Deployment Server
  • Configured an Octopus Deployment configuration
  • Installed an Octopus tentacle and Redgate’s DLM Automation Suite in our Build Environment

With this setup I was able to:

  • Make a change to the database in my local work environment
  • Commit this change to source control which triggered an automated build in Team City
  • Create a release in Octopus Deploy and deploy it with the push of a button

I used this POC to demo to our developers, QA engineers, system engineers and managers to get buy-in, and I still use this setup for demo purposes and to test extensions and features. Hopefully this will help you set up your own proof of concept! If you have any questions about this, you’re welcome to get in touch with me on twitter (or Alex Yates, who’s been helping me get this all implemented!)

2 thoughts on “How I set up a POC Database Deployment Pipeline for Continuous Integration, part III”

Leave a Reply

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