Peer to peer replication on SQL 2000?

Hello,
reading about peer-to-peer transactional replication in SQL 2005
(http://msdn2.microsoft.com/ms152536.aspx), I would like to do the same
thing with 3 SQL 2000 servers. Of course peer-to-peer replication is not
available, but can anybody suggest a way to simulate this kind of
replication with SQL 2000?
thanks,
al.
AL,
the nearest thing is probably bi-directional transactional replication:
http://support.microsoft.com/default...b;en-us;820675
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Bi directional transactional replication only works between 2 nodes. The
closest you can get is merge, but this would require your publisher to be
always accessible. If it goes down, the other two nodes are out of luck.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23AE7C%23REGHA.3984@.TK2MSFTNGP14.phx.gbl...
> AL,
> the nearest thing is probably bi-directional transactional replication:
> http://support.microsoft.com/default...b;en-us;820675
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Maybe I'm missing something it but according to the MS document, you can use
a central subscriber topology to use BiDirectional Transactional replication
to cater for more than two nodes?
Cheers,
Paul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23P67ZKSEGHA.828@.TK2MSFTNGP10.phx.gbl...
> Bi directional transactional replication only works between 2 nodes. The
> closest you can get is merge, but this would require your publisher to be
> always accessible. If it goes down, the other two nodes are out of luck.
> --
> 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
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23AE7C%23REGHA.3984@.TK2MSFTNGP14.phx.gbl...
>
|||Paul Ibison wrote:
> Maybe I'm missing something it but according to the MS document, you can use
> a central subscriber topology to use BiDirectional Transactional replication
> to cater for more than two nodes?
> Cheers,
> Paul
That's what I'm trying to do. Manually, as stated in that article. Still
working on it ;)
|||Well what do you know!
Last time I tried this I could not get it to work. Changes originating on
Server B would go to Server A and C but not back to B. Then them C would
loop back to A and go to B, and collision. Let me try to repro this again.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OlJqkTSEGHA.1384@.TK2MSFTNGP11.phx.gbl...
> Maybe I'm missing something it but according to the MS document, you can
> use a central subscriber topology to use BiDirectional Transactional
> replication to cater for more than two nodes?
> Cheers,
> Paul
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23P67ZKSEGHA.828@.TK2MSFTNGP10.phx.gbl...
>
|||Hilary,
I just managed to let this work, but it's a mess of things to do
manually (including generating - and then modifying!! - stored
procedures that will handle inserts/updates/deletes on other servers).
If you follow the instructions, and start with a simple table, you get
it working. The problem is then using this for a large db...
I'm still evaluating if it's worth the effort, but this is the only
solution I found (thanks Paul) to simulate what under SQL 2005 is called
"peer-to-peer transactional replication".
If you or anybody else is using this kind of replication, I'd like to
hear your opinions.
bye,
al.

> Well what do you know!
> Last time I tried this I could not get it to work. Changes originating on
> Server B would go to Server A and C but not back to B. Then them C would
> loop back to A and go to B, and collision. Let me try to repro this again.
>
|||It absolutely can not be done. You wind up with endlessly looping
transactions.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"AL" <albeNOSPAM@.ihnetNOSPAM.it> wrote in message
news:uAJszyREGHA.376@.TK2MSFTNGP12.phx.gbl...
> Hello,
> reading about peer-to-peer transactional replication in SQL 2005
> (http://msdn2.microsoft.com/ms152536.aspx), I would like to do the same
> thing with 3 SQL 2000 servers. Of course peer-to-peer replication is not
> available, but can anybody suggest a way to simulate this kind of
> replication with SQL 2000?
> thanks,
> al.
|||Mike,
I like your explanation of avoiding closing the loop, but as there is only a
requirement for 3 servers (and the poster wants the nearest method in SQL
2000 to simulate peer-to-peer), why won't
ServerA <--> ServerB <--> ServerC
work? (you mention this in another reply ).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Al,
it would help us to know if it is a requirement for all the nodes to
communicate with eachother (rather than having a simple chain). As you can
see from the thread, this is the crux of whether or not bidirectional TR is
applicable or not.
Rgds,
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql