Re: Merge or Trans with QUS best when publishing partitions of database to slow subscribers
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Wed, 8 Nov 2006 14:20:46 -0500
Queued replication does not have the rich conflict resolution features that
merge does. Also queued does not scale well beyond 10 subscribers and is
best used when the majority of the DML occurs on the publisher.
Merge is resilient to network failures and is in fact designed with lossy
low bandwidth links in mind.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Laurence Neville" <laurenceneville@xxxxxxxxxxx> wrote in message
news:OmMzo41AHHA.1220@xxxxxxxxxxxxxxxxxxxxxxx
The background:
I am designing a replication topology with one "head office" database
publishing to 1-100 "branch" databases. Each branch database contains a
partition of the head office database, in other words just the data
belonging to that branch. To enable this I must create a
publication/publications on the head office database which filters the
data by the branch's branch code.
The filters on many of the articles will require several joins on
intermediate tables in order to ultimately join on the branch table which
contains the branch code.
The publisher will be SQL Server 2005 and all the subscribers will be SQL
Express. The subscribers are connected most of the time but there are
occaisional network outages. The subscribers are all on low powered
Windows 2000/XP workstations/laptops.
There will be a fairly constant flow of data from subscribers to publisher
(subscribers are point of sale systems that return sales to head office).
There will be only intermittent flows of data from publisher to
subscribers (new products for sale etc). There should be no conflicts
generated in this topology.
The question:
It looks like a choice between Merge publications and Transactional with
Queued Updating Subscribers. Which solution would handle this topology
best given the following concerns:
1. Ease of setting up publications. With merge I can have one publication
using a dynamic filter using the host_name function. Merge also has more
options for joining related tables. With transactional it looks like I can
join related tables in the filter, but it must be static, so I need a
publication for every branch, e.g.
WHERE branch_id in (select branch_id from branch where branch_code =
'001')
Will there be a problem with merge or transactional joining any number of
tables back to the Branch table in a filter?
2. Performance. Because the subscribers are low spec and generate a large
number of data changes to be replicated back to the publisher, we want the
replication method to impose as small an overhead as possible. We also
want as small an overhead as possible on the publisher server.
3. Network outages. Sometimes a particular branch will be disconnected,
from an hour to a few days.
.
- References:
- Merge or Trans with QUS best when publishing partitions of database to slow subscribers
- From: Laurence Neville
- Merge or Trans with QUS best when publishing partitions of database to slow subscribers
- Prev by Date: Re: ReIndexing while using replication Important
- Next by Date: Re: ReIndexing while using replication Important
- Previous by thread: Merge or Trans with QUS best when publishing partitions of database to slow subscribers
- Next by thread: Re: Merge or Trans with QUS best when publishing partitions of database to slow subscribers
- Index(es):
Relevant Pages
|
