Multiple distribution agents running on subscriber

I have 1 production db and 1 subscriber db using transaction replication on
SQL Server 2000 standard edition. They are over 100 gb in size. Essentially
the subscriber is a hot backup for the the production server. The db was
restored manually at the subscriber because it is to large for snapshot.
After reading Hilary's book, scanning the internet and posting to this
group, I made an exucutive decision to split up the articles into smaller
subscriptions: 6 to be exact, using pull subscriptions to minimize problems
on the production server. This allowed me to better control/assess problems,
latency, etc.
Everything is going alot better (if you've read any of my previous posts)
and I have most of the problems under control and latency is way down.
(Thank Hilary and Paul!) I do have one BIG question though, since there
doesn't seem to be a lot of documentation on this type of set up (multiple
subscriptions and publications with a 1to1 database.)
Okay here is the question. My distribution agents that are running on the
subscriber will "stop" running once they have "succeeded" or no replicated
transactions are available for that subscription. What is the best way to
get notified once these have stopped and setup a schedule for restart when
new transactions are available? If there is no way of knowing when new
ones are available, can I set a job to automatically start the agents when
for peak times that transactions will be available for the particular
subscription? Right now I am doing it manually which isn't very condusive to
sleep.
For example I know my subscription called "Main Bulk" will need to be
running most often during working hours while my subscription called "Price
History" will need to be running mostly overnight. Hope I've explained this
clear enough.
Thanks!!!!
Kristy
I think your best bet would be to query
distribution.dbo.msdistribution_status and then start up the distribution
agent after a certain number of undelivered commands are pooled for a single
agent in the distribution database.
ie
select * from distribution.dbo.msdistribution_status where agent_id=7 and
UndelivCmdsInDistDB > 2000
if @.@.rowcount > 1000
exec sp_start_job @.job_id=0x8458390850805824052
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:egY1BEoUFHA.3840@.tk2msftngp13.phx.gbl...
> I have 1 production db and 1 subscriber db using transaction replication
on
> SQL Server 2000 standard edition. They are over 100 gb in size.
Essentially
> the subscriber is a hot backup for the the production server. The db was
> restored manually at the subscriber because it is to large for snapshot.
> After reading Hilary's book, scanning the internet and posting to this
> group, I made an exucutive decision to split up the articles into smaller
> subscriptions: 6 to be exact, using pull subscriptions to minimize
problems
> on the production server. This allowed me to better control/assess
problems,
> latency, etc.
> Everything is going alot better (if you've read any of my previous posts)
> and I have most of the problems under control and latency is way down.
> (Thank Hilary and Paul!) I do have one BIG question though, since there
> doesn't seem to be a lot of documentation on this type of set up (multiple
> subscriptions and publications with a 1to1 database.)
> Okay here is the question. My distribution agents that are running on the
> subscriber will "stop" running once they have "succeeded" or no replicated
> transactions are available for that subscription. What is the best way to
> get notified once these have stopped and setup a schedule for restart when
> new transactions are available? If there is no way of knowing when new
> ones are available, can I set a job to automatically start the agents when
> for peak times that transactions will be available for the particular
> subscription? Right now I am doing it manually which isn't very condusive
to
> sleep.
> For example I know my subscription called "Main Bulk" will need to be
> running most often during working hours while my subscription called
"Price
> History" will need to be running mostly overnight. Hope I've explained
this
> clear enough.
> Thanks!!!!
> Kristy
>
>
sql