When using Continuous or Automated Deployment, how do you deploy databases?

Question

I'm looking at implementing Team City and Octopus Deploy for CI and Deployment on demand. However, database deployment is going to be tricky as many are old .net applications with messy databases.

Redgate seems to have a nice plug-in for Team City, but the price will probably be stumbling block

What do you use? I'm happy to execute scripts, but it's the comparison aspect (i.e. what has changed) I'm struggling with.

Answer 1

We utilize a free tool called RoundhousE for handling database changes with our project, and it was rather easy to use it with Octopus Deploy.

We created a new project in our solution called DatabaseMigration, included the RoundhousE exe in the project, a folder where we keep the db change scripts for RoundhousE, and then took advantage of how Octopus can call powershell scripts before, during, and after deployment (PreDeploy.ps1, Deploy.ps1, and PostDeploy.ps1 respectively) and added a Deploy.ps1 to the project as well with the following in it:

$roundhouse_exe_path = ".\rh.exe"

$scripts_dir = ".\Databases\DatabaseName"

$roundhouse_output_dir = ".\output"

if ($OctopusParameters) {

$env = $OctopusParameters["RoundhousE.ENV"]

$db_server = $OctopusParameters["SqlServerInstance"]

$db_name = $OctopusParameters["DatabaseName"]

} else {

$env="LOCAL"

$db_server = ".\SqlExpress"

$db_name = "DatabaseName" }

&$roundhouse_exe_path -s $db_server -d $db_name -f $scripts_dir --env $env --silent -o > $roundhouse_output_dir

In there you can see where we check for any octopus variables (parameters) that are passed in when Octopus runs the deploy script, otherwise we have some default values we use, and then we simply call the RoundhousE executable.

Then you just need to have that project as part of what gets packaged for Octopus, and then add a step in Octopus to deploy that package and it will execute that as part of each deployment.

Answer 2

We've looked at the RedGate solution and pretty much reached the same conclusion you have, unfortunately it's the cost that is putting us off that route.

The only things I can think of are to generate version controlled DB migration scripts based upon your existing database, and then execute these as part of your build process. If you're looking at .NET projects in future (that don't use a CMS), could potentially consider using entity framework code first migrations.