Traditional GUI-based tools are great for looking up information in a database or making a quick change to a single instance, but their utility often diminishes when you’re working on a prototype system.

For example, take the common scenario of a commercial application that requires a dedicated database. A prototype of the database is designed and created using the author’s favorite tool (SQL Server Management Studio, Oracle Designer, TOAD, etc.), and then exported into script form using that tool’s built-in utility. The resulting script is then used to generate copies of the database for the customers.

The install process is rarely a smooth one, however. First of all, all the elements of the database mirror those of the development environment - users, passwords, and so forth. Those need to be changed, probably through some manual changes to the generated script.

What about updates? Any changes to the database in new software versions create a dilemma: should the changes be deployed as a patch or as a whole database drop-in replacement? The previously described process of modeling the system in an editor and then exporting a script file isn’t granular enough to support most patching operations, but replacing an entire database creates its own problems - most notably the scale of the deployment procedure and the customer’s (usually reasonable) expectation that their data will persist through upgrades.

Anyone who’s had to modify automatically generated code before probably sees the problem here - the job just got a lot more complex. Since no humans were involved in the creation of the script files, there’s no institutional knowledge about their structure, so every change must be carefully researched in order to ensure it does what it’s supposed to and doesn’t break anything else in the process.

Then there’s the question of rollbacks. What if something goes wrong? Can the system be restored to its previous state? Not many database tools have an export feature for rollback scripts.

The problem is, these tools simply weren’t designed to handle scenarios like this.

On the other hand, engineering a custom solution can result in more robust systems in less overall time. Here’s a technique I’ve used several times to simplify database development and management.

At a granular level, any change to database structure; creating or dropping objects, changing permissions, modifying data; can be encapsulated in a SQL (or PL/SQL, or Transact SQL, etc) script. Where database visualization software frequently gloms all these SQL statements together into large “install” scripts, I propose treating them discretely. The important distinction is that for each action, there is an associated “undo” action that returns the database to its prior state:

Action: create table
Undo: drop table

Action: grant permission
Undo: revoke permission

Action: drop table
Undo: create table and restore all data

Any install, upgrade, or rollback activity can be defined as a sequence of these types of actions. Scripting these operations as part of any release can go a long way toward streamline testing and deployment of your changes.

(more…)

  (1 comment)