Data capture in sql

[louis]

Hi guys,

Need best way for following seniaro:

Need to replicate data in sql server 2005 db (25 tables) (max record volume in a table is 1 million), job needs to run daily and need to load data only if they have changed/updated or inserted.

server A Server B
Table A Table A
first day rec count 100 load all 100
next day rec count 120 load only 20

Please give your valuable suggestions.

Thanks

[kris007]

Couple of Options:

1. You can use a LookUp Stage and use the data in the target table as a reference to find the updates and inserts.
2. You can use Change Capture Stage to compare your source data between two consecutive runs and identify the inserts and updates.

[FranklinE]

For a low-volume database, we "cheat" a bit. Using the first load as the starting point for my description, the load file for the first day is the "new" file, and there is no "old" file involved. We preserve the "new" file and rename it to "old" for the next day's processing, and the Change Data Capture works very well for it.

For larger volumes, a better approach would be with the help of DBAs. I don't know SQL Server very well, but I do know that DB2 and Oracle can maintain change logs (in DB2, we use the BMC utilities). It is not DataStage, and I don't know it well enough to provide details, but in the end it is as simple as taking the change log from the "live" database and applying it to the other database. I've not heard of anything that's faster or more reliable.

[ray.wurlod]

Why not use SQL Server replication? IBM's CDC product (formerly Data Mirror) would also be a solution if your budget could run to it.

[louis]

Hi guys,

We have come up to add some audit cols in target & source tables one of them is the update date (Timestamp) which makes easy/simple, we will only pool and load date above that update date in target tables.

Thanks for your valuable approaches, makes me proud to be @ dsxchange.com.

Thanks,
Louis

[kumar_s]

Topic resolved? Do you like to mark it resolved?