Automating Schema Management
Part 1 - Why compare schemas?
Change is a Way of Life
Databases tend to be forever evolving. Only very rarely are
the structures within databases "static". That is, developers (either in-house,
hired consultants or software providers) are constantly "improving" their
applications. These improvements often require changes to the structure of the
associated database. New objects are added, existing objects are removed, renamed or
modified. As a database administrator, how do you know that what is in the databases
that are "under your control"? As a developer, how can you be sure that the version of
the database that you are working with agrees [logically] with version-n of the data
model?
One solution is do not do any in-house development or integration!
You can achieve this by buying a suite of integrated packages such as
Oracle® Applications, PeopleSoft, SAP etc. Of course, your organization's pockets might
not be that deep! Additionally, using packages "straight out of the box"
is not always feasible. Packages do not always solve every problem (at
least not without significant modification) and usually integration is required
with other applications within the organization. So, for those situations where
you are required to develop, modify and integrate, what do you do? How do you
track the various versions of schemas?
In many situations, change is a way of life. Your
organization's databases are exposed to continual change.
Often, these updates come from multiple
sources. For example, external software suppliers and in-house development
teams may both develop their own applications and then provide integration components.
During the development cycle, many instances of various schemas are created, updated and
destroyed. These need to be tracked, versioned and managed. So who are you going
to call?
The DBA's Viewpoint
As a database administrator, your developers will likely ask you questions like:
- Have you built me the latest version of the schemas?
- What is the latest version anyhow?
- How does this new version differ from what I am currently working on?
- I don't want to lose the test scenarios I've set up interactively.
Can you update my test database (schemas) without destroying my data?
- Why can't I update my own schemas?
Just give me the changes and I'll do it myself!
In order to resolve these issues you have to be able to achieve the following:
- Define, document and publish schema versions.
- Define, document and publish differences between schema versions.
- Create scripts (or have the facility) to update schemas from one
version to another in a reliable manner.
- Verify that a schema is as per the specification.
That is, it has not been changed in some haphazard manner.
For example, that last "quick fix" script that the
development team released has altered the production database
so it now differs to the reference model that the data
modeling team "published".
The Developer's Viewpoint
As a developer you might be concerned with what the DBAs are doing to
"your" schemas.
Suppose that the DBA team have been busily tuning and have now created
a whole heap of additional indexes.
They tell you, these are just "physical" changes!
You guys [i.e. developers] don't need to know about these!
Just make sure your upgrade scripts never fail [and of course make sure you
never drop any of our changes [i.e. the changes that we never told you about]!
Discipline and a Little Bit of Help
In a well disciplined environment, all changes are properly documented and are
applied to all environments using appropriate change control procedures.
Yep, I'm starting to lose you!
You're muttering "It just doesn't happen like that
in our organization!".
But it can. All you need is a little discipline
[no, make that lots of discipline] and a little help.
This help is in the form of automated schema management tools.
Copyright © Impact Systems Pty. Ltd. 2008
|