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,
the easiest scenario would be mege replication. Snapshot will copy over the
whole tables and unless the tables are small or all rows are always updated,
this is not recommended, and you'd have to set up replication each time
after the snapshot - possible but not so nice. Also, if the data is
partitioned then great, but if not then merge will take care of that with
the conflict resolvers. The only thing is that you won't be able to easily
prevent the download of data from Loc1 until the evening, as these changes
will propagate during the frequent synchronizations. If this is not at all
desirable, than you might have to consider a more radical solution - you
could have Loc1 as the publisher but use unidirectional merge with the
subscribers and reinitialize each evening.
HTH,
Paul Ibison
|||Hi Paul,
Thanx for your reply.
But would not be using the bi-directional transactional
replication solve this ?
Loc1 acts as Publisher with Loc2 and Loc3 as subscribers.
And then Loc2 and Loc3 as Publishers, and Loc1 as
Subscriber ?
regds,
Shrikant

>--Original Message--
>Shrikant,
>the easiest scenario would be mege replication. Snapshot
will copy over the
>whole tables and unless the tables are small or all rows
are always updated,
>this is not recommended, and you'd have to set up
replication each time
>after the snapshot - possible but not so nice. Also, if
the data is
>partitioned then great, but if not then merge will take
care of that with
>the conflict resolvers. The only thing is that you won't
be able to easily
>prevent the download of data from Loc1 until the
evening, as these changes
>will propagate during the frequent synchronizations. If
this is not at all
>desirable, than you might have to consider a more
radical solution - you
>could have Loc1 as the publisher but use unidirectional
merge with the
>subscribers and reinitialize each evening.
>HTH,
>Paul Ibison
>
>.
>
|||Shrikant,
this would be an option. If you expect conflicts then it can become terribly complicated, and the initial setup is not straightforward, but you're right - it is in some ways a simpler solution. To You might want to have a look at the most recent issue of
SQL Server magazine if you're thinking of going down this route - there is a nice article on setting it up.
Regards,
Paul Ibison