Replication possible ??

Question

Replication possible ??

philcart

SSC-Forever

Points: 47794
More actions
January 13, 2004 at 11:23 pm

#83902

Just wondering if it's possible to use replication for a project I'm working on.
The source data originally comes from a Progress database that is managed by a third-party. We have no control over this database and cannot make any changes to it . We also hold a copy of the data in our own SQL Server database .
On a nightly basis we truncate all the tables in the SQL Server database and copy over a new set of data. We have to do it this way because the Progress database does not have any fields to indicate if a record has been changed. . This pcopy takes 4-5 hours every night.
Now we have another application that needs this same data in another database at a remote site. We cannot use the same "copy all the data" methodology as it would take too long .
Would it be possible to use replication to only push out changed data?
--------------------
Colt 45 - the original point and click interface

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

If you set replication in SQL, then you won't be able anymore to truncate the tables, you will have to delete them, wich means that all the deletes will replicate to the subscribers, and then all the inserts you execute after that, causing a lot of traffic.

If I where you I would improve a bit the process of copying from the Progress Db.

Try to campture only new records in SQL and also diferents records and apply those changes, and then SQL replication will do the same in the other server.

philcart SSC-Forever Points: 47794 More actions · Answer 2

We don't have any means of determining what has changed in the Progress database unless we check every field in every record. This would substantially increase the time it takes for the nightly copy and cause the transfer of data to the remote site to happen even later.

How about if we replicate to a second database on the SQL Server? Can I introduce something like a "lastUpdate" column from which we can then extract the changed records for the remote site?

--------------------
Colt 45 - the original point and click interface

jj8558 SSC Enthusiast Points: 143 More actions · Answer 3

I am presently doing this - Use a SQL staging database that gets reloaded each night. I then use DTS to load my Production SQL database with only the changes. (This is the hard part) ..you will have to write a Where not exists clause for each table to do the updates from Staging to Prod. Inserts are a little easier. I then replicate out from the Prod server to other servers.