Crossed transactional replication

Say we have office1 and office2, each has a copy of the database. We
want changes on one to be reflected ASAP on the other. And we have
the luxury of knowing that about 99.44% of the time, the two offices
work on separate accounts, so collisions, while not impossible, are
going to be possible on only a few tables and will happen very
infrequently, and say further that their resolution will be easy.
What I'd LIKE to do is set up unidirectional transactional replication
from #1 to to #2, and separately from #2 to #1. Will this even begin
to work, or will SQLServer forbid a publisher from also being a
subscriber to the same table? Yes, I could try it. No, I haven't
noticed it in BOL.
Now, it seems this would produce a looping situation, where an update
on #1 will cause an update on #2, which will then cause a redundant
update on #1, etc. UNLESS the replagent on the subscriber was smart
enough to turn on the "not for replication" flag automagically before
doing its thing. Is that logically correct? Is the capability
available? And if so, would it still be smart enough to detect
collisions if they occurred?
I think we have a pretty common scenario so maybe someone (or many
someones) is already doing it, but I am totally unclear from BOL as to
whether it's possible to solve directly with current tools. I can see
some home-grown ways to work around things, but they are necessarily
more complex than if the tool can do the job in the first place.
Thanks.
Josh
See "immediate updating option, overview" in BOL.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:i9c5f11pn844ka549jb4hcgduuoae6m77t@.4ax.com...
> Say we have office1 and office2, each has a copy of the database. We
> want changes on one to be reflected ASAP on the other. And we have
> the luxury of knowing that about 99.44% of the time, the two offices
> work on separate accounts, so collisions, while not impossible, are
> going to be possible on only a few tables and will happen very
> infrequently, and say further that their resolution will be easy.
> What I'd LIKE to do is set up unidirectional transactional replication
> from #1 to to #2, and separately from #2 to #1. Will this even begin
> to work, or will SQLServer forbid a publisher from also being a
> subscriber to the same table? Yes, I could try it. No, I haven't
> noticed it in BOL.
> Now, it seems this would produce a looping situation, where an update
> on #1 will cause an update on #2, which will then cause a redundant
> update on #1, etc. UNLESS the replagent on the subscriber was smart
> enough to turn on the "not for replication" flag automagically before
> doing its thing. Is that logically correct? Is the capability
> available? And if so, would it still be smart enough to detect
> collisions if they occurred?
> I think we have a pretty common scenario so maybe someone (or many
> someones) is already doing it, but I am totally unclear from BOL as to
> whether it's possible to solve directly with current tools. I can see
> some home-grown ways to work around things, but they are necessarily
> more complex than if the tool can do the job in the first place.
> Thanks.
> Josh
>
|||On Thu, 4 Aug 2005 20:57:17 -0700, "ChrisR" <noemail@.bla.com> wrote:
>See "immediate updating option, overview" in BOL.
Yes, I have, but it seems to be an alternative to crossed replication,
not really a satisfaction of it. It depends on using DTC to go behind
the replication and have the "subscriber" work directly against the
publisher, rather than cross replications. Anyway, it also uses GUIDs
and triggers, which are two more things we'd like to avoid.
Does anybody actually do it that way?
I mean, look, what if the two offices worked on COMPLETELY distinct
subsets of data, so IF we could have crossed replication it would
never have collisions, but the replication system would still have to
know the ranges on either side. We could probably implement this with
custom SPs or DTS filters, IF the crossed replication were legal in
the first place. Perhaps it is, that's what I'm asking, until I have
the time to simply try it - but I pretty much expect something in the
system will bark at me and it won't work. Again, I think this is a
very common requirement, and it would be nice to have it work out of
the box. Yukon?
Josh
|||I think most people just use immediate updating. Probably gonna have to test
your idea to see if it works.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:0ai7f1pqhmulj1ln5v7qhumkgan7ftt3ds@.4ax.com...
> On Thu, 4 Aug 2005 20:57:17 -0700, "ChrisR" <noemail@.bla.com> wrote:
> Yes, I have, but it seems to be an alternative to crossed replication,
> not really a satisfaction of it. It depends on using DTC to go behind
> the replication and have the "subscriber" work directly against the
> publisher, rather than cross replications. Anyway, it also uses GUIDs
> and triggers, which are two more things we'd like to avoid.
> Does anybody actually do it that way?
> I mean, look, what if the two offices worked on COMPLETELY distinct
> subsets of data, so IF we could have crossed replication it would
> never have collisions, but the replication system would still have to
> know the ranges on either side. We could probably implement this with
> custom SPs or DTS filters, IF the crossed replication were legal in
> the first place. Perhaps it is, that's what I'm asking, until I have
> the time to simply try it - but I pretty much expect something in the
> system will bark at me and it won't work. Again, I think this is a
> very common requirement, and it would be nice to have it work out of
> the box. Yukon?
> Josh
>
|||On Tue, 9 Aug 2005 13:23:24 -0700, "ChrisR" <noemail@.bla.com> wrote:
>I think most people just use immediate updating. Probably gonna have to test
>your idea to see if it works.
Guy at work claims he already has it going, he just had to customize
the SPs to prevent loops. When and if I actually get a look, I'll let
you know. FWIW he still had the distributors on the publishers.
J.