Deploying Database Projects with Octopus Deploy

Are you interested in automating your deployment process? Leave a comment and I would be more than happy to help you along the path, you won’t look back!

At DrDoctor we are currently under going a massive initiative to automate our deployment process. We have made excellent progress over the last couple of months,  and as it stands we can confidently and effortlessly release new versions of the various software components of the system (IIS websites, Windows services).

My colleague Steve has written on the stack which we are using to automate, at the core of it is TeamCity and Octopus Deploy.

From the beginning of this initiative there has always been an elephant in the room, a task that no one has wanted to tackle nor known how to. You can probably guess from the title of this post that I’m taking about database deployments.

At the moment the database upgrade part of our process looks like this:

1. Build the SQL Server Database Project in Visual Studio
2. Find the output directory of the SQL project
3. Connect to the remote server
4. Copy the DacPac file to the remote server
5. Look up syntax for SqlPackage.exe
6. Generate update script (using SqlPackage.exe)
7. Look over script in management studio
8. Run script

As you can see there are a lot of steps and one needs to remember various things, like the syntax for SqlPackage (I’ve just about memorised it now).

We are using Octopus Deploy as our deployment system, it works by copying NuGet packages to remote servers and then uses PowerShell scripts to install and configure. We also use TeamCity as our build server.

The guys at octopus deploy have a handy NuGet package which you install into the projects you want Octopus to deploy, and during the Build stage in TeamCity it will then turn these projects into NuGet packages, ready to be deployed.

Unfortunately there is no way to install NuGet packages into SQL Server Database Project types (not that I could find anyway), there are two ways of turning your database project into a NuGet package on TeamCity.

The first is to manually edit the project file adding the targets for OctoPack, the other method is to use a NuSpec file and the NuGet Package build step in TeamCity. The following snippet needs to be pasted at the very bottom of the .sqlproj file right before the tag.

1
2
3
4
5

<Import Project="..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets" Condition="Exists('..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets')" />
<Target Name="EnsureOctoPackImported" BeforeTargets="BeforeBuild" Condition="'$(OctoPackImported)' == ''">
  <Error Condition="!Exists('..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets') And ('$(RunOctoPack)' != '' And $(RunOctoPack))" Text="You are trying to build with OctoPack, but the NuGet targets file that OctoPack depends on is not available on this computer. This is probably because the OctoPack package has not been committed to source control, or NuGet Package Restore is not enabled. Please enable NuGet Package Restore to download them. For more information, see http://go.microsoft.com/fwlink/?LinkID=317567." HelpKeyword="BCLBUILD2001" />
  <Error Condition="Exists('..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets') And ('$(RunOctoPack)' != '' And $(RunOctoPack))" Text="OctoPack cannot be run because NuGet packages were restored prior to the build running, and the targets file was unavailable when the build started. Please build the project again to include these packages in the build. You may also need to make sure that your build server does not delete packages prior to each build. For more information, see http://go.microsoft.com/fwlink/?LinkID=317568." HelpKeyword="BCLBUILD2002" />
</Target>

NB: You will need to make sure that the relative paths to OctoPack is correct.

The downside to this obviously is that when a new version comes out I’ll have to manually update this path to point to the new version.

Now TeamCity will turn this into a NuGet package, at this point I can automate steps 1-4.

To automate steps 5 and 6 requires writing a PowerShell script which will call SqlPackage.exe. The following script will have the the required parameters to filled in during deployment by Octopus Deploy and use them to execute SqlPackage.exe:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

$dbName = $OctopusParameters['DbName']
$user = $OctopusParameters['DbUser']
$pwd = $OctopusParameters['DbPassword']
$server = $OctopusParameters['DbServerName']
 
Write-Host DbName: $dbName
Write-Host User: $user
Write-Host Server: $server
 
& 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe' /Action:Script /OutputPath:Upgrade.sql /SourceFile:National.dacpac /TargetServerName:$server /TargetDatabaseName:$dbName /p:IncludeCompositeObjects=true /TargetUser:$user /TargetPassword:$pwd

It used to take about 30 minutes on average to do all this and was a massive pain just to deploy a relatively minor change. Now it takes between 5 and 10 minutes, depending on the amount of changes being applied. We now have a very simple process to follow.

Octopus Deploy doing the deployment of the database project and generating the upgrade script

The upgrade.sql script generated by SqlPackage.exe