Source control, build and deploy of SQL Server databases using GitHub, TeamCity and Octopus Deploy

I’ve only really used Microsoft products for source control, build and deployment: SourceSafe in my younger days and more recently TFS.  I’ve been using SSDT for a while now with the database projects checked into TFS.  Since we went back to using sprints we’ve been using the TFS work items to track epics, stories and tasks and organise these into a backlog where we pull from for each sprint.  I really like the fact that TFS does everything and that you can check your changes in under one or more work items so it’s easy to tell what changes were made for what story.  I have never been a big fan of the build definitions in TFS and don’t like updating the XAML files.  However, building and deploying SSDT projects is very simple using MSBuild (see a previous post for how to do this).  When you build an SSDT project you end up with a DACPAC and you can publish this using MSBuild.exe passing in a publish.xml file or using SQLPackage.exe passing in target server and database information.  However, a few weeks ago we were told we were moving away from TFS and would be using “better” tools, namely GitHub for source control, TeamCity for build and Octopus for deploy.  The developers in the company were keen to move to GitHub and myself and the rest of the database team are always willing to try new things and improve our processes.

Moving to GitHub was relatively straight forward.  We’re still using SSDT and at the end of the day source control just holds a bunch of files so GitHub was fine for this.  It took a little while to get used to the slightly different way git and GitHub work and at first I was using SourceTree and still use this from time to time but Visual Studio can also connect directly to a git repository and I find it much easier checking in and syncing my local git repository with the remote one through Visual Studio.  I’m not going to go into any more details about GitHub as if you decide to use it there is plenty of online help.

As I have mentioned the TFS build definitions were the things I liked least about TFS so I was looking forward to having a play with TeamCity. I like the product and have written a separate post on how to install it and set up a build for an SSDT project and publish to a particular environment.

We were recommended to use Octopus Deploy. There is a TeamCity plugin to allow you to run Octopack which is a package you can added to Visual Studio projects which when run will create a nuget package that can be passed into Octopus Deploy to deploy to whatever environments you need. This ensures that the same build artefacts get deployed across all your environments and this can be completely automated: you can check-in to your chosen source control which will trigger a build that is then deployed to a CI server and bunch of automated tests can be run. If the tests pass it can then be deployed to a staging environment and more automated tests can be run. If these test also pass we can then deploy to live and the whole process can be completely automated and triggered just from checking in some code. I know you can do all this with TFS but in theory this sounded great and I quite liked the Octopus application from the online demo I watched. However, the problem is that the Octopack package cannot be added to SSDT projects.  We were told maybe we should use something other than SSDT.  SSDT uses a “model-driven” approach meaning that you get your SSDT project into the state you want your data model in and then use an application like MSBuild to make a target database the same as the SSDT model.  Octopus recommend that you use a “change-script-driven” approach meaning that you create an initial data model and release this.  Any changes you make to the initial model and made by writing change scripts, e.g. ALTER TABLE, ALTER PROC etc.  Octopus only easily supports “change-script-driven” applications like ReadyRoll and DbUp.  I had a quick look at ReadyRoll and after using SSDT using a “change-script-driven” tool instead of a “model-driven” tool seems to be absolutely ridiculous.  Why would you make development an absolute nightmare just to fit into the build and deploy tool you want to use.  So we are sticking with SSDT and if you’re using SQL Server there is no better tool in my opinion.

Now we reached a bit of a stalemate: we were told we had to use Octopus and we were not moving away from SSDT.  After a bit of digging around we managed to find a way of tricking the SSDT project to use Octopack and to create a nuget package containing the compiled DACPAC and a powershell deploy script that uses the Data-Tier Application Framework to publish the DACPAC against each environment in Octopus.  It took a long time to work out how to do it but it’s not too complicated.  I’ve written another post that describes in detail how to do this.

In summary

So what’s better for SQL Server source control, build and deploy: TFS or GitHub, TeamCity and Octopus Deploy? For a start I’d always say use SSDT rather than any other tool.  If licence costs are not an issue and you want the ability to check code in under work-items then I’d use TFS for source control.  The main benefit of TFS is it does everything including the story board if you’re working in sprints.  We’re currently using Trello as our sprint board.  I’ve been using Trello for my personal to-do list for quite some time but I prefer the TFS sprint board and being able to check in under a particular work item.  There may be story boards out there that link to GitHub and if so this is an option.  I prefer TeamCity to the TFS build definitions and quite like Octopus.  However, having to manually create a nuspec file and Powershell deployment script for something you can do in one short MSBuild or SQLPackage command seems a bit extra work.  If I was starting a new project and had to choose one setup I’d probably go with TFS for work management and source control and use TeamCity for both build and deploy without using Octopus.  It was a close call and I can get by using any of them.  At the end of the day they are all tools that work (albeit after some initial pain), none of them perfect but all are manageable.

Related Posts:
Using TeamCity to Build and Deploy SSDT Projects
Using Octopus Deploy to Publish SSDT Projects Built with TeamCity


5 thoughts on “Source control, build and deploy of SQL Server databases using GitHub, TeamCity and Octopus Deploy

  1. Hi, you mention at the end of the article that you’ve written another post to describe the details of how to use OctoPack and Octopus with SSDT. Has that post been published yet? Can you provide a link to it? We are looking to do the same thing and any insight, and specific details, would be very helpful. Thanks!

    • Hello Scott. Thanks for the comment. I was a bit premature with the comment about the post about OctoPack. I’ve been writing that today but it’s not quite ready. I’ll try and put something out tomorrow even it’s a draft version as what I’ve got so far should be useful. I’ve managed to get it working but it’s a bit of a hack. I add the dacpac to a nuget package along with a powershell script to do the actual deployment. This gets built by TeamCity and the nuget package is passed to Octopus for deployment where the powershell script is run to publish the dacpac. It’s not exactly how Octopus should be used but it does work perfectly well.

  2. Pingback: Using Octopus Deploy to Publish SSDT Projects Built with TeamCity | Simon Learning SQL Server

Leave a Reply

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

You are commenting using your 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