Home
>
Archive
>
SSW Standards
>
Developer Access
>
SSW Why 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.
The Common Scenario
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
SSW 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.
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.
Acknowledgements
Ross Donald