Database Release Automation

Implementing a fully automated, continuous application delivery process requires that ALL configuration items, including those that are stored in a database, be version controlled and deployed as part of the full cycle.

Unfortunately, a recent survey showed that many companies which implement continuous delivery for their application code do not do the same for their database code.

Here are some of the database configuration items that Clarive is capable of managing:

1) DDL, schema

2) DB Scripts, Stored-Procedures, Functions and Triggers, such as PL/SQL or Mongo Javascript

3) Application Configuration stored in the Database

Data?

Why is Clarive concerned about managing data? Well, the answer is rather simple: a great number of applications, be it in-house or closed ERP products, store their configuration in the database. Configuration that affects how the application logic and interface behaves. Usually this means inserting or updating a few rows of data from one environment to the next.

Whenever code changes are promoted and deployed to the next environment in the pipeline, their corresponding configuration data needs to be deployed along it. Failing to do so in a coordinated way may result in having incompatible application code with configuration data in an environment.

How does Clarive deploy to a database?

Clarive comes with several mechanisms and processes to manage and deploy changes from and to a database.

It does that by:

1) connecting directly to the database via its own set of drivers;

2) connecting via a database command interface (ie. sqlplus, mysqladmin…) either locally from the Clarive server or remotely, connected to a server with an agent.

How to Rollback Changes?

A fully automated continuous delivery process is not complete unless it can be demoted, rolled-back or backed-out at any point during or after deployment.

Here are some of the mechanisms available in Clarive for managing the backout of database changes:

DB transaction enclosing

Clarive allows you to open a transaction in the beginning of a deployment, and close it at the end. If something fails in between, the database rollbacks naturally as a failed transaction.

Oracle Flashback

Oracle allows for flashback of tables to a previous state, which make for real safe rollback of data. It needs to be turned on by administrators. Then Clarive will invoke the flashback mechanism for a safe rollback.

Forward and Rollback patches

Clarive allows you to define forward-rollback db patches (migration scripts) that go in pairs. If something fails in forward (deploy), Clarive runs the rollback db patch script, which is carefully restoring the db to a safe state. Clarive may enforce Developers to checkin the pair into the repository (or as an attachment to the Changeset), so that rollback has at least been taken care of.

More Features

There are many more possible facets to take into consideration before archieving a full, automated delivery process.

Here are some additional operations available in Clarive for handling database deploys:

Oracle Change Tracking Provider

It’s a mechanism by which Clarive generates patches automatically by tracking user changes to a database.

User reviews which changes she may want to add as revisions to Changesets.

Oracle dependency detection

Clarive detects in which order Oracle scripts need to be executed for a correct deployment.

For example: one script may create a table, the next may set trigger of some sort over that table. Clarive makes sure they’re executed in a given order, determined automatically or manually.

SQL Validation Operation

As part of a rule, and previous to actually deploying changes to an environment, Clarive checks that every and each one of the DB patches are actually syntactically correct and detects possible errors, like missing tables and columns.

Depending on the database, Clarive can check against the target enviroment to make sure that script will work there before deploying.

Parallel Deployments

Sometimes we need to be able to deploy changes to many Databases at once. Clarive is able to fork many simultaneous connections to different databases to deploy to them.

What’s next?

If you’re in the process of thinking of how to implement a database release automation process for your company, here are some things you need to take into consideration:

How do you know which applications are using the database to store configuration? Code parsing and dependency detection may be of use here. Make sure you parse your code with a tool that can identify database dependencies or at least, changes to database dependencies from the current baseline.

How do you version database changes? When making changes directly to the database, how do you know what has changed and where? Once you have your changes versioned, make sure you can identify and group those changes as you tie them into version control, and that the version control tool can be easily updated.

How do you deploy database changes? Think about what strategy your company uses for forward and rollback database deployments. Some of them can be as simple as creating forward and rollback patches. Or you can just backup certain tables in the process, or use certain flashbacks mechanisms available from specific database vendors to quickly restore the database to a previous state. Don’t forget to establish an ordered approach to deploying, as some schema changes need to be run in an orderly fashion to avoid having invalid objects, such as triggers, in the database.