Propagating Changes to Live Databases with SSW
Data Renovator
Keeping your live production database up to date
with the latest structural changes is an ongoing
problem for developers. The database needs to be
taken offline quickly to make structural changes
and convert data but this can be tricky when
people are relying on the system. The data
conversion can also take time as new fields and
relationships might have been added.
Different Methods of Updating a Database
The following examples assume that the database
is in Microsoft Access format and has the data
in a separate database to the forms, reports and
code. The client or live production database is
named DBDATAv1.mdb and the development team has
made changes to the structure of this database,
which is now called DBDATAv2.mdb.
|
No
|
Method
|
Develop On
|
Additional Development
|
Downtime for Client
|
Comments
|
| 1. |
Database Offline(Cannot be used during
development)
|
Master copy of Application and Data
(DBDATAv
|
None. |
As long as development takes (days to
weeks)
|
Can work for small, quick changes |
| 2. |
Database Online Manual Updates |
Master copy of Application Copy of
production database
|
None. Keep a record of structural changes
|
As long as it takes to make changes
manually (a few hours)
|
Works for small changes. Chance that some
changes will be missed on larger jobs
|
| 3. |
Database Online Write Queries to move data
from development to production
|
Master copy of Application Copy of
production database
|
Writing and testing update Queries at the
end of development
|
As long as it takes to run the queries and
convert data (a few hours)
|
Allocate lots of time at the end of
development to write and test the update
queries.
|
| 4. |
Client Online Write DAO Code to change
structure of production database
|
Master copy of Application Copy of
production database
|
Code and interface to store and make the
changes to live data
|
As long as it takes to run the code (a few
minutes)
|
Good if the database has a large amount of
data and few structural changes
|
Issues of Converting Live Data
Assuming you have ruled out the first option
Database Offline for anything other than small
changes, the following issues need to be
considered:
Tracking Structural Changes
As development is being done on a copy of the
live database that will be synchronized with the
live database at the end of development, all
changes to the schema must be tracked. Tracking
changes is especially important when using
methods 2, 3 and 4.
|
SSW Data Renovator compares two databases
and reports any differences in the
structure.
|
Making Structural Changes
If the old database will have its structure
changed instead of transferring all data into
the new database, structural changes need to be
made either manually or through code. Methods 2
and 4 work this way.
|
Another SSW Product SSW Data PRO! can be
used to automatically make structural
changes to databases on-site
|
Removing all test data from the new structure
Using Methods 2 and 3, the developer makes a
copy of the master data database that has the
new structure, clears all test data out of this
database then transfers the data from the live
database into the development database. Clearing
test data can be a painstaking task if the
database is large and referential integrity is
enforced, as the data must be deleted from
tables in the correct order from the child
tables upwards.
|
SSW Data Renovator can remove all data
from a database in the correct order
according to the relationships defined in
the database.
|
Converting the Data from the old data structure
to the new
Once development has finished, either the
production database must have its structure
changed to match the changes made in development
or the data must be transferred to an empty copy
of the development database. This can be
complicated when new fields have been added
which cannot be empty, new relationships have
been defined and field properties have been
changed.
|
SSW Data Renovator reports differences
between tables and fields as well as
indexes, relationships or properties.
|
Making structural changes to the database works
when the database is very large and only a small
number of changes have been made, alternatively
the data is transferred from the old version to
the new. This ensures that none of the changes
made in development are lost and the data is
valid.
|
SSW Data Renovator performs data transfer
from one database to another without any
changes to the old database. Tables and
Fields can even be renamed and transferred
without renaming the fields in the old
database.
|
Checking all data has been transferred correctly
After the data has been transferred, checks are
needed to ensure that all the records have been
transferred correctly. Usually this consists of
counting the records in the tables before and
after the transfer.
|
SSW Data Renovator automatically displays
a list of tables and the number of records
before and after the transfer.
|
The history behind SSW Data Renovator why it was
developed
At SSW the common scenario when the client
requested changes to an existing application was
that:
-
While the changes are made the client
continued to use the database system in their
offices
-
The developers worked on the Application
Database and made structural changes to a copy
of the Data Database.
-
Changes to the Data Database were tracked and
either Update Queries or DAO code was written
to make the update.
After development was finished, a visit was made
to the clients offices and the database was taken
offline while changes were made manually to the
Data database. This was time consuming and
expensive for the client, there had to be a better
way
Using SSW Data Renovator
So we developed SSW Data Renovator. This allows
us to do the development in a similar way but
when we come to update the data at the clients
offices we use SSW Data Renovator. We compare
the version that the Client has been using
against the version that has just come out of
development. The changes are checked and
confirmed and all reports are printed. The data
is then transferred into a clean copy of the new
database structure and the number of records
transferred is checked from within the SSW Data
Renovator interface. The downtime of the clients
database system is shortened and the possibility
of mistakes is reduced.
|
Note: If the client is not accessible we
use Data PRO! Which uses a different
approach in that it records changes to the
data database and replicates at the client
site i.e. perform changes to database
structure remotely without user
intervention.
|
More Information
SSW Data Renovator Home Page
http://www.ssw.com.au/DataRenovator/
SSW Data PRO! Home Page
http://www.ssw.com.au/DataPRO/
SSW Home Page
/ssw/
Email SSW
Ross Donald
|