transactional publication on another server. Replication between these
two servers goes down frequently. I've been told the job that runs at
the subscriber needs to determine if replication is up and current at
the time the job runs, and if replication is NOT up and current, the
job needs to exit and run at a later time. The purpose of this
instruction seems to be so that personnel monitoring replication, do
not have to monitor and/or re-run processing on the subscriber.
It's been suggested that checking the MSreplication_subscription.time
value in the subscriber database is a solution. I see from BOL that
column is defined as the last time the Distribution agent updates the
subscriber. What's its usefulness as an indicator that replication is
working?
TIA
You could set up the inbuilt alerts to notify you if an agent has stopped
abrubtly and then take action (automatically) as a result. To see the
current state of the job, you can use this script:
http://www.replicationanswers.com/Do...unningJobs.txt
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul, I'll check out the script. But it's a litlte more
complicated than simply stopping the regularly-scheduled job.
Basically, they want any and all regularly-scheduled processing at the
subscriber, to be completely self-repairing when it comes to
replication being up, down, or in the progress of catching up. The
personnel that monitor this repository are somoewhat restricted in how
they can use the inbuilt SQL Server alerts, because of security
concerns. They use that and the fact that replication is undependable,
to justify this requirement that jobs be replication-proof.
as a side question, am I way off base in my opinion that production
DBA's should moniitor jobs on production boxes, as a matter of course?
thanks again...
|||OK - if the replication setup should be self-repairing then I must ask -
what errors are causing it to go down? If it is an unreliable connection
then the agents can be set in a loop to restart automatically, but if it is
anything more difficult then the source should be registered and in most
cases needs to be dealt with. Replication alerts will allow your DBAs to
know via email when something has caused the agents to stop, and the
alternative is to effectively poll the system - either manually by going in
to replication monitor and looking for the red icons or or in code, but for
my money this is reinventing the wheel as the alerts are there for this
purpose. I definitely agree that monitoring of jobs is part of the
Production DBAs work - that's what the Notifications tab is for on the Jobs
form - and most DBAs Demand this info rather than have to be persuaded that
it's worthwhile
.Rgds
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think their replication IS set up to be self-repairing. The problem
is they want JOBS to be self-repairing, when it comes to replication
being down.
This job creates a dataset for use by a downstream process (that occurs
hours later). If replication is down when this job runs, the dataset
*may* be missing data, with no way for the processes that use it to
tell. My solution to that issue was for monitoring personnel to have
the knowledge that if replication is down, this job has to be manually
re-run. They want that situation to be automated in some fashion.
They have suggested that the job run in a continuous loop until it can
determine that replication is up-to-date. Thus, the suggestion to use
MSreplication_subscriptions.
I agree with you that the real issue is why replication is so
undependable. At this point I feel like saying, what is the point of
having a subscriber repository if processing that runs there always has
to check whether data is current?
thanks for letting me vent :-)
sql
No comments:
Post a Comment