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
|