Releasing Databases in VSTS with Redgate SQL CI and Octopus Deploy

You can still do Database Lifecycle Management (DLM) workflows in the hosted version of Team foundation Server (TFS) in Visual Studio Team Services (VSTS) . If you are doing your database development in SSMS, you can use a mix of tools to set up the functionality in VSTS. Jason Crease demonstrates how to build and deploy a simple database into the test environment using SQL CI, SQL Source Control, Octopus Deploy, tSQLt, SQL Cop and SQL Release, all with the minimum of hassle and effort.

Microsoft’s Visual Studio Team Services provides a convenient hosted version of TFS. With the recent addition of the Visual Studio Marketplace, it’s now really easy to create build-definitions to do your ALM and DLM (application and database lifecycle-management) workflows. In this article I’ll present a quick example of a DLM workflow.

You code SQL in SSMS, commit it using Redgate SQL Source Control to VSTS, build it using SQL CI in VSTS, and deploy with SQL Release in Octopus Deploy. Using the new extensions for SQL CI and Octopus Deploy, it’s straight-forward to setup advanced functionality in VSTS.

Database Continuous Integration in VSTS

My project, WidgetShop, has a SQL Server database. I’ve developed this database in SSMS, and used Redgate SQL Source Control to source-control the schema.

2364-WorkingInSSMS-4b594ff4-05fa-41ca-9d

My source-control system is VSTS. In VSTS code explorer, I can see my database schema:

2364-VSTSvc-d66492a1-0635-43c3-96bb-122d

Next in the chain I have a VSTS build-definition. This uses a few different tasks: Redgate SQL CI extension tasks, the Publish Test Results task, the Copy and Publish Build Artifacts task, and the Octopus Deploy Create Octopus Release task.

This system builds and tests the database. It then sends the package to Octopus Deploy – a popular deployment management system. I’ve set it up to deploy the package just to the testing environment – more on this later.

2364-BuildOverview-aa7669d9-fd67-4244-94

The Redgate SQL CI Extension

The Redgate SQL CI Extension is available from the Visual Studio Marketplace. Using this extension, you can add tasks to a VSTS build definition that will build a NuGet database package from a schema, run tSQLt tests, sync the schema to a target database, and publish the package to a NuGet feed.

I also publish the tSQLt test results using the Publish Test Results task. This produces a nice interactive way to explore your test results from within VSTS.

2364-vstsTestSummary-5a8c4935-22c0-4f66-

A tutorial is on the Redgate documentation site which fully explains how to set up the build definition.

The Octopus Deploy extension

In the Visual Studio Marketplace, there is an Octopus Deploy extension. This extensions adds a task to your build-definition task-list. This task lets you trigger an Octopus Deploy release from a VSTS build. It has several nice configuration options too, e.g. including all the relevant TFS Work Items in the release’s release-notes. See the Marketplace page for more information.

I’ve added this task to the end of my build definition. If the database builds and passes my tSQLt tests, a release will be created in Octopus Deploy. You can optionally deploy the release to an environment. Here, I’m deploying it directly to my Testing environment from the build.

2364-OctExtension-938351b5-3696-4271-92e

Doing a Build

I have a trigger on my build definition to cause a build on every check-in. So committing a schema change using SQL Source Control will cause a build.

2364-buildDefBuildOnCheckin-f0b2036d-955

VSTS Build agents can either be hosted (i.e. managed automatically by Microsoft on Azure) or on-site (run by you in your local environment). Hosted build-agents may be convenient, but they make it hard to deploy to on-site resources. You’d have to open your Octopus Deploy server (and often SQL Server) to the internet, which is difficult and insecure. That’s why I’m using an on-site build agent.

My build does these things:

  • Using the Redgate SQL CI extension, build the scripts folder to a NuGet package.
  • Publish this package as a build artifact. I often find build artifacts useful, but this step is not strictly necessary.
  • Using the Redgate SQL CI extension, run the tSQLt tests.
  • Publish the test results to VSTS. This creates the nice test visualisations and results in VSTS.
  • Publish the NuGet package to my on-site NuGet feed. 2364-publishPackage-daa16b1e-db27-4f3d-8
  • Sync the package to a database. I use this to update a CI database to the latest source-code. This gives my team a database which represents the latest source-controlled schema.
    2364-syncStep-868ce52a-64c6-4be7-9895-68
  • Release to my Octopus Deploy server.

I can watch the progress of the build from VSTS.

2364-build42pc-1769f173-11c0-49bc-a37f-1

Deploying to Octopus Deploy

The final step deploys a build to Octopus Deploy. When I go to Octopus, I can see this release – which is numbered as 0.0.9. It’s only deployed as far as testing. If I want this build to go to Preproduction and Production, I should click ‘Deploy’ to advance through the Octopus environments.

2364-widgetShopReleases-a244a9ac-744e-4d

Within Octopus Deploy, I’ve set up a deployment process that uses Redgate SQL Release to deploy to multiple environments. For a tutorial on how to set this up, see here.

2364-ODprocess-02effab6-11ab-4869-a0fc-f

Tags: , , , ,

  • 8015 views

  • Rate
    [Total: 0    Average: 0/5]
  • philcart

    Great article … but no SQL Source Control

    Hey Jason

    Great job on the article. This is something I’d love to get up and running at work.

    Unfortunately we shelved Redgate SQL Source Control a few months back as it always failed when deploying SQL Server Broker changes. After more than 4 years of waiting for a fix from Red-Gate, SSDT became mature enough that we decided to try it. We can do our database deployments through SSDT without having to rely on a bunch of manual SQL scripts to handle SQL Service Broker changes.

    Is this something that we can approach and put together without using Redgate SQL Source Control?

    Cheers
    Phil