10 July 2013

Continuous Integration for SQL Server Part III – Deployment

Update: 31st March 2015

Since writing this article, a number of Redgate product development changes have taken place. Some of the references in the article are now out of date, so here’s a summary of the changes that have taken place:

  1. Redgate no longer sells Deployment Manager. As an alternative, everything described here can be set up using Octopus Deploy - http://octopusdeploy.com/.
  1. The sqlCI tool referenced in the article has been replaced by SQL CI, part of DLM Automation. SQL CI works with Octopus Deploy and other release management tools to build, test and package your database changes. It can synchronize your database changes with other environments, such as QA, or publish the packaged database changes to a NuGet feed for your release management tools to pick up for future deployments.
  2. Redgate now offers SQL Release as part of the DLM Automation. SQL Release uses a set of PowerShell cmdlets, so you can integrate it with any release management tool, including Octopus Deploy. It means you can automate the deployment of database changes efficiently and safely, with the option to introduce an approval step as part of the deployment process for teams to review update scripts before they are deployed to pre-production and production environments.

If you have any questions about automating the deployment of Database Projects using the latest versions of our database lifecycle management tools, please contact the DLM team for more information or an online demo: dlm@red-gate.com

Original article

In this article, I’ll describe the next stage in implementing a full Continuous Integration and Deployment process for SQL Server databases: Deployment.

This post follows on directly from my previous post on Continuous Integration and testing for SQL Server. Following the steps in this previous post is a pre-requisite to following the steps below.


So we now have tSQLt tests running on our Bamboo CI server so that if a developer (e.g. me!) introduces an error (or breaks a policy), then this fails the build.

But what if then want to deploy these builds to a remote server? The next stage is to use another Redgate tool, Deployment Manager, to get these builds out to the production box (for example). NB: The example used here is a very simple example of how to use a tool to deploy to a particular environment. Though I’ve named the target environment “Production”, we would never advise using a dynamic database upgrade process, as described here, for a production environment (Ask your DBA if you doubt me!). However, this is a process you could use for a pre-prod environment or, QA, for example.

Installing Deployment Manager

  1. First, download the product from the product page by clicking on the link GetDM.png.
  2. Double-click and run the downloaded MSI. I won’t show all of the steps here, but just accepting the defaults and clicking Yes when asked should, for most people get you up and running. Specifically, when asked, use the default NuGet feed provided by Deployment Manager.
  3. When asked to create an administrator account, enter an email address and password for an admin account:



  4. The next step is to create some environments (these are groups of related machines – see below), by clicking on creating some environments then Add environment:



  5. Click Create, then click on Add Target Machine to add an actual target box to this environment, for deploying our database. The next box provides a process for setting up firstly the Deployment Manager agent for the target box, and also for setting up the secure connection between boxes (if applicable). In this next box, I first download the Agent Installer on to the target machine by clicking the Download Agent installer link at the top. Also note, there will be a Server Key in this screen (here, blurred out), which will come in useful later:



  6. Once the agent MSI is downloaded on the target machine, double-click to install – again, a very short and simple process. At the end of the install process, you’ll be shown a final screen containing an Agent Key and a box for Server Keys. Click the Add button, and then cut and paste your Server key from the previous screen here and click Save:



  7. Once you’ve done this, you need to return to the previous screen (from step 5 above), and copy and paste the Agent Key from the target machine above in to your “Add target machine” screen, as well as entering the machine name and URL for the Agent:



  8. Once you’ve clicked Create, you can run a Health Check on the next screen if you wish, by clicking on Check health:


  9. We now have an Environment and a Target Machine set up. Next stage, is to add a Project. Click on Projects at the top, then the Create project button:



  10. Create a project called RedGateApp and Save this. Then click on Add package step:


    At this point, there are a large number of ways you can create a (NuGet) package to be deployed by Deployment Manager. Clicking on Deployment Manager publishing tools will show a number of options, for example:

    SQL Server Management Studio Add-in

    This add-in allows you to deploy databases straight out of SSMS:


    Further information can be found here.

    Command Line Interface

    This is a versatile command line tool that allows you to create packages yourself from within various tools. Further information can be found here.

    Visual Studio Add-in

    This add-in allows you to create a package easily and quickly straight out of VS:


    More info can be found here.

  11. However, we’re going to be using the sqlCI tool that will publish to a NuGet feed (for deployment by Deployment Manager) for us. To do this, open the sqlCI.targets file that we’ve been using to configure our build, and make the following amendments (your own values, in red) – the explanation of these values is below:

    What these values mean: <packageRepository> – this is the URL of the NuGet feed where the output from the CI process will place the package. Deployment Manager works by taking packages (NuGet packages, to be specific) from a feed and then allowing the user to release these to different environments. However, we need to first place packages on those feeds – here is where we specify where sqlCI should place the packages. <databaseUserName> and <databasePassword> – these are the credentials required to temporarily create a database to run the tSQLt tests.<packageID> and <packageVersion> – these are important for identifying the packages created by sqlCI in the Deployment Manager (DM) interface (i.e. in DM, we need to be able to say “I want this package for my deployment”)

  12. Once these changes have been made, again, check these in to GitHub and sync. Then, re-run the build. You’ll see something like the following:


    – a successful build. However, there’s not really much indication here of what has happened!

  13. To find out what happened to the package that we have (in theory!) created and sent to the feed, we need to return to the Deployment Manager interface. Returning to the screen from step 10 above:



  14. If you start typing in the Package section, you recently created package name should appear:



  15. Select “RedGateApp” and, additionally, select the “Production” environment and click Add>> to move it to the Included deployment targets. Click Save:



  16. The next step is to add a number of variables for the project, that can be added in to the releases:


    As can be seen, most of these variables are specifying the database location and credentials needed to access the database on the production box. There is an additional variable - allowDynamicDatabaseUpgrade – which allows Deployment Manager to carry out a dynamic database upgrade on the production database (using the Redgate SQL Compare engine). NB: This isn’t, generally, advisable for production deployments! Instead, it’s also possible to use a static database upgrade process, using pre-created upgrade scripts based on source controlled versions of the database. Further details can be found on the following two help pages:



  17. Additionally, the database needs to be set up on the Production box. On the production box, open SQL Server Management Studio (SSMS), connect to the relevant server, and click “New Database…”:



  18. Create a new database called “RedGateAppProd” and click OK:


  19. We also need to create the relevant user for Deployment Manager to access the new database. Again, in SSMS, expan the Security tab and create new login:


  20. Create a new login named “Bamboo”, password “Bamboo” and uncheck the “Enforce password policy” checkbox. Click OK:



  21. Next, return to Deployment Manager. If you click on Projects (in the top nav), then on “RedGateApp”, this will show you a screen that (finally!) allows you to create a release to Production:


  22. Click on “Create release” then enter some Release Notes and click Save:


  23. Click “Deploy this release” on the following page:


  24. ..enter some Comments and click “Deploy release”:


  25. You’ll then see Deployment Manager proceeding through the deployment steps:


  26. In theory, success! But, we need to check – returning to SSMS on the production box – previously the “RedGateAppProd” database was empty (we only just created it). What’s there now?:


    The Beatles have arrived!



This has been a very quick run through the implementation of CI for databases, followed by a managed release process using Deployment Manager. For the latter, in particular, I’ve barely touched on the proper process of updating target database environments in different ways. For example, two options for carrying out this process are to implement dynamic updates (as described here), using the SQL Compare engine, but another alternative is to use static upgrade scripts, based on version-controlled versions of the database. More details can be found at http://documentation.red-gate.com/display/DM2/Working+with+database+packages#Workingwithdatabasepackages-HowDeploymentManagerupgradesdatabases.

The next post in this series will focus on the final part of Continuous Delivery – monitoring what happens after the deployment. How do you know if your deployments worked?

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


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