Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2009-05-28 : 16:36:58
|
| I have 2 SQL 2000 instances on separate servers. One is planned as a reporting database containing some (not all) tables in the other. They are big tables. We want them synchronized daily, but using DTS to move the entire tables nightly would cause too much network traffic. Some sort of delta transfer is desired. What would be the easiest way to accomplish this? |
|
|
mualsh
Starting Member
8 Posts |
Posted - 2009-05-28 : 17:34:15
|
| I would recommend considering transactional replication. Depending upon the data volume and transaction size that you want replicated, it may make sense to consider setting up the replication in continuous mode (as opposed to a nightly update). This would give you the benefit of making your reporting data available (on the reporting server) almost instantaneously (as opposed to the next day availability). |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-28 : 17:54:56
|
Another approach is to add a column (named LastEdited or similar) in the source table and have a trigger update the value every time an insert or update is done against the record.Then only transfer the records from yesterday which now is easy by selecting from the table with comparing the LastEdited column with today's date.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
Synchronize Reporting Tables Nightly