⚠️ This page has been archived

✅ New page with updated info: ssw.com.au

Home > Archive > SSW Standards > Developer Access > SSW Why Data Renovator

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.

The Common Scenario

At SSW the common scenario when the client requested changes to an existing application was that:

  1. While the changes are made the client continued to use the database system in their offices.
  2. The developers worked on the Application Database and made structural changes to a copy of the Data Database.
  3. 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:

Acknowledgements

Ross Donald