In IBM UrbanCode Deploy, a deployment is meant to handle the entire application. To provide rapid feedback and drive consistency in releases, deploying the whole application using the same techniques used in production is important. Often the full application includes databases whose schemas are being updated. Database deployments are notoriously tricky. The good news is that while there is no “silver bullet” fix, UrbanCode Deploy has some key capabilities and integrations that support database deployments for teams at different levels of maturity.
What makes Database Deployments Hard?
Database deployments are particularly tricky for a couple of reasons. The first set of reasons are technical. Database changes are:
- Incremental: Usually, destroying a database and recreating it from scratch is not an option – at least in production. The business critical data inside would be lost. Instead we apply a series of updates to the database.
- Ordered: The incremental changes to a database are ordered. If change number ten adds a table and change twenty updates that table, it is very important to run ten before twenty.
- Non-repeatable: Adding a record twice results in two records. Adding a table twice will generally result in an error.
- Non-reversible: Once a change that destroys data has occurred, the only hope of rolling it back is to restore from backup.
These characteristics are quite different from a typical Java or .Net web application where a deployment moves a bundle of artifacts to some servers and installs them. The whole bundle is typically deployed and rolling back is as simple as redeploying the old versions.
The next set of challenges for database deployments is social. These deployments are often executed by DBAs who are somewhat separate from the core development team. Often the development teams doesn’t fully understand how changes are made to the databases and the DBA team doesn’t understand why the databases are being changed. That gap can lead to miscommunication, mistakes and general misery.
Automating with UrbanCode Deploy
There are several approaches to automating database deployments that are well supported by UrbanCode Deploy. Some teams will start by keeping the process manual, but tracking it within the tool. More will take their existing (mostly SQL) update scripts and manage their incremental and ordered deployments. For many teams, the long term goal is to try to approximate “full” deployments by using a specialized database deployment tool integrated with UrbanCode Deploy.
This approach should only be used in the short term, but can be a nice way to bring a manual process into the orchestration fold. The strategy is straight forward. Create a component as normal. For the deployment process include just a single step: a manual task instructing users in the “DBA” role for the environment to get to work. When they mark the step as complete, the rest of the automation for the application can continue.
Running SQL Scripts
If the old way to deploy was for developers to craft sql scripts which they delivered as incremental updates to the DBAs to run, it’s natural to ask if UrbanCode Deploy can deploy the same scripts. It absolutely can. The low-level automation is pretty easy. You simple move the scripts to a deployment machine (often not the database server itself) and apply them either using a JDBC adaptor or by calling the command line tools for your database. Plugins are available for Hadoop, Microsoft SQL Server and Oracle. If you wanted to create a custom plugin along these lines, look at this example for Postgres.
But the basic automation doesn’t handle the incremental, ordered, reversible challenges. Here’s how to do that:
- Incremental: Most of the versions of a component that are collections of scripts should be flagged as Incremental in UrbanCode Deploy.
- Ordered: The inventory will track the order of incremental versions entering an environment, and a Snapshot based on the environment will automatically capture the order.
- Non-repeatable: UrbanCode Deploy’s default behavior is not to deploy what is already there. So if a Snapshot contains database updates one through twenty and is applied to an environment that already has changes one through fifteen, only changes sixteen to twenty will be applied.
- Non-reversible: While there is no magic, some teams create a “rollback” folder in each incremental version to contain reversing scripts. If so, a standard rollback type in UrbanCode Deploy is “Remove undesired incremental versions”. A basic automation process would be setup to run the rollback scripts rather than the going forward scripts. In the Application Process, you would call that rollback process with the rollback type of “Remove Undesired Incremental Versions.” With these options, the rollback process for each version deployed to the environment but not part of the desired Snapshot would be called in the right order (reverse of deployment order).
The result here is pretty nice. The deviation from how teams have been working is relatively small. Someone creates a database script (either a developer or DBA) for each change, and that is treated as an incremental version. Those can enter a lower test environment one by one and get tested. When a bunch of changes work alongside code changes, the collection can be placed in a snapshot and moved to the next environment. Whatever is already there doesn’t get reapplied and whatever is new does – in the same order tested in the lower environment.
There are some challenges though. Any ordering of changes within a version will need to be managed by a naming convention or a wrapper script inside the version. With frequently changing databases, the number of incremental versions being tracked against various environments could grow long, leading to a large number of mini-deployments when spinning up a new test environment. So while a solid approach, there is room for improvement by moving away from working with bags of SQL scripts.
Moving away from SQL Scripting
Many teams want to get more declarative and be able to generate a single “build” that represents a version of the schema which a deployment can bring an actual database into compliance with. This is something the UrbanCode team encountered a when it first started building enterprise products. Clients wouldn’t take every upgrade, so each release of the product had to include both the ability to create a new database quickly and the ability to upgrade from any prior version. We built an XML based toolkit for expressing the ordered changes to the database, and if you look inside one of our installers you will still find those XML files.
Since then, this approach has spread and a number of open source and commercial tools have emerged to help version database changes and apply them correctly. Some expect developers to design the changes in within the tool, others employ database comparison techniques to generate suggested changes. In the open-source community LiquidBase and DB Deploy are among the best known. Datical, who has released a Datical DB plugin for UrbanCode Deploy, has extended LiquidBase as the foundation of their commercial offering. DBmaestro also introduced plugin for their commercial offering. They emphasize version controlling database changes.
These approaches are very database centric and while different tools have different advantages, getting away from carefully managing the incremental updates through scripts can be transformational. That said, the process and tooling changes tend to be more significant as the entire way changes are crafted shifts.
About the Data…
Most of the discussion around testing and releasing database change is centered on schema change. Getting test data into test lab, can be a challenge. To help with that, there’s also an integration for InfoSphere Optim Test Data Management. So when the application changes, UrbanCode Deploy can push new versions of the application code in, update the database, refresh the data and then kick off automated testing tools to drive rapid feedback.
We’re adding integrations all the time. For the current list of database oriented integrations, see https://developer.ibm.com/urbancode/plugins/ibm-urbancode-deploy/#filter=Database