Automated deployments, Team City, Octopus Deploy and SQL database changes

Hello everyone,

I am currently managing a development teams automated deployment of a .net web application with a sql server backend.

We are SVN as our source control

We currently have fully automated for building and deployment of the web portion but are struggling with automating the sql piece.

Our developers are not comfortable with creating SQL Scripts for all their changes as they are used to using Visual Studio to manage the database changes (using a DB project).

Is there a way to easily do the following.

  1. Have Team City detect changes in the DB project and rebuild the db project.
  2. Have Team City and Octopus deploy then deploy the delta change automatically to environments set up in Octopus Deploy?

Thanks for

Hi Corey,

Thanks for getting in touch! There is no easy way to do what you have described.
The easiest way would be to use a tool such as SQL Compare from Red Gate.

Otherwise you might be able to build a hook in Team City and write some magic scripting to create a file.
But as far as we know both Team City and Octopus have nothing built in that will help here, unless you add a third party such as SQL Compare.

Vanessa

Corey, perhaps ReadyRoll SQL could help?? It comes with native Octopus integration.

http://ready-roll.com/

http://doc.ready-roll.com/display/RRSQLDOC/Octopus+Deploy/

Hi Corey,

There are now Octopus step templates available for ReadyRoll, Redgate SQL Release and FlyWay. All are great tools for source controlling and rolling out database changes.

ReadyRoll: http://documentation.red-gate.com/display/RR1/Tutorial%3A+Octopus+Deploy+Step+Template
Migrations driven, plugs into VS.

Redgate SQL Release: https://documentation.red-gate.com/display/SR1/Deploying+a+database+package+using+Octopus+Deploy+step+templates
Model driven, plugs into SSMS with Redgate SQL Source Control

Flyway: http://workingwithdevs.com/flyway-migrate-step-template-octopus-deploy/
Open source, migration driven

Open disclosure: I work for Redgate. Redgate has acquired ReadyRoll so now it can offer both a model driven and a migrations driven approach. I wrote the Flyway step template.

Alex Yates
@AlexYates