publishing a subscription ( 3 servers )

I have a publisher that publishes a subset of master data ( transactional ).
The subscriber then publishes the same tables to dumb, live servers.
Transactional again. The master server ( the first one in the chain) is the
distributor. Replication is initiated via the Replication ActiveX objects
from a .NET application.
Once I have applied the snapshots in order and configured the servers,
replication breaks after about a day with errors about not being able to
drop tables on the intermediate server because they are participating in
replication. No surprise about the error, except since I am using
transactional replication everywhere, why is it trying to drop the table?
it is a push-only publication, I do not need merge replication, conflict
resolution, or updating from the subscriber. How can I prevent my error.
Surely what I want to do can't be so unusual?
Think Content Management Systems.
Admin database -> publishes to - > staging database -> publishes to -> live
database.
This should be straight-forward, no?
thanks for any help guys.
Leon
Leon,
your @.pre_creation_cmd is drop. If you create the first publication and
subscription then set it going (initialize it), then create the second one -
the republisher, it should be ok. You will have problems if you need to
reinitialize the first publisher/subscriber pair and will receive the
message you saw, so in this case you'll first need to drop the second
publication, and readd it later.
HTH,
Paul Ibison
|||( whoops - must remember to Reply Group!! )
thanks Paul. I know this happens when I run the initial snapshot, and I
create the publications very carefully as a result ( I ran a generate sql on
the pubs to save time )
The first time I set this up, everything was fine, except I needed to drop
and recreate the second publication on account of a change in configuration.
then the problems started. It all works fine for about half a day, then I
get the error - so I wonder if I haven't properly removed the publications
that first time... I'm going to clear everything, diable replication and run
from a clean slate here, and keep in mind what you're saying. Lucky that I
can eh!
Are there any other gotchas I need to be aware of when re-publishing a
subscribed table?
Thanks again
Leon
-- Original Message --
From: "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
Newsgroups: microsoft.public.sqlserver.replication
Sent: Monday, May 17, 2004 11:00 AM
Subject: Re: publishing a subscription ( 3 servers )

> Leon,
> your @.pre_creation_cmd is drop. If you create the first publication and
> subscription then set it going (initialize it), then create the second
one -
> the republisher, it should be ok. You will have problems if you need to
> reinitialize the first publisher/subscriber pair and will receive the
> message you saw, so in this case you'll first need to drop the second
> publication, and readd it later.
> HTH,
> Paul Ibison
>
|||I think you need to
1) script out the schema you want replicated on your publisher, run it on the first subscriber/publisher and the second subscriber
2) when you create your publication on both your publisher and subscriber/publisher in the article properties selection delete all the data in the existing table in the name conflicts section.
3) create your publication and subscription from your publisher to your publisher subscriber.
4) generate your snapshot and replicate it from your publisher to your publisher/susbcriber.
5) when 4 is complete, repeat steps 3 and 4 for your publisher/subscriber to your subscriber.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html