|
Home > Archive > MS SQL Server Replication > December 2005 > Notifcation of when data replicates - Transactional replication
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Notifcation of when data replicates - Transactional replication
|
|
| mje11 2005-12-21, 11:23 am |
| Hi there
Setup: SQL 2000, Transaction replication, continuous distribution agent
I have a single one-way publisher-subscriber setup. Numerous update
jobs to several tables happen at the publisher, kicked off from DOS
batch files. Batch file export jobs run against the subscriber. The
export jobs at the subscriber should only run once the data has
replicated from the publisher, so I need a way of knowing when the data
has arrived.
The only method I can think of involves:
1. The update job updates table A at publisher, then inserts a row to a
log table, table B. Both tables are published.
2. Data replicates to subscriber: updates to table A arrive, then the
insert to table B fires a trigger (to create a file) to notify the
export jobs that they can start.
This logic assumes that data is replicated in the order in which the
changes are made at the publisher. Is this correct?
Also, am I right in thinking that if table A and table B were in
separate publications, with independent distribution Agents this would
not work?
Finally, does anyone know of a better way of accomplishing what I need?
Many thanks for any help,
Matthew
| |
| Paul Ibison 2005-12-21, 11:23 am |
| Matthew,
the order of changes is indeed maintained in transactional replication,
provided the articles are in the same publication. If they were in separate
publications with independant agents, the order would depend on the order of
running the agents.
The trigger method could be used to flag the data arriving, and notification
Services or some sort of polling mechanism can be used to check for the
flag's existence. You could also check the distribution history tables to
determine when data was last sent over, but in your case it is the final
flag that seems to really indicate the next process can begin on the
subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
| |
|
| Thanks for getting back to me, Paul.
Just to confirm, if I split the tables over separate publications and
used a single distribution agent, this would not maintain the order of
changes?
Cheers,
Matthew
| |
|
|
|
|
|