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">
   <metadata>
      <id>Accounts</id>
      <version>1.0.0.0</version>
      <authors>Deirdre OLeary</authors>
      <description>Package for Accounts</description>
   </metadata>
   <files>
      <file src="Deploy.ps1" />
      <file src="\bin\Release\Accounts.dacpac" target="Content\Deploy" />
   </files>
</package>

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:

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).


Add a new project for Accounts.

In the project, add a variable for the Tentacle server name.

In the Project Settings, set “Use the version number from an included NuGet package”.

Add two “Deploy a NuGet Package” steps to the project, one step to deploy Accounts & the other to deploy Accounts_Tests.

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.

Create two build configurations (01 – Build, 02 – Deploy).

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.

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).

Run 01 – Build to see an artifacts dropdown which should contain the .nupkg files, Deploy.ps1 files & dacpacs.

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.

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.

Add an “OctopusDeploy: Create Release” step to the 02 – Deploy configuration. Use the Octopus ApiKey generated above.

Add dependencies to the 02 – Deploy configuration.

Add a trigger to the 02 – Deploy configuration.

Run 01 – Build & it will trigger 02 – Deploy on completion, deploying both databases to the Tentacle server.

---

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).