SQL Server Database :: Continuous Deployment using Github, TeamCity and Octopus

Introduction

In my previous article, we have seen how to implement a continuous integration system for Microsoft SQL Server databases in addition to having a source control system. At the end of the article, we established that a continuous deployment system is essential for smooth delivery of modifications in a project.

Again, as mentioned in the previous article, there are several tools available to implement continuous deployment but for this course, let us take Octopus Deploy and learn how to set up a pipeline for continuous delivery of your code to various environments.

Background

Database deployment is not as straightforward as application deployment as many factors like schema changes, data changes, data persistence and other miscellaneous factors such as deployment downtime, fear of changes, etc., take over. These are few of the complexities because of which several organizations choose to stick to manual deployment of database changes.

But through the course of this tutorial, let us try to simplify the setup of a continuous delivery pipeline and see how we can reduce the manual intervention and eliminate possible errors and learn how to mitigate a deployment failure.

Prerequisites

In order to proceed with this step, you should be ready with a continuous integration system for your database project. In case you do not have it yet, please look at the previous article that I wrote.

• SQL Server Database Continuous Integration using Github and TeamCity

If you have successfully completed that step, let us move on to the next and final step in this long process.

3. SQL Server Database Continuous Delievery using Github, TeamCity and Octopus

a. Understanding Deployment Procedure

Before we move forward with this course, it is important to understand the basic steps involved in the deployment process of a database. The whole process can be broadly divided into three main steps:

• Creating a change script for the latest changes in the database
• Creating and validating an artifact (a NuGet package or a zip) containing the database changes
• Publishing the created artifact to different environments and automating the procedure

b. Setting Up Octopus Server

If you have an Octopus Deploy server that you are already using for deploying your application, you can create another project on the same server for your database project deployment. If you do not have a server installed, go ahead and download Octopus Server and Octopus Tentacle from the official website and install them. Octopus server needs to be installed somewhere in an appropriate location where you can push published packages from TeamCity. Mind that Octopus Tentacle needs to be installed on each server that you are going to deploy your database on viz., Test, Staging, Production etc.,

As is the case with TeamCity, there are several online tutorials about how to setup Octopus Server and Octopus Tentacle. The official documentation contains detailed a step-by-step tutorial about how to install both of them.

If your installation is complete, you should be able to see Octopus Manager and Octopus Tentacle Manager to look somewhat like this:

On Octopus Manager, click on the link to browse the Web Portal. This action will open your browser and you should be able to see Octopus Server dashboard where you configure your deployment procedure.

The first thing that we want to do here is setup environments for each of the phases involved in our project delivery. Let us consider that there are four phases in our delivery, say, Development, Testing, Staging and Production. We need to setup four environments in Octopus Server. On the navigation bar, click on Infrastructure tab.

On the next screen, click on the Add Environments button. If your screen looks different from what is shown in the below picture, go to Environments tab and click Add Environments button.

Specify appropriate name for the environment and click on Save button.

That should add a deployment target for Development. Follow the same procedure and repeat steps to create three more environments for Test, Staging and Production.

c. Setting Up Tentacles

The next step is to create and specify deployment targets for each of the environments. As we already have Octopus Tentacle installed on each of the Development, Test, Staging and Production server, they are going to be our deployment targets and we will specify them here.

Click on ‘Add Deployment Target’ button.

On the next page, choose the type of your deployment target. In this example, the Octopus Tentacle is hosted on a local machine running Windows. Select it according to where your deployment target is hosted. Once you select the type of deployment target, you will be asked to select the type of target. Hover on the Listening Tentacle panel and click on the Add button.

On the next screen, you can see the installation instructions for the tentacle. If you have not already installed tentacle manager on the deployment targets, download tentacle and install there. This page shows a Thumbprint which is specific to the Octopus Server. We should provide this thumbprint while creating a tentacle on the deployment target and the created tentacle will have its own thumbprint which we will have to specify in the next step of creating a deployment target. Confusing? Don’t worry. Let us come back to this shortly.

For now, copy the thumbprint and open Tentacle Manager. Here, we create a tentacle to connect between the Octopus Server and deployment target for Development environment.

Click on the list of Tentacles and select New Tentacle to add a new tentacle.

Next, give a name for the instance, Development in this case and click Next.

It will take you to the welcome screen of the newly created tentacle and click on Get Started to proceed with configuration.

On the next screen, select communication style as Listening tentacle and proceed with Next.

For Storage, leave it to default and click Next.

In the Listening Tentacle step, choose the port on which you want the Tentacle Service to listen to. Default value is 10933. In the thumbprint box, paste the thumbprint that you copied from the Octopus server dashboard and click Next. This thumbprint is specific to the server and will be the same for all the tentacles that you will create as deployment targets.

On the next step, click on Install button and the new tentacle will be installed and tentacle service will be started.

After installation is complete, click on Finish and information about the newly created tentacle will be displayed on the Tentacle Manager. The status of the tentacle is running and if you observe, the tentacle has its own thumbprint. This is specific to this tentacle and each tentacle created will have a unique thumbprint.

Copy this thumbprint and go back to the Octopus Server dashboard and click Next.

Octopus will ask for a display name for the listening tentacle. Provide a suitable name, set the target environment and role. If you do not have any roles created in Octopus yet, you can type in the input box and create one. Assign the roles and if you scroll down, you can see that Octopus server has already populated the thumbprint of listening tentacle and the URL.

Click on Save button and then go to Infrastructure tab. You should be able to see that a new listening tentacle has been created.

Repeat the procedure to create deployment targets for Test, Staging and Production and set them to respective environments.

d. Creating an Octopus Project

Next step is to create a project in Octopus. In order to do that, we need to define a deployment life cycle in Octopus for our project. For that, click on Library on the navigation bar and go to Lifecycles tab. Here, you can add a new lifecycle.

Provide a suitable name and description for the lifecycle and save it.

The final part is to add a new project in Octopus and set it to follow this deployment lifecycle. From the navigation bar, go to Projects tab and click on Add Project button. It will open a popup asking for project name. Input your project’s name and save the project.

With that, your project is displayed under Projects.

Let us pause with Octopus setup for now and work on building a pipeline between TeamCity and Octopus Deploy.

e. Creating a Pipeline between TeamCity and Octopus

In this setup of CI/CD pipeline, TeamCity is usually responsible for checking changes in source control, compiling the source code and creating NuGet packages for deployment. These packages will be pushed to Octopus, which will take them and push them to development, test, staging and production environments.

i. Installing Octopus Deploy Integration Plugin

For creating a pipeline between these two tools, we can use a TeamCity plugin called Octopus deploy integration. This can be obtained from the JetBrains plugin repository or from Octopus downloads page. Let us walk through the setup in the coming section but if you want to know more about the plugin, you can visit the official page on Octopus’ website here.

To install this plugin from TeamCity, open TeamCity, click on Administration and select Plugins-List from the sidebar. On the page that comes up, click on Browse plugins repository and click on Proceed.

This will take you to the official TeamCity plugins repository. In this repository, search for the plugin called Octopus Deploy Integration and select the item from the search list.

From here, you can click on Get button and install it on to your TeamCity server.

You should be taken back to TeamCity and you can select to install the plugin.

The plugin will be uploaded to TeamCity and it needs to be enabled. Enable the newly uploaded plugin and confirm if TeamCity asks for any confirmation. It is recommended that you restart TeamCity server after installing the plugin.

If the installation is successful, you should be able to see Octopus Deploy Integration plugin listed in External Plugins list.

ii. Installing NuGet

Since our process involves publishing code as NuGet packages, we must enable NuGet in TeamCity. Go to Administration->Tools->Install Tool.

From the dropdown, select NuGet.exe and choose the latest available stable version and install.

If the installation is successful, you should see NuGet listed under Tools.

iii. Creating a NuGet specification

To build a NuGet package from database project, we need to specify the package metadata in a .nuspec file. If you do not know what a nuspec file is, consider it as a specification for NuGet to publish your package. This is the most important component of a NuGet package and it is an XML file with .nuspec extension that represents your package and contains all the information from Nuget.org.

Whilst not essential, it makes life somewhat easier if you include the .nuspec file in the root of your project.

Add a new file to the root folder of your project with the name as <ProjectName>.nuspec.

In this nuspec file, copy and paste this code:

Replace [ProjectName], [YourName] and [YourDescription] with your corresponding values. It should look somewhat like this:

In the <files> section, observe that there are references to two files. This means that we are telling NuGet to include these two files while packaging. The .dacpac file contains the definition of all SQL server objects in our database and the Deploy.ps1 file is a Powershell script that we want Octopus to execute after picking up the package. The code written in this Powershell script creates an SQL server script out of the dacpac and will execute it on the Octopus server.

Add another file to the project with the name ‘Deploy.ps1’ and paste this script into the file.

Quote:

# -------------- 
# Set params
# --------------
 
$BuildName = $OctopusParameters["BuildName"]
$ServerName = $OctopusParameters["ServerName"]
$DatabaseName = $OctopusParameters["DatabaseName"]
$DbUsername = $OctopusParameters["DbUsername"]
$DbPassword = $OctopusParameters["DbPassword"]
 
$ConnectionString = "Server= $ServerName; Database= $DatabaseName; User ID= $DbUsername; Password= $DbPassword"
 
Write-Host "This build will deploy: " $BuildName " to Server: " $ServerName
 
try {
    # Load in DAC DLL (requires config file to support .NET 4.0)
    Add-Type -path "C:Program FilesMicrosoft SQL Serv-er150DACbinMicrosoft.SqlServer.Dac.dll"
 
    # Make DacServices object
    #$d = New-Object Microsoft.SqlServer.Dac.DacServices "Server = $ServerName; 
    Data-base = $DatabaseName; Integrated Security = True;";
    $d = New-Object Microsoft.SqlServer.Dac.DacServices $ConnectionString
 
    # Register events (this will write info messages to the Task Log)
    Register-ObjectEvent -in $d -EventName Message -Source "msg" 
    -Action { Out-Host -in $Event.SourceArgs[1].Message.Message} | Out-Null
 
    # Get dacpac file
    $dacpac = (Get-Location).Path + "Content" + $BuildName + ".dacpac"
    # 'Content' is from the nuget package that is created. So, you cannot see it on the local directory
 
    # Load dacpac from file & deploy to database
    $dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)
 
    # Set the DacDeployOptions
    $options = New-Object Microsoft.SqlServer.Dac.DacDeployOptions -Property @{
       'BlockOnPossibleDataLoss' = $true;
       'DropObjectsNotInSource' = $false;
       'ScriptDatabaseOptions' = $true;
    }
 
    Write-Host "Generating deployment script"
 
    # Generate the deployment script
    $deployScriptName = $BuildName + ".sql"
    $deployScript = $d.GenerateDeployScript($dp, $DatabaseName, $options)
 
    # Write the script out to a file
    $deployScript | Out-File $deployScriptName
 
    Write-Host "Deploying dacpac"
    # Deploy the dacpac
    $d.Deploy($dp, $DatabaseName, $true, $options)
 
    # Clean up event
    Unregister-Event -Source "msg"
 
    Write-Host "Successfully deployed"
    exit 0 # Success
}
catch {
    Write-Host ($_ | ConvertTo-Json)
     
    # Called on terminating error. $_ will contain details
    exit 1 # Failure
}

In the properties of Deploy.ps1, set ‘Copy to Output Directory’ as ‘Copy always’ or ‘Copy if newer’ because we would want the changes in this script to go into the published package whenever a change is made to the script. Rename the project name with your Octopus project name in variable declarations at the top. We are doing this so that we do not have to use different deployment scripts for each environment. We can set variables in Octopus and read values from there. Let us get into the details of variable creation in further sections. On line 17, change the path to the location of Microsoft.SqlServer.Dac.dll on the server on which Octopus is running. If you do not find the file, you will have to download and install sqlpackage.

After making the changes, add both the newly created files (.nuspec file and Deploy.ps1) to source control and check in your code. This should trigger a build in TeamCity.

Let us now go ahead and add build steps in TeamCity to create NuGet packages from the .nuspec file that we created.

iv. Adding build-steps in TeamCity

We should now add a couple of steps in the build process of TeamCity. One to create a NuGet package from our database/SSDT project and the other to push the built NuGet package to Octopus. To do this, let us edit the project’s build settings. Go to Projects and hover on the caret beside your build. From the dropdown, select Edit Settings.

Select Build Step and click on Add build step button.

Select the runner type as NuGet Pack and give a name for the build step. Under package manager, we need to specify which specification file needs to be used for building the package. From the directory structure, select the nuspec file that we created in the previous section.

Specify the directory for which you want the artifacts to be published to. You can also leave this to default. Check the box where it says Publish created packages to build artifacts and Save.

What this step essentially means that whenever TeamCity performs a build, the project is compiled into an artifact containing a NuGet package which can be pushed to Octopus. Let us verify if it works correctly. Go to Projects and click on Deploy button beside your build step. This will rebuild your code.

When the build is complete, you can see that the artifacts icon is highlighted. Until now, the icon was light in color because TeamCity was not building any artifacts but since we have a NuGet package, the icon color is darker and if you hover on the icon, it will display all the files and folders available in the artifact.

You can see that a dacpac is created in Content folder and the deployment script (Deploy.ps1) is also in the package. This package is ready to be moved to Octopus and there you can create releases and automate them to be deployed to various targets.

To achieve this, we need to add another build step in TeamCity. Before that, we need an API key from Octopus. Go back to Octopus Server web portal and click on your username at the top right section of the page. Click on Profile and on the next page, go to My API Keys and click on New API Key.

Mention appropriate purpose and click on Generate New button.

Copy your API key and go to TeamCity portal again.

Add a new build step with the following configuration.

Set the runner type as OctopusDeploy: Push Packages, provide a name for the step and provide the credentials to Octopus server. Paste the API key that was generated in the previous step. In the package path, let us set a pattern so that whenever there is a new build, only the latest built package will be pushed to Octopus library.

The package path pattern in this case is telling TeamCity to pick the latest built nupkg file from the output folder, i.e., nuget folder that we mentioned in previous build step. If you mentioned a different path while creating the NuGet specification, make sure that you are pointing the path accordingly.

Further, click on Show Advanced Settings link and check the box beside Publish packages as build artifacts and change overwrite mode if you want to replace the existing package if there is an existing package with the same name.

Save the settings and now you should see that a new build step is added.

With this step, the pipeline is set between TeamCity and Octopus to build project into a NuGet package and push the package to Octopus. Let us test if it is working.

Go to TeamCity and click on Deploy button.

If the build is successful, you should be able to see the compiled NuGet package in Octopus Package Library. To verify, go to Octopus->Library->Packages. There is the latest package pushed from TeamCity.

So, our code is getting pushed to Octopus without any manual intervention. What is left to do is automatically creating releases and deploying to various environments.

f. Configuring Variables in Octopus

Before going forward, there is one more configuration that is left to do. In the Deploy.ps1 script, we wrote code such that the server credentials are picked from Octopus itself so that we can use the same script for all the environments. What we need to do now is to create variables in Octopus and assign different values for them in different environments.

In Octopus, go to Projects, select your project and go to Variables. Start typing in Enter new variable text box and add these five variables:

• BuildName
• ServerName
• DatabaseName
• DbUsername
• DbPassword

Enter values for each variable and set the scope for which the value is valid. You may use the same value for all the environments (or) you may add a different value for each environment for a variable.

Save to update the details. By doing this, we are preventing the need to use different deployment scripts/credentials for different environments. It is the same script for all environments now.

g. Defining a Deployment Process in Octopus

In the same tab, go to Process section and click on Add Step button.

In the subsequent window, choose the step template as Package and select Deploy a Package from installed step templates.

Name the step and assign the target role for this deployment step.

Under package details, we specify the ID of the package that we want Octopus to pick from the NuGet library. Write your project name there, leave everything else to default values and save your configuration.

A step is added to the deployment process and we are good to continue.

h. Automating Releases

The next thing we would need to do is to create automatic releases whenever there is a new package available in the Octopus Package Library. We can do it by creating a Trigger in our project.

In the same tab, switch to Trigger and click on the Setup button under Automatic Release Creation. In the package step dropdown, select the package that you created just now and click on the Save button.

We should also create a trigger to trigger deployment whenever there is a package ready for release. In the same tab, click on Add Trigger and in the dropdown, select Deployment Target Trigger.

Set the name and for Event filters, select Machine becomes available for deployment. This selects all the necessary events required to keep deployment targets up to date.

Save the trigger.

i. Testing the Deployment Pipeline

Go to TeamCity and click on Deploy button of your project’s build step.

Switch to Octopus web portal and click on your project. Surprise, surprise! There is a release available to be deployed to your development environment.

If you click on the Deploy button, your database will be deployed to the development server. We are almost there but ideally you would not want to click on the button every time you check in the code. Would you?

Let us look at how you can automate this process further.

j. Automating Deployment to Development Environment

Automating every new release to be deployed needs us to add a phase to the deployment lifecycle in Octopus.

On the web portal, go to Library->Lifecycles and click on the deployment lifecycle you created at the early stages of setting up the Octopus server.

Under the deployment lifecycle, in the Phases section, click on Add Phase button. Specify a name for the phase.

Scroll down and click on Add Environment and on the popup window, select the environment that you want the new release to be automatically deployed to. In this case, we want the release to be deployed to Development environment.

Select the checkbox that says, Deploy automatically to this environment as soon as the release enters this phase and click OK. This is the setting that does the magic for us. We will also need to add other environments and set if we want the release to be deployed manually or if you need any manual intervention to roll the deployment from one environment to the other. In a real-world project, ideally the Quality Assurance team will be moving the deployment from one phase to another after all the quality checks. So, we leave the deployment in all other environments to be queued manually.

Save the changes that you made to the deployment lifecycle.

Let us test if the process works. Go to TeamCity and click Deploy. Wait till the build process is complete and the package is pushed to Octopus.

Switch to Octopus portal and…

Hurray!

Our database is deployed successfully. That’s it. The pipeline is completely setup.

Fulfilling the Use Case

So far, we have done a lot of installation, configuration and some coding. What is the fruit of our efforts? Is it worth going through this long procedure?

Revisiting our use case that we defined at the beginning, since the system is setup, if we make a slight change in code and check in to our Github repository, the project should be automatically built in TeamCity and the database should be deployed on the server by Octopus. It should be then available to be moved to the next phases. Did we really achieve it? Let us see.

Change something in the project. Like, add a new table and check in the code.

Wait for some time and check your database server.

The new table is there. Remember that we just checked in the code. As we did earlier, we neither built the project in Visual Studio nor did we publish it. It is a simple check-in and the code is automatically deployed on the server.

In TeamCity, the code is checked and there is a source control with entire history of changes by all the developers in your team.

Conclusion

Huh! That is the end of the tutorial.

Now, just write code and check in and it ready to be moved from phase to phase with the comfort of clicking a single button. If you ever ran into the nightmare of committing a manual error while executing the change scripts of a database while deployment, you will understand how fruitful your efforts are with this course.

Points of Interest

There are several tutorials available on the internet for creating a CICD pipeline for web applications, but rarely do you see an article about how it can be done for databases. Even if you find any, they are very old and some of the methods involved in the process are confusing, not fully automatic and not scalable.

This course is made by demonstrating the easiest possible methods and is perhaps the most comprehensive guide written on the topic of source control, continuous integration and continuous deployment for SQL Server databases. The entire guide/course/tutorial is broken down into various steps and screenshots are provided at every possible step.

At the end of this, I would still like to emphasize that this is a simplification of the scenario that you would face in a real-world organization. Several factors would come into place such as having different dockers for each environment, having to use a VPN/proxy to be able to access the database server, limiting access to deployment based on user roles, etc. Although the infrastructure can get complicated, the method for creating the pipeline is still the same. You can configure these settings in respective layers. I am pretty sure that you will figure it out.

Thank you for reading. Hope you learned a lot and wishing you happy development and easy deployment.

History

• 20^th October, 2019: Initial draft of this article