I’m researching continuous deployment of databases. It appears a significant problem area, and something that's much less established than continuous deployment of code.

If you're presently doing continuous delivery / deployment of databases, or attempted to get it done, I’d be very interested to listen to of the encounters.

I begin to see the following possible trouble spots:

  • Version control.
  • Continuous integration.
  • Unit testing.
  • Deployment.
  • Staged roll-out / roll-back.

Do you know the primary problems you experienced setting this up / attempting to set this up?

What are the tools / frameworks that will help?

Thanks,

Justin

I have automated lots of our database (MSSQL) into our automation setup. I've not solved all the problems, but I will tell you things i have for every of the problem points, and when you comment and request more I'll attempt to help. Clearly how lots of the work will have the way your product is to establish and that which you use for other continuous integration solutions, so that your mileage can vary.

  • Version control

    We use Subversion with this. We must teams of scripts. The very first is the install script - this could produce a new database on your own for any new deployment. The second reason is some upgrade scripts that upgrade any arbitrary version as much as the most recent version. They are damaged into some scripts for tables, procs, and data, but they're always run together. Each update statement is couched inside a conditional, to ensure that they aren't run unless of course they should be. We have a small table within the database for script pieces that require to operate only one time and therefore are difficult to identify if they have been run. i.e. if "MajorUsernameValidation" is incorporated in the table, we do not run that part.

  • Continuous integration

    You will find three stages to the continuous integration. One is a straightforward validation database the upgrade scripts are run against on every checkin. This database sits dormant by any product, it's just used to make sure that the SQL applies and it has no major runtime issues. The second reason is that after our development/deployment develops happen (a minimum of two times each day) the database is upgraded with them. By doing this the tests running from the new build will also be from the new database, and also the integration testing accomplished for this certifies the database. Finally, at specific key events the install script operates against a clean database and also the schema is in comparison for an upgrade database to make certain they're synchronized.

  • Unit testing

    Unit exams are run from the upgraded database in the earlier step. There's additionally a devoted testing database that's stored current to ensure that specific database tests could be run against it outdoors from the deployment setup.

  • Deployment

    Based on if it's a brand new install or perhaps an upgrade, we offer a load file that runs the correct scripts within the proper order. These launched scripts will always be scripts that have undergone the continuous integration and unit testing steps.

  • Staged roll-out / roll-back

    This really is always the tricky one. Frequently, we simply create a backup of every database prior to it being upgrade and restore this database if your failure happens. Ideally, you'd possess a roll-back script for every change. Used, we've found that it's very tough to create a script that comes arbitrary changes backward. This is actually the one are where I believe our process may be the poorest.

The approach that I have seen my clients possess some success with would be to essentially believe that you will find some challenges with db deployments.

  1. They're order dependent

  2. They're non-repeatable

  3. There's no miracle (however like Red-colored Gate try)

  4. Different conditions are used to at different paces

  5. Rollback is really a discomfort

Which results in an answer that needs:

A) An purchased listing of changes (addressing #1)

B) Checking the database to determine what changes happen to be applied and using only those that haven't (#2 and #4)

C) An identical rollback script for every change for #5 or acknowledging defeat and try to continuing to move forward, or moving back through rebuilding a back-up.

We make use of this general approach within the upgrade scripts in our items and can include some fundamental pedaling around that within UrbanDeploy but given #3 (there's no miracle) there's still a large amount of "process" needed -- someone (most likely designers) must make that purchased list and each change must happen this way for repeatability.