We want to publish a row filtered table where each subscriber can have
one or more of the row partitions. We'd prefer not to have separate
databases or tables for each partition at the subscriber. We've look
at various scenarios, transactional repl with DTS filter, merge
replication with dynamic join filters. Can't figure out clearly if
this would work.
Is it possible to have multiple subscriptions in to the same target
table on the subscriber, assuming the same table structure?
Seems like this could work with merge replication provided the initial
snapshot is loaded without deleting existing table structure or data
since the updates are based on rowguid which would keep things
separate.
I am a little confused about what you are trying to accomplish. You can
publish a table multiple times in the same publication or in different
publications.
If you publish a table multiple times in the same publication the
destination object name must be different. Does this answer your question?
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
<vbhackattack@.hotmail.com> wrote in message
news:1172026819.916313.105530@.k78g2000cwa.googlegr oups.com...
> We want to publish a row filtered table where each subscriber can have
> one or more of the row partitions. We'd prefer not to have separate
> databases or tables for each partition at the subscriber. We've look
> at various scenarios, transactional repl with DTS filter, merge
> replication with dynamic join filters. Can't figure out clearly if
> this would work.
> Is it possible to have multiple subscriptions in to the same target
> table on the subscriber, assuming the same table structure?
> Seems like this could work with merge replication provided the initial
> snapshot is loaded without deleting existing table structure or data
> since the updates are based on rowguid which would keep things
> separate.
>
|||On Feb 20, 7:00 pm, vbhackatt...@.hotmail.com wrote:
> We want to publish a row filtered table where each subscriber can have
> one or more of the row partitions.
> Seems like this could work with merge replication
Yes this can be done with merge replication. You need to enable the
"multiple subscribers can have data" option -- I forget what it is
called exactly -- when you create the publication.
And also you need to have filtering logic that would result in
overlapped data such as "where SalesPersonId=X or RegionId=Y"; each
SalesPerson gets their individual data and everybody that asks for
RegionId Y gets gets data for RegionId=Y.
|||I will try to restate my scenario to clarify: I would like for a
single subscriber (a site) to get 2 or more horizontal partitions of a
publication (2 or more offices' data) in to the same local subscriber
table, and be able to update them back to the publisher. I say "in to
the same local table" so the local windows application reads/writes a
single table and knows nothing about the replication configuration. I
do not want multiple tables or multiple databases one for each
horizontal partition. Reading the docs I figure replication could
possibly handle this:
- snapshots are partitioned so they can be loaded more than one in the
same table, use option not to drop existing schema
- updates with a single partition will work because insert/update/
delete only touches rows within the partition subscription
Might have to config manually since replication wizard might create
the agent jobs with overlapping names
One problem I see is if an update causes a row to disqualify for one
partition and requalify for the other partition, this would translate
in to a delete from one and insert in the other so the data gets
dropped at the subscriber and then re-downloaded. Not a big deal with
just a single table but eventually I'm extending this to multiple
tables some with lots of data in each partition.
Does that make my scenario clearer?
On Feb 21, 8:58 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> I am a little confused about what you are trying to accomplish. You can
> publish a table multiple times in the same publication or in different
> publications.
> If you publish a table multiple times in the same publication the
> destination object name must be different. Does this answer your question?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> <vbhackatt...@.hotmail.com> wrote in message
> news:1172026819.916313.105530@.k78g2000cwa.googlegr oups.com...
>
|||Yes it does. The best way to do this would be to make the single subscriber
a publisher and the other current publishers subscribers.
In this case the single publisher could start off as empty and you could use
sp_addtabletocontents on the subscribers to push their data to the
publisher.
With filtering DML on subscriber a would move to the publisher and not be
replicated to subscriber b. The partition realignments you speak of
(deleting a row on subscriber b and have it inserted on subscriber a) are
handled if you use join filters which extend the join to all child rows of a
modified parent row.
Yours is a complex scenario - have I understood it correctly?
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
<vbhackattack@.hotmail.com> wrote in message
news:1172146434.103851.121880@.l53g2000cwa.googlegr oups.com...
>I will try to restate my scenario to clarify: I would like for a
> single subscriber (a site) to get 2 or more horizontal partitions of a
> publication (2 or more offices' data) in to the same local subscriber
> table, and be able to update them back to the publisher. I say "in to
> the same local table" so the local windows application reads/writes a
> single table and knows nothing about the replication configuration. I
> do not want multiple tables or multiple databases one for each
> horizontal partition. Reading the docs I figure replication could
> possibly handle this:
> - snapshots are partitioned so they can be loaded more than one in the
> same table, use option not to drop existing schema
> - updates with a single partition will work because insert/update/
> delete only touches rows within the partition subscription
> Might have to config manually since replication wizard might create
> the agent jobs with overlapping names
> One problem I see is if an update causes a row to disqualify for one
> partition and requalify for the other partition, this would translate
> in to a delete from one and insert in the other so the data gets
> dropped at the subscriber and then re-downloaded. Not a big deal with
> just a single table but eventually I'm extending this to multiple
> tables some with lots of data in each partition.
> Does that make my scenario clearer?
> On Feb 21, 8:58 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
