Automating Schema Management

Part 2 - Why automate schema management?

Tedious and Error Prone

Resolving the above issues without automation is tedious and error prone. Many sites utilize CASE tools to define, document and compare (difference) schemas. However, quite often there is little or no support for updating existing schemas. It is [usually] a trivial exercise to build [one or more] new schemas from the latest DDL generated by your CASE tool. However, when your existing schemas contains data that needs to be preserved then simply dropping the old schema objects and re-creating the new schemas is not always desirable. For example, the database might be very large and thus the time to export and re-import all data might be prohibitive. Alternatively, as is often the case, the old table definitions might be incompatible with the new structures. If you do not have automated upgrade tools, schema updates are tedious and error prone.

Outside of CASE

Within CASE tools, the comparison exercise tends to be confined to comparing the schemas defined within the tool. This does not preclude reverse engineering existing schemas into the CASE tool but this is often labor intensive and time consuming. Often the CASE tools provide only some of the required automation. What if the application's schemas are not under CASE control? What about those environments where the database administrators (DBAs) and developers add their own objects? What about development teams who wish to test their latest ideas? That is they wish to fast-track outside of the blessing of the corporate data model.

Tools Provide Automation

An automated approach to comparing and updating schemas implies using a tool to compare schema in the working database against some reference schema. It also implies that the tool will not only report the differences but will provide a means to update the working schemas (or database) such that its structure matches the structure defined in the reference schemas (or database). Structure does not (usually) include contents (i.e. data rows) but may optionally include physical characteristics such as storage, object placement etc.

Alternatives to Automation

So what alternatives are there to using an automated upgrade tool?  For small development databases you can create a set of scripts to load all data into the database. Then, whenever a significant schema change is made you simply drop the old schemas, use the DDL to create the new schema structures and then run your scripts to re-populate the database. For simple changes you can manually determine the differences and create DDL scripts to make these changes. What about those extra test cases that you just entered? Shame you didn't take the time to add it to your test data scripts! While on that point, who is going to maintain the test data scripts? They need updating each time the schema structures are changed.

Thus, in some circumstances the manual approach is appropriate. Often however, you will want to preserve your data. In many cases the scripts to load the data are not kept up to date. That is, they worked perfectly with the version of the schemas that was 6 releases ago, but now they won't run as you haven't had the time to upgrade the scripts. Nobody wants extra work when the deadlines are tight. Anyhow, most of your development team don't really care much about those latest changes published by the data modeling group.

Beware those Dependencies

Well what if you only want to change a type, let us say to change the length of one of its data members. This shouldn't affect much after all it is only used by two tables and all you want to do is change the CITY member from VARCHAR2(20) to VARCHAR2(40) and you know that the actual data stored is not going to change. Simple enough, we'll just issue a CREATE OR REPLACE TYPE ... Well no. First you have to drop any other types that use this type. Secondly, you are going to have to drop the two tables that use it as well. Shame about that! Seems things are not always as simple as they should be! Maybe future releases of Oracle® will be kinder!

Summary

In summary, the reason for using automated tools to upgrade your development databases is to update them in the fastest, most reliable and least disruptive manner. If the changes are simply modifying some referential integrity constraints and some other minor changes then you would not want to drop your schemas (or database), would you? Well I wouldn't! Furthermore, there is rarely any choice but to upgrade production databases. Well, maybe if it is a small data warehouse that gets reloaded every night, but, in most cases... In most cases, we must preserve the existing data! End of story! Period!

So it seems there is a need to automate!

Copyright © Impact Systems Pty. Ltd. 2008