Automated Database Deployments using State Based Redgate SQL Change Automation

Published on: 20 Jul 2018 by: Bob Walker

Introduction

My previous blog posts discussed why you need automated database deployments and tips for getting started down that path. Enough talk, it is time for action! This article will walk through setting up an automated database deployment pipeline using the state based approach for Redgate's SQL Change Automation. I picked this tool to start with because it is easy to setup, integrates with SSMS, and...well...I already had a demo setup. I'm also a little biased towards Redgate's tooling. So there's that.

The end goal of this article is for you to have a working proof of concept for you to demo.

Prep Work

For this demo you will need a SQL Server instance running, an Octopus Deploy instance and a CI server. I recommend using a Dev environment or your local machine for this proof of concept.

Tools Needed

You will need the following tooling. The examples given use TeamCity and VSTS/TFS. As you will see later, the core concepts and UI for all the CI tools will be very similar.

  • Octopus Deploy:
    • Get 45-day free trial for on-premise here.
    • Get 30-day free trial for Octopus Cloud here.
  • Redgate SQL Toolbelt
    • Get 14-day free trial here.
  • CI Tool (pick one):
    • Jenkins - download here.
    • TeamCity - download here.
    • TFS - download here.
    • VSTS - try here.
    • Bamboo - download here.
  • SQL Server Management Studio (SSMS):
    • Download for free here.
  • SQL Server:
    • SQL Express - download here.
    • SQL Developer - download here.

Installing Software

I'm not going to walk you through how to install those tools. This article would be 100 pages long if I did that. If you run into issues please go to the vendor's website for any help. If you need any help installing Octopus Deploy please start with our docs or contact support.

Developer Machine

This is the machine where we will be making the schema changes and checking them into source control. When you install Redgate's SQL Toolbelt you will be prompt to install quite a bit of software. You only need to install the following:

  • SQL Source Control
  • SQL Prompt (it isn't required, but it makes your life so much easier)
  • SSMS Integration Pack

Build Server

Both Octopus Deploy and Redgate have plug-ins for the major build servers.

  • Jenkins:
    • Octopus - download here - Please note, you can have Jenkins interact with Octopus by using octo.exe. You can read more about that here.
    • Redgate - download here.
  • TeamCity:
    • Octopus - download here.
    • RedGate - download here.
  • VSTS/TFS:
    • Octopus - download here.
    • Redgate - download here.
  • Bamboo:
    • Octopus - download here.
    • Redgate - download here.

Deployment Target

Installing an Octopus Tentacle on SQL Server is a big no-no. The documentation goes into further details why. Instead, we will install the tentacle on a jump box which sits between Octopus Deploy and SQL Server. For security purposes, I recommend running the tentacle as a specific user account. This way you can make use of integrated security. Here is some documentation on how to configure that. Keep in mind that only works if you are using Active Directory. If you are not using that, you can still use this process, you will just need to use SQL Users instead.

For the jump box you will need to install the following items:

  • SQL Change Automation PowerShell 3.0.
  • SQL Change Automation.

Sample Project

For this walk-through, I modified the RandomQuotes project used in previous Will It Deploy videos. If you haven't had the chance to check them out you are missing out. Do yourself a favor and watch them. Each episode is around 15 minutes. You can find the playlist here.

The source code for this sample can be found here. You will need to fork this repository so you can make modifications to it later in this article.

Configuring the CI/CD Pipeline

Everything you need is already checked into source control. All we need to do is build it and push it out to the SQL Server.

Octopus Deploy Configuration

You will need the step templates from Redgate to create a database release and deploy a database release. When you are browsing the step template you might notice the step template to deploy directly from a package. The state-based functionality for SQL Change Automation works by comparing the state of the database stored in the NuGet package with the destination database. Each time it runs it creates a new set of delta scripts to apply. Because of that, the recommended process is:

  1. Download the database package onto the jump box.
  2. Create the delta script by comparing the package on the jump box with the database on SQL Server.
  3. Review the delta script (can be skipped in dev).
  4. Run the script on SQL Server using the tentacle on the jump box.

Using the step template to deploy from the package prevents the ability to review the scripts.

This is the process I have put together for deploying databases.

I am a firm believer of having tools handle all the manual work for me. This is why my process will create the main SQL user for the database, the database, add the SQL user to the database, and the user to the role. If you want your process to do that, you can download those step templates from the Octopus Community Step Template Library.

You don't have to add all that functionality. This is the beginning of your automated database deployment journey. Providing that much automation can be very scary without trusting the process. Completely understandable. The main steps you need from the above screenshot are:

Let's go ahead and walk through each one. The download a package step is very straightforward, no custom settings aside from picking the package name.

The Redgate - Create Database Release step is a little more interesting. What trips up most people is the Export Path. The export path is where the delta script will be exported to. This needs to be a directory outside of the Octopus Deploy tentacle folder. This is because the "Redgate - Deploy from Database Release" step needs access to that path and the Tentacle folder will be different for each step.

What I like to do is use a project variable.

The full value of the variable is:

    C:\RedGate\#{Octopus.Project.Name}\#{Octopus.Release.Number}\Database\Export

Other recommendations on this screen:

  • You will also notice I have supplied the username and password. I recommend using integrated security and having the Tentacle running as a specific service account. I don't have Active Directory setup on my test machine so SQL Users it is for this demo.
  • Take a look at the default SQL Compare Options and make sure they match up with what you want. If they don't then you will need to supply the ones you want in the "SQL Compare Options (optional)" variable. You can view the documentation here. If you do decide to go the route of custom options I recommend creating a variable in a library variable set so those options can be shared across many projects.
  • Use a custom filter in the event you want to limit what the deployment process can change. I wrote a lengthy blog post on how to do that here. My personal preference is to filter out all users and let the DBAs manage them. Even better, let Octopus manage them since it can handle environmental differences.

The next step is approving the database release. I recommend creating a custom team to be responsible for this. My preference is to skip this step in Dev and QA.

The create database release step makes use of the artifact functionality built into Octopus Deploy. This allows the approver to download the files and review them.

The final step is deploying the database release. This step takes the delta script in the export data path and runs it on the target server. This is why I recommend putting the export path in a variable.

That is it for the Octopus Deploy configuration. Now it is time to move on to the build server.

Build Server Configuration

For this blog post, I will be using VSTS/TFS and TeamCity. The build should, at a least, do the following:

  1. Build a NuGet package containing the database state using the Redgate plug-in.
  2. Push that package to Octopus Deploy using the Octopus Deploy plug-in.
  3. Create a release for the package which was just pushed using the Octopus Deploy plug-in.
  4. Deploy that release using the Octopus Deploy plug-in.

VSTS / TFS Build

Only three steps are needed in VSTS/TFS to build and deploy a database.

The first step will build the database package from source control. The items highlighted are the ones you need to change. The subfolder path variable is relative. I am using sample Git repo which is why I have the "RedgateSqlChangeAutomationStateBased" folder in the path.

The push package to Octopus step can be a little tricky. You need to know the full path to the artifact generated by the previous step. I'm not 100% sure how you would know without trial and error.

Here is the full value in case you wish to copy it.

    $(Build.Repository.Localpath)\RandomQuotes-SQLChangeAutomation.1.0.$(Build.BuildNumber).nupkg

The Octopus Deploy Server must be configured in VSTS/TFS. You can see how to do that by going to our documentation.

The last step is to create a release and deploy it to dev. After connecting, VSTS/TFS with Octopus Deploy you will be able to read all the project names. You can also configure this step to deploy the release to Dev. Clicking the "Show Deployment Progress" will stop the build and force it to wait on Octopus to complete.

TeamCity

The TeamCity setup is very similar to the VSTS/TFS setup. Only three steps are needed.

The first step, the build database package step, has similar options to VSTS/TFS. You will need to enter in the folder as well as the name of the package.

The kicker is you have to enter in a package version in the advanced options. If you don't then you will start getting random errors from the Redgate tooling saying something about an invalid package version.

The publish package step requires all three of the options to be populated. By default, the Redgate tool will create the NuGet package in the root working directory.

The final step is creating and deploying the release. Very similar to before, you provide the name of the project, the release number and the environment you wish to deploy to.

The CI/CD Pipeline In Action

Now it is time to see all of this in action. For this demo, I will be creating a new database, RandomQuotes_BlogPost_Dev.

As you can see I do not have any databases with that name. You can see I have used this SQL Server as my test bench for automated deployments.

Let's take a quick look at the tables stored in source control.

If we open up one of those files we can see the create script generated by Redgate's SQL Source Control.

Kick off a build and let's see that whole pipeline in action. The build looks successful.

No surprise, the deployment was successful in Octopus Deploy. The VSTS/TFS build was set to wait on Octopus Deploy to finish deploying the database. If the deployment failed the build would've failed.

Going back to SSMS and we can now see the database and the tables have been created.

Changing the Schema

Well, that is all well and good but that was a project already created. Let's make a small change and test the whole process. There is a bit more setup involved with doing this.

  1. Clone your forked repo to your local machine.
  2. Open up SSMS and create a random quotes database on your local machine or dev.
  3. In SSMS bind the source controlled database to the newly created database. You can read how to do that in the documentation.

When linking the database to source control you need to provide the full path to the folder where the source control is stored. I store all my code in a folder called C:\Code.git. So the full path is:

Here is the text of that path for you to copy:

C:\Code.git\AutomatedDatabaseDeploymentsSamples\RedGateSqlChangeAutomationStateBased\db\src\

Once you are finished you should see something like this:

Now we can make the change to the database. For this test, let's just add in a stored procedure which will return a value.

Now we can commit that change to source control.

And assuming the CI/CD build is set to fire on commit you should see that new sproc appears in Dev!

Conclusion

Automating database deployments does require a bit of prep-work but the payoff is well worth the effort. Having the auditing alone is well worth it. With this tool, I can now see who made a change, when a change was made and when that change went into production. In the past, that was kept in another location with a 50/50 shot of it being updated.

As you start down this journey my recommendation is to add the manual verification step to all environments until trust has been established. This will ensure you don't accidentally check in a change which blows away half the team's database changes.

Until next time, happy deployments!


Octopus Deploy is used by thousands of developers across the globe, from small companies to large enterprises. Find out if it meets your deployment automation needs by taking advantage of our free 30-day trial. You can spin up an instance with just a few clicks!