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:
- 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 </Project> tag.
<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:
$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.
Disorderly Development 
That was really helpful, thank you.
You’re welcome, glad it was helpful. Are you actively using Octopus Deploy for production systems?
[…] changes was too hard to automate, so we stuck with a manual process. Over time we’ve figured out how to automate right up unto the point of applying the […]
[…] about various aspects of the DrDoctor automated build process (you can read a few posts here, here and here). In the last week we’ve made a few more improvements that are worth a mention. […]
Thanks for sharing, setting this up now. As for manual update of octopack, if you have more than one project in your solution and add nuget package to non DB project, then your relative path would point to a latest package, updateable from another project (non-DB).
Ken,
Thanks for the post discussing your techniques to publish Dacpacs using Octopus. I’m also in need of a similar solution. One thing I’m curious about was if you created a .nuspec file for your DB Project. If so, do you mind providing some details on your file mappings?
Hi, we use TeamCity to generate NuGet packages using the Octopus Deploy plugin. That takes care of everything so there is no need for a nuspec file, so long as your project includes the Octopack NuGet package. Hope that makes sense…?
Thanks for the super fast response. Yup, that makes sense. Octopack has been installed so hopefully I’m good to go. Thanks again!
No problem, let me know how you get on and if you need anymore help just ask.
Ok, hit another snag… what all did you have to install on the Build Server to get the project targets to map correctly. The “SQL Server Database Project” references this Import:
Did you have to install VS and SQL Server on the build server to get this to work?
Thanks again for your help.
Looks like word press ate the XML Import part of my post. Here is the Project attribute path:
$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets
You will (unfortunately) have to install SQL Server Data Tools onto your build server, and then check that the paths all tie up.
In TeamCity, I use the Visual Studio runner type to build my solution and in the config specify /p:VisualStudioVersion=11.0 so that the MSBuild paths tie up to the Microsoft.Data.Tools.Schema.SqlTasks.targets file. Hope that makes sense?!
That’s what we ended up doing was installing SSDT. I ended up manually altering the config to use 11.0 instead of the default 10.0. Working like a champ now. Thanks again for the pointers. Keep up the great work!
[…] a previous blog post I showed how you can fiddle with the dtproj and add a reference to the Octopack NuGet package to […]
HI, My name i Mathis and I working on deploying a database in a CI environment.
The 1st issue is building my DB. I create a SQL project in VS 2015, build when I build unfortunately my project relies on other db references in the same db server.
So I said no problem right. So I added a db reference , but then the new db reference need to reference the original db. Which I cannot reference back because I would get a circular error. I would first need some advise on how to fix this , so I did some research at the blog below http://blogs.solidq.com/en/sqlserver/ssdt-how-to-solve-the-circular-references-issue/… I not a db guru and not sure if this would fix my issue.
After that my plan is to use Team city and octo deploy to drop newly added sql scripts to the QA server and have the TC trigger the automation on the product with the selenium agent. If you have a chance I would like to discuss certian scenarios of you are free to help . thanks
Mathis
Mathisyoung@yahoo.com
After generating the upgrade.sql, how do you then call that script from the octopus so it actually executes in the DB?
Hi, I think we used to run the upgrade.sql script manually. But you could automate this using SqlPackage