Depending upon your requirements, you might be able to use
bi-directional transactional replication for this. Let's say you
designate Loc1 as the "master" server. Loc2 and Loc3 each subscribe to
Loc1, and Loc1 subscribes to Loc2 and Loc3 -- i.e., data replicates both
ways. However, under bi-directional replication, you enable loopback
detection, so that you don't wind up endlessly bouncing the same data
between servers.
LOC1
/ \
/ \
/ \
LOC2 LOC3
Any changes directly made to Loc1 will replicate to Loc2 and Loc3.
Any changes made to Loc2 will replicate to Loc1, and from there
replicate to Loc3 -- but not back to Loc2.
Any changes made to Loc3 will replicate to Loc1, and then from there,
replicate to Loc2 -- but not back to Loc3.
One issue that you get with bi-di replication is that simultaneous
updates to the same row on two servers can cause a data anomaly. There
are ways to deal with this, but it sounds like you may not have to worry
too much about this problem.
-----Original Message-----
From: Shrikant S. Patil
[mailto:mssqldba-ezmlmshield-x85592608.[Email address protected]
Sent: Wednesday, June 30, 2004 10:54 AM
To: LazyDBA Discussion
Subject: Replication design
Hi,
I have the following scenario, and need to configure replication for
this. Pls advice.
We have 3 SQL server databases, at 3 locations on SQL server 2000
servers. They are connected over WAN by 2 MBPS link (triangle). I have
to configure replication on this. Database has around 200 tables, and
size of database would be around 200-500 MB each. Changes would be minor
on daily basis. I have these servers at following locations. Loc1
(HeadOffice) Loc2 (branch office) Loc3 (branch office)
Data has to replicate from Loc2 and Loc3 to Loc1 at short intervals
during the day. Somewhere in midnight (off peak hours), data has to
replicate from Loc1 to Loc2 and Loc3 (for changes made at Loc1 to be
reflected in Loc2 and Loc3). These database are modified only during the
day, and number of users at each location will not exceed 30.
I have following queries
- Can I configure Transactional replication for Loc2 -> Loc1 and Loc3 ->
Loc1 to replicate at short intervals. And additionally Snapshot to
happen at midnight from Loc1 -> Loc2 and Loc3. Or do I require Merge
replication for this scenario. (or any other solution you think would
suit this requirement)
Pls suggest.
Thanx,
Shrikant Patil
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page