tSQLt Part 2: Deploying tests

Aim: Deploy the tSQLt framework & tests using TeamCity & Octopus Deploy.

In production, you may have separate TeamCity, Octopus Server & Tentacle environments. However, as this is a basic setup for testing purposes, I will install all three on the same machine as my local SQL Server instance. For clarity, I will refer to the server that we are deploying the databases to as the Tentacle server.

Prepare the SSDT solution & SQL Server instance for deployment:
Open the Accounts SSDT solution in Visual Studio. Open the Package Manager Console & install Octopack. Octopack creates NuGet packages (.nupkg) that are compatible with Octopus & will be used to create Nuget packages which contain the dacpacs for each project.
Here the version is specified so that all projects in TeamCity are using the same version however you can install the latest by leaving out the version.

PM > Install-Package Octopack -Version 3.0.27

To prompt the Accounts & Accounts_Tests projects to use Octopack, open each .sqlproj file in a text editor & add the following to the end of the file (just before </Project>).

<Import Project="$(SolutionDir)\packages\OctoPack.3.0.27\tools\OctoPack.targets" />

Add a Deploy.ps1 file to the Accounts project. This PowerShell file is used by Octopus to deploy Accounts.dacpac from the Nuget Package to the database. It should contain:

# Set params
$buildName = "Accounts"
$DatabaseName = "Accounts"

Write-Host "This build will deploy: " $buildName " to Server: " $ServerName

# load in DAC DLL
Add-Type -path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll"

# make DacServices object
$d = New-Object Microsoft.SqlServer.Dac.DacServices "server=$ServerName; User ID=OctopusLogin; Password=password";
$dacpac = (Get-Location).Path + "\Content\Deploy\" + $buildName + ".dacpac"

# Load dacpac from file
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)

# Set the DacDeployOptions
$options = New-Object Microsoft.SqlServer.Dac.DacDeployOptions -Property @{
   'BlockOnPossibleDataLoss' = $true;
   'DropObjectsNotInSource' = $false;
   'ScriptDatabaseOptions' = $true;

# Deploy the dacpac
$d.Deploy($dp, $DatabaseName, $true, $options)

Add a similar file to the Accounts_Tests project, updating the $buildName & $DatabaseName parameters. Retain the password for OctopusLogin as it will be created on the destination SQL Server instance later in the process & Octopus will use it to create the database objects, run scripts, etc.

Add an Accounts.nuspec file to the same directory as Accounts.sqlproj. The .nuspec file must be in the same directory as the .sqlproj file & must have the same name. The .nuspec file describes the NuGet package that will be created for the Accounts project. It should contain:

<?xml version="1.0"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
      <authors>Deirdre OLeary</authors>
      <description>Package for Accounts</description>
      <file src="Deploy.ps1" />
      <file src="\bin\Release\Accounts.dacpac" target="Content\Deploy" />

Add a similar file for Accounts_Tests, updating id, description & file src.

If you have NuGet Package Explorer installed, you can check the .nuspec files by opening them. You should see:
11 nuspec_file
You can also run msbuild Accounts.sln /t:Build /p:RunOctoPack=true. If you do, you should see octopacked & octopacking directories in the obj directory & they should contain .nupkg & .nuspec files respectively. There should also be a .nupkg file in bin/Release.

In tSQLt Part 1: tSQLt & SSDT, I added a post-deployment script to Accounts_Tests which runs all tests at the end of the deployment. Remove this script as the tests will now be run by a separate build step in TeamCity instead.

Finally, add the Accounts & Accounts_Test projects changes to version control.

Set up Octopus Deploy:
Octopus has two main components:

  • Octopus Server which includes the web portal and central orchestration service.
  • Tentacle which is the deployment agent service.

On the Tentacle server, install .NET 3.5 & 4.5. If running Windows Server 2008 R2, install Powershell 3 (http://www.microsoft.com/en-us/download/details.aspx?id=34595).
Install Octopus Server & Tentacle from http://octopusdeploy.com/downloads with default settings (e.g. listening tentacle). Set a suitable port for Octopus (e.g. 82) so the interface is http://localhost:82/.

The following changes are made via the Octopus Server interface:
Create an environment for the Tentacle server (e.g. Local) & create a new machine in that environment with a suitable role (e.g. Testing).
12 octopus_new_machine
13 octopus_machine_settings
Add a new project for Accounts.
14 octopus_new_project
In the project, add a variable for the Tentacle server name.
15 octopus_variables
In the Project Settings, set “Use the version number from an included NuGet package”.
17 octopus_project_settings
Add two “Deploy a NuGet Package” steps to the project, one step to deploy Accounts & the other to deploy Accounts_Tests.
16 octopus_project_step_1
In the User page, generate a new ApiKey & take note of it.

In SSMS, create a new SQL login to match the one used in Deploy.ps1 (e.g. OctopusLogin). Give the login the required permissions to deploy the database & objects (e.g. sysadmin).

Set up TeamCity:
TeamCity builds the projects independently of the environments to be deployed to, coordinates the deployments & runs the tests.
Install TeamCity from http://www.jetbrains.com/teamcity/download/. Set a suitable port for TeamCity (e.g. 81) so the interface is http://localhost:81/.
Download the TeamCity plugin from Octopus: http://octopusdeploy.com/downloads. Via Services, stop the TeamCity Server. Paste Octopus.TeamCity.zip to C:\ProgramData\JetBrains\TeamCity\plugins. Start the TeamCity Server again.

The following changes are made via the TeamCity interface:
Create a new build project.
18 tc_new_project
Create two build configurations (01 – Build, 02 – Deploy).
19 tc_new_build_config
Attach a new VCS root for Accounts to the 01 – Build configuration. This will allow Team City to get the latest changes from version control when building.
20 tc_new_vcs_root
Add a “Visual Studio (sln)” step to the 01 – Build configuration. This step will create the .nupkg files containing the dacpacs. Tick “Run Octopack” & set the Octopack Package Version to the build number. This must be multi-part so if the build is a single number, set the Octopus Package Version to 1.0.%build.number% (or something similar).
21 tc_build_step_1
Run 01 – Build to see an artifacts dropdown which should contain the .nupkg files, Deploy.ps1 files & dacpacs.
22 tc_artifacts
Add a “NuGet Publish” step to the 02 – Deploy configuration. This step will publish the .nupkg files to the repository so that Octopus can deploy them. If this is the first time using this runner, you may have to fetch NuGet.exe & set the default path in the settings. You may also need to register at nuget.org to get an ApiKey.
23 tc_build_step_2
For the above step, if you use the built-in Octopus repository, get the Package Source from the Octopus interface (Library > Packages) & use the Octopus ApiKey generated above.
24 octopus_package_source
Add an “OctopusDeploy: Create Release” step to the 02 – Deploy configuration. Use the Octopus ApiKey generated above.
25 tc_deploy_to_local
Add dependencies to the 02 – Deploy configuration.
26 tc_dependencies_1
Add a trigger to the 02 – Deploy configuration.
27 tc_trigger_1
Run 01 – Build & it will trigger 02 – Deploy on completion, deploying both databases to the Tentacle server.
28 tc_success

Next: I plan to add a configuration to TeamCity to run the tSQLt tests automatically following each successful build & deploy.

Note: If the database does not exist on the Tentacle server, Octopus will automatically create it (using SQL Server instance defaults).



  1. […] a previous post on deploying databases using TeamCity & Octopus, I used a PowerShell script (Deploy.ps1) to […]

  2. One important point I forgot to make in the above post. When adding Deploy.ps1 to the projects, make sure that the “Copy To Output Directory” property is set to “Copy Always” or “Copy If Newer”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional


Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert


A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: