Can subscribed table support partial replication with localization

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HardKhor - 30 May 2006 03:33 GMT

Hi experts,

I am currently using SQL Server 2000. My system uses replication is
synchronize the data between HQ and the branches. Can SQL Server 2K support a
partial replication of the columns in a published table?

Partial replication means to replicate only some of the columns in a table
between HQ and branch, while leaving the non-replicated columns "localized".
E.g. in a Stock table, with columns [Price] and [Quantity], HQ controls the
price for all branches, while branch retains control of its own stock level.

I tried with "Filter Columns" but it only ended up removing the unchecked
columns in the subscriber, and I cannot add non-replicated columns into a
subscriber table used for replication.

Any ideas would be much appreciated. TQ.

Reply to this Message

Paul Ibison - 30 May 2006 20:11 GMT

The easiest way to achieve this is to partition the table into 2 tables and
just replicate one of them. A view can be used to amalgamate the data, and
an instead of trigger used to allow users to treat the view much like a
table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Reply to this Message

HardKhor - 31 May 2006 03:37 GMT

Hi Paul,

Thanks, views with triggers really powerful stuff. New alternatives for
architectural design.

Now it got me thinking more of these questions:

1) While I suspect there will be some performance overhead using the
StockView case, but based on your experience, would there be a HUGE
performance overhead, or would it be within an acceptable range?

2) On a similar note, how would the performance acceptability be if I add
index to the StockView?

If performance overhead is acceptable, then the StockView would certainly
behave like a Stock table!

> The easiest way to achieve this is to partition the table into 2 tables and
> just replicate one of them. A view can be used to amalgamate the data, and
> an instead of trigger used to allow users to treat the view much like a
> table.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com

Reply to this Message

Paul Ibison - 31 May 2006 09:22 GMT

If we conisder Table1 has 4 columns A,B,C,D where A is the PK. We partition
this table to Table2(A,B,C) and Table3(A,D) and create a view which
amalgamates the data together by inner joining the column As. Using the
"instead of" trigger should be fast, because updates and deletes are both
based on column A (the PK values) of the 2 tables. You could add an index to
the view but there shouldn't be any need - you'll be joining on the PK of
each table and these indexes will be picked up in any query.

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Reply to this Message