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
Data capture in sql
Moderators: chulett, rschirm, roy
Data capture in sql
Every Thing Is Possible!!
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.
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.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Database deltas
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Data capture in sql
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
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
Every Thing Is Possible!!