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).
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:
- Build the SQL Server Database Project in Visual Studio
- Find the output directory of the SQL project
- Connect to the remote server
- Copy the DacPac file to the remote server
- Look up syntax for SqlPackage.exe
- Generate update script (using SqlPackage.exe)
- Look over script in management studio
- 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.
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:
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