September 20, 2006 at 6:34 am
Hi all,
I have encountered some problems in scheduling the replication agents but before explaining that let me give a small background of the current DB landscape.
It is implemented as Star topology or re-publisher topology with the publisher and the subscriber configured on SQL Server 2000 and it consists of ShareDB on SQL Server 2005 which acts as the re-publisher of data. All other databases publish articles to ShareDB which in turn re-publishes to the databases which subscribes for the article. The topology is using a remote distributor on SQL Server 2005 to offload the replication agent jobs processing from the OLTP environment. The distributor job agent is running continuously and it is shared for a specific database in case of Snapshot replication. And all the Transactional publications will have a separate distributor agent. At present the replications agents are scheduled on both the publisher and ShareDB.
Now, I am trying to enforce one way scheduling on the publisher side which will start the snapshot agent for the corresponding publication on ShareDB after the distributor agent transfers the data from the snapshot folder to the subscribers. This approach is effective and more reliable in the sense that data synchronization will happen immediately after the distributor agent transfers the data to ShareDB.
The following are the issues related to scheduling the replication agent on the publisher only.
1. I have included sp_start_job stored procedure after the run agent step in the shared distributor job agent steps to start the snapshot agent of the corresponding publication on ShareDB after the distributor agent transfers the data to ShareDB. Since the distributor agent is running continuously in our scenario, the next step is not getting executed and so the job agent of the corresponding publication on ShareDB is not started.
2. It is also necessary to specify either the job name or the job id in sp_start_job stored procedure to start the job agent. With the replication setup configured in the aforementioned fashion, how is it possible to invoke the snapshot job agent of the corresponding publication on the second link (from ShareDB) dynamically after the distributor agent on the first link is completed?
Thanks a lot,
Barry
September 20, 2006 at 9:25 am
Barry,
Are you saying that you have transactional replication from the publishers to ShareDB, and then snapshot replication from ShareDB to the subscribers?
It sounds like you want to trigger a snapshot from ShareDB to the subscribers any time that a change is replicated from the publisher to ShareDB. Is this right, or am I misunderstanding?
September 22, 2006 at 3:13 am
Ronzo,
Thanks a lot for taking time to read and reply to my post.
I think you have misunderstood the concept. Actually the setup is something like this. Both snapshot and transactional publications are created on the publisher and the same publications are also created on ShareDB to replicate the articles to the respective subscribers.
And I am trying to schedule the snapshot publications only on the publisher side and based on the distributor agent transferring the data from the snapshot folder for a particular publication, the corresponding snapshot agent for that publication on shareDB should be started.
Barry
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply