Replication Question

[lee_shelton]

I couldn't find the 'search' button so I'm not sure if this is the right forum...

We want to start replication. We have 500 offices across the country with separate Progress 8.3 (SCO Unix) databases. So we want to 'replicate' those db's by combining them into one large db at the central office.

The path chosen is to install db triggers and have the trigger programs append the updated buffer info (creates/upodates/deletes) to a text file. One text-file per each of the 500 offices.

Then send that text-file over a "dial-up" connection (using maybe FTP) to the central office.

At that point they want to load the text-file into a SQL-Server db, but what has me puzzled is back at the start:

Has anyone used just a "text file" for their replication log trail?

I'm afraid of OS file collisions when multiple users start firing those triggers -- all going to one text file. (Although someone mentioned to me SCO's file-locking functions scripts or C-functions -- I've not looked into it, yet)

Shouldn't we start like the sample code in /usr/dlc with another database and table with the RAW data field -- and then _under Progress control_ export the latest transactions to a text-file??

I don't know how to articulate my concerns and the other team members seem "bent" on NOT distributing another 500 db's (however small).

Thanks
Lee

 

[cmorgan]

Replication

First, let me suggest that you look into upgrading to version 9. V9 has some nice replications features built in.

If you are stuck in v8 then read on.

I've been down this road in v6 and v7, and let me tell you - it was no fun. What we did was add a new table to the database to track each change to each record in each table we cared about. The new table had fields for the name of the table being changed, a unique sequence number, a change type (Add/Delete/Change) and and 2 text fields. Whenever a record was changed we would create a new entry in the change log describing the change. Each field in the record would be concatenated into the first text field before the change, and into the second text field after the change. Sort of a record by record before and after image.

Then, when we were ready to update the central database, each satellite db would dump it's change log, and ftp the file to the central db.

A program on the central db would read the files, and call a different update program for each record read, based on the table being updated. You need to write a custom import/update program for each table you care about - I would suggest writing a simple code generator to write the programs for you.

The same process can be run from the central db to the satellites to update them with changes made elsewhere.


If you would like more details, feel free to contact me off line and I'll try to help you any way I can.

 

[rrojo7229]

I couldn't find the 'search' button so I'm not sure if this is the right forum...

We want to start replication. We have 500 offices across the country with separate Progress 8.3 (SCO Unix) databases. So we want to 'replicate' those db's by combining them into one large db at the central office.

The path chosen is to install db triggers and have the trigger programs append the updated buffer info (creates/upodates/deletes) to a text file. One text-file per each of the 500 offices.

Then send that text-file over a "dial-up" connection (using maybe FTP) to the central office.

At that point they want to load the text-file into a SQL-Server db, but what has me puzzled is back at the start:

Has anyone used just a "text file" for their replication log trail?

I'm afraid of OS file collisions when multiple users start firing those triggers -- all going to one text file. (Although someone mentioned to me SCO's file-locking functions scripts or C-functions -- I've not looked into it, yet)

Shouldn't we start like the sample code in /usr/dlc with another database and table with the RAW data field -- and then _under Progress control_ export the latest transactions to a text-file??

I don't know how to articulate my concerns and the other team members seem "bent" on NOT distributing another 500 db's (however small).

Thanks
Lee

Hi Lee,

I have a similar situation. Did you implement the Replication Trigger after make the upgrade to Progress V9?

Thanks.

 

[tamhas]

If you are using V8 and thinking in terms of flat files and dialup, I am probably wasting my breath, but ... this is an absolutely ancient architecture. Why aren't you thinking in terms of integration over an ESB?

 

[joey.jeremiah]

Hello all

In regards to log based replication it is definitely doable.

I wrote a simple one way replication engine for a selected number of tables a short while ago for a client. AFAIK it's been running ever since.

From what I remember there were no performance issues, although, there were no high volume of transactions to begin with.


I created a database to store notes similar to a BI file, the notes are of course grouped by transactions.

Notes are rolled over to the target database asynchronously by a batch process in a set interval.

I can also think of rolling the changes to another database type, like, Oracle, SQL Server, even MySQL.

I'll be abit busy in the next couple of weeks but if you or anyone else is interested or would like to contract out the job feel free to contact me privately.

 

[timk519]

Been there, done that - except the replication system I created was peer-to-peer.

1) 8.3 / SCO is seriously outdated. Consider moving to 9.1E if at all possible, it is a lot faster than 8.3, and makes work like this possible.

2) Do not, under any circumstances, use text files as your log trails. Why? Because they have no knowledge of transaction scope and rollback. You must do a transaction log to a replication table, so if a TX is backed out, the log is too.

3) Others have suggested various forms of mimicing BI logging and the like. I did something like that as well, although my tx logs had to cover multi-db transactions as well.

The other option is to track key-field IDs to the tx target table, and then use them to dump the target tables in order to get your change log. This doesn't result in a field-level change log, so you'll get more information than was actually changed, but it'll be easier to implement.

4) in a 9.1* environment, this isn't all that hard for someone who'se done it already. If you need help and can afford a good contractor, I can help out as well.