3-way replication?

Hello,
I currently have a database set up for replication on 3 servers as
follows:
Pub - users connect and update database
Sub1 - database is accessed for local application
Sub2 - database is accessed for local application
The subscriptions are set up for both immediate and queued updating, so,
in theory, if Pub goes down, the users can re-connect to Sub1 to update
the database, and when Pub comes back, the queued updating will apply
the changes to Pub. Also, during the time Pub is down, the local
application on Sub1 will have access to current data.
However, as I understand it, Sub2 will not have access to the latest
data while Pub is down, since the only way those changes make it to Sub2
is through Pub. So, Sub2 will be accessing "old" data as long as Pub is
unable to send the changes.
Do I have this right ? Is there a simple way to get Sub1 to update Sub2
when Pub is down ? As it is now configured, it's kind of a pain in the
neck to manage the replication when we have to make a change to the
database definition, and I also want people to be able to update at Sub2
and have the changes propagate to Sub1 and Pub, in the event that
becomes necessary. But I don't want to create a structure that is a
nightmare to manage.
Any suggestions ?
Thanks,
Patrick
No, there is no simple way to do this.
Replication requires a publisher which figures out what goes where.
What you could do is have Pub publish to Sub1, and then have Sub1 publish to
Sub2. I would use merge replication for this.
Why are you using immediate updating?
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
"Patrick Johnson" <PJohnson_TechnoScope@.nospam.com> wrote in message
news:PJohnson_TechnoScope-09A593.15383717022005@.msnews.microsoft.com...
> Hello,
> I currently have a database set up for replication on 3 servers as
> follows:
> Pub - users connect and update database
> Sub1 - database is accessed for local application
> Sub2 - database is accessed for local application
> The subscriptions are set up for both immediate and queued updating, so,
> in theory, if Pub goes down, the users can re-connect to Sub1 to update
> the database, and when Pub comes back, the queued updating will apply
> the changes to Pub. Also, during the time Pub is down, the local
> application on Sub1 will have access to current data.
> However, as I understand it, Sub2 will not have access to the latest
> data while Pub is down, since the only way those changes make it to Sub2
> is through Pub. So, Sub2 will be accessing "old" data as long as Pub is
> unable to send the changes.
> Do I have this right ? Is there a simple way to get Sub1 to update Sub2
> when Pub is down ? As it is now configured, it's kind of a pain in the
> neck to manage the replication when we have to make a change to the
> database definition, and I also want people to be able to update at Sub2
> and have the changes propagate to Sub1 and Pub, in the event that
> becomes necessary. But I don't want to create a structure that is a
> nightmare to manage.
> Any suggestions ?
> Thanks,
> Patrick
|||In article <uHp4mjTFFHA.1264@.TK2MSFTNGP12.phx.gbl>,
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:

> No, there is no simple way to do this.
> Replication requires a publisher which figures out what goes where.
> What you could do is have Pub publish to Sub1, and then have Sub1 publish to
> Sub2. I would use merge replication for this.
> Why are you using immediate updating?
Hi Hilary,
Thanks for your reply.
I have all of the users updating at Pub right now, so conceivably I
could get away without immediate updating, but the intent is that local
updates can be made at Sub1 and Sub2 and propagated immediately through
the system.
In practice it is a rare event for an update to happen someplace other
than at Pub.
Is immediate updating a bad idea ?
It's been a while since I set it up, but I remember there was some
reason I didn't want to use merge. Of course, now I can't remember what
it was.
Thanks,
Patrick