Automating database deployment using a CMS

Question

My team are developing Umbraco websites and looking to improve the automation of our database deployment. Currently we have automated builds and deployments of our source code using Kiln and Team City, but cannot work out how to handle the database deployment.

The challenge is applying changes made in the development environment, while keeping new database content that has been added in the live environment via the CMS. Currently our changes are deployed using courier manually, but we aim to convert this into a '1 click deployment' which is what we desire for running our automated tests and ease of deployment. How can we automate this without wiping new content added in the CMS?

Answer 1

In general what you are looking to do is called database migrations. The simplest possible solution is to just have a series of SQL DDL scripts that are run in order to create and modify the schema to get to a certain point. Because it is SQL you could also use it to insert structural data if required by the CMS.

I'm not familiar with umbraco personally, but I've used a number of other CMS products and most of them have some sort of migration system in place that satisfies your requirements. I would first look to leverage that system -- it probably has all you'd need and will mesh well with the database changes required by CMS upgrades or plugins.

If you need to roll your own on top of or in parallel to that system, I would probably use RoundhouseE as it should be able to work astride whatever umbraco is doing. It can be run from a command line and we certainly use it very effectively every day from TeamCity up to and including live deployments on running web applications.

Answer 2

After further investigation, this is possible to do using Courier. Courier contains 'revisions' which can be checked into source control and deployed. They can be configured to move either only the data itself (from live back to dev) or to move only the structure (from dev to live).

The only limitation to this is Courier does not support Umbraco Members.