Merge replication between two publishers with dynamic filters

Hello,
I am working on a distributed database system in which each site is a
publisher of a filtered set of data. It is necessary that a publisher can
subscribe to another publisher.
I am using merge replication, dynamic filters and push subscriptions. Each
site publishes the same tables with another filter.
When I test this scenario, I notice that the merge agent replicates only the
changes of the publisher to the subscriber. A change on the subscriber (thas
has also a publication on the same tables) is not replicated to the publisher.
Does someone know a solution for this?
Is it possible to have (bidirectional) merge replication between two
publishers with dynamic filters?
I hope someone can help me with this.
thanks in advance!
Marco Broenink
Could you please describe in more detail your publisher subscriber
configurations and the filters.
If the table has a column that you are using to filter from Publisher to
Subscriber and then make this subscriber a republisher and then try to use
the same column as the filter column, there is only set of data at the
republisher/subscriber.
I am not clear on your setup. Could you please repost with more elaborate
setup steps?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:CD4D84B8-A940-46BF-AC9A-5FF5098D2200@.microsoft.com...
> Hello,
> I am working on a distributed database system in which each site is a
> publisher of a filtered set of data. It is necessary that a publisher can
> subscribe to another publisher.
> I am using merge replication, dynamic filters and push subscriptions. Each
> site publishes the same tables with another filter.
> When I test this scenario, I notice that the merge agent replicates only
the
> changes of the publisher to the subscriber. A change on the subscriber
(thas
> has also a publication on the same tables) is not replicated to the
publisher.
> Does someone know a solution for this?
> Is it possible to have (bidirectional) merge replication between two
> publishers with dynamic filters?
> I hope someone can help me with this.
> thanks in advance!
> Marco Broenink
|||Thanks for your response!
I am using a dynamic filter. This filter uses a function. This function
needs the hostname and a filter-column to dermine if the row needs to be
filtered. The filter looks like:
SELECT <published_columns> FROM [dbo].[PublishedTable]
WHERE 1 = [dbo].[fn_DynamicFilter]([FilterColumn], HOST_NAME())
The filterfunction uses a mapping table that maps the contents of the
[FilterColumn] to hostnames.
With this mapping table, each publisher publishes its own part of all data.
So the publications of two publishers do not overlap. But the publications
are on the same tables.
Problem with this configuration is that changes of a subscriber are not
replicated to the publisher. It looks like that the subscriber's own
publication is blocking this.
I hope you can help me with this.
greetings, Marco.
"Mahesh [MSFT]" wrote:

> Could you please describe in more detail your publisher subscriber
> configurations and the filters.
> If the table has a column that you are using to filter from Publisher to
> Subscriber and then make this subscriber a republisher and then try to use
> the same column as the filter column, there is only set of data at the
> republisher/subscriber.
> I am not clear on your setup. Could you please repost with more elaborate
> setup steps?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:CD4D84B8-A940-46BF-AC9A-5FF5098D2200@.microsoft.com...
> the
> (thas
> publisher.
>
>
|||I have used 'global' subscriptions in stead of 'local' and this problem is
solved.
Now the changes are also replicated from subscriber to publisher.
Unfortunately, I have a new problem.
I use replication with dynamic filters. In my system it is possible that
data is added to the subscriber that doesnot pass the filter. When
replicating, this data is deleted at the subscriber and added to the
publisher.
How can I prevent this delete & insert ?
thanks in advance, Marco
"Marco Broenink" wrote:
[vbcol=seagreen]
> Thanks for your response!
> I am using a dynamic filter. This filter uses a function. This function
> needs the hostname and a filter-column to dermine if the row needs to be
> filtered. The filter looks like:
> SELECT <published_columns> FROM [dbo].[PublishedTable]
> WHERE 1 = [dbo].[fn_DynamicFilter]([FilterColumn], HOST_NAME())
> The filterfunction uses a mapping table that maps the contents of the
> [FilterColumn] to hostnames.
> With this mapping table, each publisher publishes its own part of all data.
> So the publications of two publishers do not overlap. But the publications
> are on the same tables.
> Problem with this configuration is that changes of a subscriber are not
> replicated to the publisher. It looks like that the subscriber's own
> publication is blocking this.
> I hope you can help me with this.
> greetings, Marco.
> "Mahesh [MSFT]" wrote:
|||Glad that you could work around your first problem, though to be frank, I am
still unclear of the setup.
Regarding your new problem,
If each subscriber inserts data that corresponds to only its subset of data
then you could try using a default of some kind to the tables. Like hostname
or something that will map appropriately to the filter condition and make it
pass. So everytime an insert happens at the subscriber, the filter condition
is met and then is successfully propagated to the publisher and does not get
deleted at the subscriber in turn.
Please note that this can work only if the subscriber always makes
"good" inserts, that is to say that the subscriber never expects to insert
data (that does not satisfy the filter) and then in turn expects the data to
be deleted by the publisher.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:664513B1-B610-453F-B033-6FD1B8720BE1@.microsoft.com...[vbcol=seagreen]
> I have used 'global' subscriptions in stead of 'local' and this problem is
> solved.
> Now the changes are also replicated from subscriber to publisher.
> Unfortunately, I have a new problem.
> I use replication with dynamic filters. In my system it is possible that
> data is added to the subscriber that doesnot pass the filter. When
> replicating, this data is deleted at the subscriber and added to the
> publisher.
> How can I prevent this delete & insert ?
> thanks in advance, Marco
>
> "Marco Broenink" wrote:
data.[vbcol=seagreen]
publications[vbcol=seagreen]
to[vbcol=seagreen]
use[vbcol=seagreen]
elaborate[vbcol=seagreen]
message[vbcol=seagreen]
a[vbcol=seagreen]
publisher can[vbcol=seagreen]
subscriptions. Each[vbcol=seagreen]
only[vbcol=seagreen]
subscriber[vbcol=seagreen]
|||thanks again for the response.
In my topology, I have different publishers of the same table. These
publishers use different filters. A subscriber can be subscribed to different
publishers.
For example:
Site A publishes table1
Site B publishes table1
Site C is subscribed to Site A table1. This subscribtion is filtered with a
dynamic filter F1.
Site C is also subscribed to Site B table1. This subscribtion is filtered
with another dynamic filter F2.
The different dynamic filters make sure that the subscription to Site A do
not overlap the subscription to Site B.
Thus: The table1 of C contains a subset of table1 of A and a subset of
table1 of B.
So: table1 of C contains two types of data:
- data that meets filtercondition F1 and doesnot meet filtercondition F2.
- data that meets filtercondition F2 and doesnot meet filtercondition F1.
So the problem is: The subscriber will contain data that doesnot meet one of
the filterconditions. When replicating to Site A (filter F1), data of filter
F2 is deleted. When replicating to Site B (filter F2), data of filter F1 is
deleted.
So in this scenario, I think it is not possible to make only 'good' inserts
because it violates always one of the two filtersconditions.
I hope you know a solution. Or am I trying to do something impossible?
greetings, Marco Broenink
"Mahesh [MSFT]" wrote:

> Glad that you could work around your first problem, though to be frank, I am
> still unclear of the setup.
> Regarding your new problem,
> If each subscriber inserts data that corresponds to only its subset of data
> then you could try using a default of some kind to the tables. Like hostname
> or something that will map appropriately to the filter condition and make it
> pass. So everytime an insert happens at the subscriber, the filter condition
> is met and then is successfully propagated to the publisher and does not get
> deleted at the subscriber in turn.
> Please note that this can work only if the subscriber always makes
> "good" inserts, that is to say that the subscriber never expects to insert
> data (that does not satisfy the filter) and then in turn expects the data to
> be deleted by the publisher.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:664513B1-B610-453F-B033-6FD1B8720BE1@.microsoft.com...
> data.
> publications
> to
> use
> elaborate
> message
> a
> publisher can
> subscriptions. Each
> only
> subscriber
>
>
|||Hi Marco,
Please correct me if I understood wrong:
So what you are saying is SiteA and SiteB are publishing the same tables,
but are not replicating to each other. Is that right?
But in turn are replicating that table to SiteC.
This is not supported.
In the first place, When SiteC subscribed to SiteA, it gets the table from
SiteA. Now when you configure SiteC to subscribe from SiteB, how did you
configure? Did you configure a no-sync subscription? If not, and you used
all the default settings then actually you will not even be able to complete
the subscription because the table at SiteC (got from SiteA) will be
attempted to drop and recreate with the scripts from SiteB which will fail.
If you want to do what you are trying to do, one solution is to have table1
at SiteA and replicate it to SiteC with the proper filter. Have table2 at
SiteB and replicate that to SiteC with the proper filter.
On the subscriber you can have a view on those two tables that will give you
a combined view for the results. But you may not be able to make DMLs on the
view directly. You will still need to do the DMLs on the actual tables.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:BADB7E44-D9C9-4333-B69E-9F5B33314BEF@.microsoft.com...
> thanks again for the response.
> In my topology, I have different publishers of the same table. These
> publishers use different filters. A subscriber can be subscribed to
different
> publishers.
> For example:
> Site A publishes table1
> Site B publishes table1
> Site C is subscribed to Site A table1. This subscribtion is filtered with
a
> dynamic filter F1.
> Site C is also subscribed to Site B table1. This subscribtion is filtered
> with another dynamic filter F2.
> The different dynamic filters make sure that the subscription to Site A do
> not overlap the subscription to Site B.
> Thus: The table1 of C contains a subset of table1 of A and a subset of
> table1 of B.
> So: table1 of C contains two types of data:
> - data that meets filtercondition F1 and doesnot meet filtercondition F2.
> - data that meets filtercondition F2 and doesnot meet filtercondition F1.
> So the problem is: The subscriber will contain data that doesnot meet one
of
> the filterconditions. When replicating to Site A (filter F1), data of
filter
> F2 is deleted. When replicating to Site B (filter F2), data of filter F1
is
> deleted.
> So in this scenario, I think it is not possible to make only 'good'
inserts[vbcol=seagreen]
> because it violates always one of the two filtersconditions.
> I hope you know a solution. Or am I trying to do something impossible?
> greetings, Marco Broenink
>
> "Mahesh [MSFT]" wrote:
I am[vbcol=seagreen]
data[vbcol=seagreen]
hostname[vbcol=seagreen]
make it[vbcol=seagreen]
condition[vbcol=seagreen]
get[vbcol=seagreen]
insert[vbcol=seagreen]
data to[vbcol=seagreen]
message[vbcol=seagreen]
problem is[vbcol=seagreen]
that[vbcol=seagreen]
function[vbcol=seagreen]
to be[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
not[vbcol=seagreen]
Publisher[vbcol=seagreen]
try to[vbcol=seagreen]
the[vbcol=seagreen]
no[vbcol=seagreen]
in[vbcol=seagreen]
is[vbcol=seagreen]
replicates[vbcol=seagreen]
the[vbcol=seagreen]
two[vbcol=seagreen]
|||thanks for the help!
Marco
"Mahesh [MSFT]" wrote:

> Hi Marco,
> Please correct me if I understood wrong:
> So what you are saying is SiteA and SiteB are publishing the same tables,
> but are not replicating to each other. Is that right?
> But in turn are replicating that table to SiteC.
> This is not supported.
> In the first place, When SiteC subscribed to SiteA, it gets the table from
> SiteA. Now when you configure SiteC to subscribe from SiteB, how did you
> configure? Did you configure a no-sync subscription? If not, and you used
> all the default settings then actually you will not even be able to complete
> the subscription because the table at SiteC (got from SiteA) will be
> attempted to drop and recreate with the scripts from SiteB which will fail.
> If you want to do what you are trying to do, one solution is to have table1
> at SiteA and replicate it to SiteC with the proper filter. Have table2 at
> SiteB and replicate that to SiteC with the proper filter.
> On the subscriber you can have a view on those two tables that will give you
> a combined view for the results. But you may not be able to make DMLs on the
> view directly. You will still need to do the DMLs on the actual tables.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:BADB7E44-D9C9-4333-B69E-9F5B33314BEF@.microsoft.com...
> different
> a
> of
> filter
> is
> inserts
> I am
> data
> hostname
> make it
> condition
> get
> insert
> data to
> message
> problem is
> that
> function
> to be
> the
> all
> not
> Publisher
> try to
> the
> no
> in
> is
> replicates
> the
> two
>
>