Merge Replication with only a subset of data at BOTH subscriber and publisher

I have a merge replication process (on test data) that is moving a
subset of data from one region to a central office. Now the central
office has it's own existing data, prior to initializing the merge
replication from this publisher.
Basically, when a row that existed prior to initialization is updated
at the subscriber, one that does not meet both a direct row filter and
a join filter, it is still being replicated back to the publisher, the
publisher looks like it then deletes all related records based on the
join filters because that row did not meet the criteria.
Am I trying to make merge rep do something that it does not do? I hope
that I am able to keep one subset of data in the merge process, and
have independent data on both the publisher AND subscriber.
Any help/direction is greatly appreciated.
Tony,
to have independant sets of data without truely editing the merge triggers
you really need to partition it and have separate publications. Views can be
used to amalgamate the data if needed. You can use 'Instead Of' triggers or
Partitioned Views to make them updatable.
HTH,
Paul Ibison
|||Paul,
Thanks for the information, I (stupidly) did not even consider that
possibility. I am going to set up a test here, and I might get back to
you if I run into any issues doing so.
Thanks for the insight!
Tony
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Paul, I did setup a test using views to partition off the data that I
want to publish, however it looks like when I publish those alone with
Merge replication that the data is not being transferred. The schema for
the views was initialized properly, but I think I am missing something.
You reference 'partitioned views'. Do I need to do something to the
views on the publisher in order to make changes to the data replicate
over?
Thanks in advance,
Tony
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Anthony,
I didn't intend you to create the view on the publisher :-). This is an
avenue you could go down if you use an indexed view, but it is an overhead
you don't require. All you need to do is to create separate publications.
Each one has a filter to take the rows you are interested in - effectively
to partition the table. These publications will be sent ot the subscriber
and created there as 2 separate tables. If you need to report/query these
tables on the subscriber as though they were one table, you can use views on
the subscriber for this. These subscriber views will be unions and if they
need to be updatable then you could use 'instead of' triggers or partitioned
views.
HTH,
Paul Ibison
|||Paul,
The one problem is that I can not change the schema at the subscriber
nor the publisher, as they are established as well as the data that we
are working with. Obviously, I can add to the schema, which is why I
took the indexed view comment from your response. Currently applications
access the tables directly, and they expect this replicated data to end
up there one way or another.
Basically, if I could replicate just a view from each Publisher to the
central Sub, and have the views seperate the data logically from one
another, then the Subscriber could still work with the data in the table
underneath without having to worry about filters which are not being
evaluated.
This make any sense to you, or am I off the beaten path here?
Thanks again,
Tony D
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Anthony,
on the publisher you won't need to change the schema, as you can separate
the table logically into two publications using row filters. On the
subscriber you'll have schema changes (additions) which can be transparent
to the user. Each publication replicates to a separate table. These could be
tables X and Y. The original table name is recreated on the subscriber as a
view which amalgamates (unions) the X and Y data. So from the subscriber's
point of view nothing has changed. However this view will only be editable
if you use an 'instead of' trigger or use a partitioned view. Either of
these mechanisms will filter the change into the respective replicated
table.
You mention having the 2 indexed views on the publisher, but they cannot
(easily) be replicated to the same table on the subscriber. You'll also lose
control of which changes are sent back to the publisher.
HTH,
Paul
|||Ok, I understand that so far. One question about the view on the
subscriber which amalgamates the data. You say to make this editable I
could make it a partitioned view. Is that just using 'With
Schemabinding', or do I need to index it also?
Thanks for your time Paul, this has been a help!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||One other hitch using different table names, currently all involved
tables at both the sub and pub have the same names. Is it at all
possible to publish a table so that it is replicated to a table with a
different name at the subscriber?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Anthony,
have a look at the @.destination_table parameter in sp_addarticle.
HTH,
Paul Ibison
sql

Merge Replication Problem Missing rows on subscriber

I have set up Mege replication where a publishing subscriber
subscribes to a central publisher. The publishing subscriber publishes
to other regional subscribers. All Subscriptions are set up as pull
subscriptions using dynamic snapshots. The problem however is that
when the associated application is used and merge replication running
across all machines at 1 minute intervals occurrs randomly some rows
within some of the articles are not recieved at the leaf node
subscribers (No errors are received or conflicts generated) . The
changes always make it out to the publishing subscriber. This
inconsistant state can be corrected by inititaing a reinitialisation of
the subscription for all subscrptions relating to the publishing
subscriber. When this occurrs the original shapshot is reapplied and
all the changes since the last snap shot are applied. The result is
all the records are now transferred. In addition if i use associated
web application to create records within the database and then manually
run each merge agent the data is successfully transferred.
I have tried a lot of differnet things to correct this problem.
(Monitored locks / deadlocks etc). Has anyone else experienced this
sort of behavior?
Look to see if these missing rows show up in the conflict viewer. If so you
may be able to roll these deletes back.
Are you using join filters? This could be a factor. If a parent row falls
out of the filter on the subscriber, it will take all rows in the child
tables out of the subscriber if keep_partition_changes is set to false.
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
"Davec" <dcowie@.ballsolutions.com> wrote in message
news:1138756934.548687.176410@.g47g2000cwa.googlegr oups.com...
>I have set up Mege replication where a publishing subscriber
> subscribes to a central publisher. The publishing subscriber publishes
> to other regional subscribers. All Subscriptions are set up as pull
> subscriptions using dynamic snapshots. The problem however is that
> when the associated application is used and merge replication running
> across all machines at 1 minute intervals occurrs randomly some rows
> within some of the articles are not recieved at the leaf node
> subscribers (No errors are received or conflicts generated) . The
> changes always make it out to the publishing subscriber. This
> inconsistant state can be corrected by inititaing a reinitialisation of
> the subscription for all subscrptions relating to the publishing
> subscriber. When this occurrs the original shapshot is reapplied and
> all the changes since the last snap shot are applied. The result is
> all the records are now transferred. In addition if i use associated
> web application to create records within the database and then manually
> run each merge agent the data is successfully transferred.
> I have tried a lot of differnet things to correct this problem.
> (Monitored locks / deadlocks etc). Has anyone else experienced this
> sort of behavior?
>
|||Thanks for the reply Hilary I really appreciate your help,
I am using Join filters with @.keep_partition_changes = N'true'.
There are no conflict tables
The problem seems to be that when synchronization bewteen my lowest
level leafnode subsciber and regional publisher occurr
the merge agent when enumerating pending changes does not pick up the
full set of data that should be delivered to the subscriber based on
the join filter.
I have managed to narrow down the sequence of events that cause the
problem i am experiencing
This is the Replcation topology i have used
CentralPublication ->Regional Publisher 1 (Global Subscription) ->
Subscriber 1 (Local Subscription)
In Step 1 My web application runs against the Central Publisher
database effectively creating rows of data in a number of tables (all
articles of the publication)
Step 2 - Replication takes place between central publisher and the
regional publisher via a global pull subscription. Rows are filtered
via Join filter to the regional Publisher. This seems to be operating
as i would expect
Step 3 - Replication then takes place between the regional Publisher
and the Regional Subscriber. (The regional Publisher republishes a
filtered set to the regional subscriber) Investigation of the
replicated data at the Central publisher is also correct at this stage
Step 4 - Perform mode transactions via web application such that when
synchronisation between central publisher and regional publisher take
place the join filter now includes some of the rows created in step 1
but not part of the filtred data in step 2
Step 5 - Merge Agent executes synchronizing between central publisher
and Regional Publisher. Inspection of the regional Publisher database
also is shown to be correct
Step 6 - Merge Agent executes on regional Subscriber to sync Regional
subscriber with Regional Publisher. And not all rows are transferred
Interestingly the missing rows seem to be the ones that were created in
step 1 and only satisfy the join filters at step 4 and 5.
Actually this problem i have just noticed seems very similar to one you
have been discussing on Developers Dex
http://www.codecomments.com/sql/mess...4712070&Page=1
with regards to Join Filters and GenerationIDs.
|||As part of my ivestigation i have noticed that the msMerge_GenHistory
table at the leafnode subscriber is populated with entries that have
not been necessaily delivered to the the subscriber. Does this sound
like expected operation. Could this account for the missing records in
that the merge process believes they have already been delivered.

Merge replication Primary Key violations

I have a Merge Replication Problem and I'm not sure how to proceed.

Given the following:

Multiple clients have merge subscriptions to a central Server's DB.
DB contains a table tblUser
tblUser has a Varchar field; UserLoginName, which is Primary Key.
It also has an integer field, UserValue

The replication is configured to resolve to the greater integer value.

Client 1 creates a new User
INSERT tblUser
(UserLoginName, UserValue)
VALUES('Fred',1)
At the same time Client 2 does the same thing, but with a larger value
INSERT tblUSer
(UserLoginName, UserValue)
VALUES('Fred', 2)

One of the inserts will get to the Central Server first.
The other will form a conflict.

A row insert at 'Client2.dbUsers' could not be propagated to 'CentralServer.dbUsers'.
This failure can be caused by a constraint violation.
Violation of PRIMARY KEY constraint 'PK_tblUser_1'. Cannot insert duplicate key in object 'dbo.tblUser'.

So now I have two different value fields and a conflict that cannot be resolved.

Is this fixable?
Do I need to make changes at athe Architechture level?

Hi Brian,

if you need to store both records from all subscribers, You should add a column for client id .So once tables replicates from all subscribers each client has their own id.

Example: if client 1 has the code 100 and client 2 has the code 200

the table at subscriber will be : 100,'Fred',1 (Record comes from client 1)

200,'Fred',1 (Record comes from client 2)

if you need to resolve the conflict and to store 1 record only you should assign priority for each subscriber

For more details check the following URL : http://support.microsoft.com/kb/315514

Regards,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com

|||

I need to store only the higher of the two.

If the record already exists and both try to update the record, only the higher is stored, as desired.

But when the record does not exist, both subscribers do an insert, and it does not store only the higher.

Reading all the KB articles on merge replication, it appears that Updates and Inserts are supposed to be resolved the same.

I can't use subscriber priority, as I don't care WHERE the record came from, only the value stored.

Merge Replication over Internet with MSDE and Sql Server (Urgent)

Hello,
I would like to setup Merge Replication between a central Sql Server as
publisher and a variety of MSDE clients as subscribers. The catch that I am
running into is that I want to be able to do this over the Internet. I have
set this up with SQL CE, so I assume that it is possible with MSDE, however,
I just can't seem to find any documentation on this. I would appreciate it
if someone could tell me if this is possible or not.
Other questions I have (but have not research a ton for yet are)
1) How can I programatically kick off a sync on the MSDE from a .NET
application?
2) If conflicts occur, how can I programatically get the conflicts back to
the client application to display in a form and allow the user to resolve
them?
It is frustrating that this is so easy with SQL CE and here I can't seem to
figure out how to do it with MSDE.
Thanks so much for your help.
Marie
Create your publication for anonymous pull. Use the merge ActiveX control on
the subscriber to pull the subscription.
You will need to specify the publisher name, network name (ie ipaddress or
FQDN), and transport mechanism using Publisher, PublisherAddress, and
PublisherNetwork properties respectively.
Check out this link for more info.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Marie" <Marie@.discussions.microsoft.com> wrote in message
news:70B8068D-06BA-4036-9433-BF3B5FBB9714@.microsoft.com...
> Hello,
> I would like to setup Merge Replication between a central Sql Server as
> publisher and a variety of MSDE clients as subscribers. The catch that I
am
> running into is that I want to be able to do this over the Internet. I
have
> set this up with SQL CE, so I assume that it is possible with MSDE,
however,
> I just can't seem to find any documentation on this. I would appreciate
it
> if someone could tell me if this is possible or not.
> Other questions I have (but have not research a ton for yet are)
> 1) How can I programatically kick off a sync on the MSDE from a .NET
> application?
> 2) If conflicts occur, how can I programatically get the conflicts back to
> the client application to display in a form and allow the user to resolve
> them?
> It is frustrating that this is so easy with SQL CE and here I can't seem
to
> figure out how to do it with MSDE.
> Thanks so much for your help.
> Marie
>
>
>
|||Actually I am a little wrong on question 2. With Windows Synchronization
Manager, you click on your subscription, click on properties, click on
other, and select Resolve Conflicts Interactively.
You can also use the Microsoft SQL Conflict Resolver control for this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23sqW9bYiEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Create your publication for anonymous pull. Use the merge ActiveX control
on
> the subscriber to pull the subscription.
> You will need to specify the publisher name, network name (ie ipaddress or
> FQDN), and transport mechanism using Publisher, PublisherAddress, and
> PublisherNetwork properties respectively.
> Check out this link for more info.
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Marie" <Marie@.discussions.microsoft.com> wrote in message
> news:70B8068D-06BA-4036-9433-BF3B5FBB9714@.microsoft.com...
I[vbcol=seagreen]
> am
> have
> however,
> it
to[vbcol=seagreen]
resolve
> to
>
|||BTW - your answer to number 2 is AFAIK - no. When you set up your merge
publication for interactive conflict resolution, pull it using Windows
Synchronization Manager, and a conflict occurs you get a dialog telling you
a conflict has occured and to contact your system administrator.
At this point you can educate your users to open the Conflict Resolver on
their own, or simply to contact you and you can resolve them.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23sqW9bYiEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Create your publication for anonymous pull. Use the merge ActiveX control
on
> the subscriber to pull the subscription.
> You will need to specify the publisher name, network name (ie ipaddress or
> FQDN), and transport mechanism using Publisher, PublisherAddress, and
> PublisherNetwork properties respectively.
> Check out this link for more info.
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Marie" <Marie@.discussions.microsoft.com> wrote in message
> news:70B8068D-06BA-4036-9433-BF3B5FBB9714@.microsoft.com...
I[vbcol=seagreen]
> am
> have
> however,
> it
to[vbcol=seagreen]
resolve
> to
>

Merge replication issue

I have merge replication set up running between a central database and
multiple subscribers. I am noticing that data that being sent from the
subscriber is not being applied to the publisher with conflicts. I am also
seeing that data that was in the subscriber yesterday is not in the
subscriber today. Some of the tables are set to use timestamp (latest wins)
as the conflict resolver while others are set to use the "default" conflict
resolver. What is the default? Why is data being removed on the subscriber
when it originated from there to begin with? If I change the conflict
resolver on the tables to be "subscriber always wins" will this help solve
the problem of missing data?
Cmoss,
do you have filters? If the rows aren't allowed through the filter clause,
this could explain why records added at the subscriber are removed on
synchronization.
The default resolver is publisher always wins, and if 2 subscribers
conflict, the first attains the priority of the publisher.
HTH,
Paul Ibison
|||Paul,
Thanks for the response. I do have filters on the articles, but the data
that is being removed is from the same tables that data was successfully
synchronized from before. I have changed the conflict type on some of my
crucial articles to be "subscriber always wins" becuase that is where the
data originates from. It is my understanding that I can change this
parameter at any time, correct?
"Paul Ibison" wrote:

> Cmoss,
> do you have filters? If the rows aren't allowed through the filter clause,
> this could explain why records added at the subscriber are removed on
> synchronization.
> The default resolver is publisher always wins, and if 2 subscribers
> conflict, the first attains the priority of the publisher.
> HTH,
> Paul Ibison
>
>
|||Yes - in my experience the resolver can be changed
dynamically. This'll mean that publisher deletes will be
discarded if the records are updated on the subscriber.
HTH,
Paul Ibison
|||Paul,
Thanks for the info. I an reviewing my subscriptions an have noticed
something odd. I have one subscription that synchronized last evening and
the results are as follows:
Merged 0 data changes ( 0 inserts, 0 updates, 0 deletes, 2 resolved conflicts)
How can I have conflicts if no data is synchronized between the local and
remote databases? Is there a way I can see what these conflicts are? I can
look at the data in the msmerge_delete_conflicts table but there were no
deletes performed.
"Paul Ibison" wrote:

> Yes - in my experience the resolver can be changed
> dynamically. This'll mean that publisher deletes will be
> discarded if the records are updated on the subscriber.
> HTH,
> Paul Ibison
>
|||Cmoss,
yes , you can right-click the publication and select 'View Conflicts'. The
conflict has already been resolved, but the details are logged and in the
conflict viewer you can reverse the selected winner/loser.
HTH,
Paul Ibison
|||I actually faces the same problem too. I used the default resolver for all
the conflict tables. Somehow I realize that there's conflict even though the
server did not do any changes and only the client do the update of a row. I
wonder why there could be conflicts even though only the subscriber do the
update. I have no choice but to switch to 'subscriber wins over publisher'
conflict resolver to solve the problem though it is not the ideal solution.
Yimmy

Merge Replication From Behind Corporate Firewall

I would like to replicate data between a central hosted server (which I
host) and a client server (hosted by my client). The client server is
behind a corporate firewall.
My understanding is that in order to perform Merge Replication both
servers would need to be able to see each other on port 1433, which
means both servers need to have a fixed IP address open to the accept
incoming/outgoing traffic from their respective IP addresses.

>From speaking with the client it's become apparent that they are not
going to provide a fixed IP address accessible to my server. Is there
any other way of doing this ie. replicate data from the client to my
hosted server?
BTW - the reason I'm doing this is that I have Reporting Services on my
hosted server so the idea was that the client could replicate data up
to my server, I maintain it on an ongoing basis and provide reports
through reporting services.
Any suggestions/ideas ?
Thanks for the help !
If your publisher has a fixed IP address this will work. You can pull the
snapshot using FTP, so they will have to open that port.
On your subscriber you will have to create an alias using client network
utility for the publisher using the fixed IP address.
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
<ciaran@.insero-technologies.com> wrote in message
news:1108055889.511697.180130@.o13g2000cwo.googlegr oups.com...
> I would like to replicate data between a central hosted server (which I
> host) and a client server (hosted by my client). The client server is
> behind a corporate firewall.
> My understanding is that in order to perform Merge Replication both
> servers would need to be able to see each other on port 1433, which
> means both servers need to have a fixed IP address open to the accept
> incoming/outgoing traffic from their respective IP addresses.
> going to provide a fixed IP address accessible to my server. Is there
> any other way of doing this ie. replicate data from the client to my
> hosted server?
> BTW - the reason I'm doing this is that I have Reporting Services on my
> hosted server so the idea was that the client could replicate data up
> to my server, I maintain it on an ongoing basis and provide reports
> through reporting services.
> Any suggestions/ideas ?
> Thanks for the help !
>
|||Hi Hilary,
Thanks for the response !
I've looked into pulling the snapshot using FTP, and while the BOL give
a good overview of setting this up from the Publication side, there is
very little covered from the subscriber side (any ideas on
references?).
I did set up the alias for my Publisher using a fixed IP address, but I
can't see where I would reference this setting up my subscription.
A Confused ..
Ciaran ;o)
Hilary Cotter wrote:
> If your publisher has a fixed IP address this will work. You can pull
the
> snapshot using FTP, so they will have to open that port.
> On your subscriber you will have to create an alias using client
network[vbcol=seagreen]
> utility for the publisher using the fixed IP address.
> --
> 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
> <ciaran@.insero-technologies.com> wrote in message
> news:1108055889.511697.180130@.o13g2000cwo.googlegr oups.com...
(which I[vbcol=seagreen]
is[vbcol=seagreen]
accept[vbcol=seagreen]
not[vbcol=seagreen]
there[vbcol=seagreen]
my[vbcol=seagreen]
on my[vbcol=seagreen]
up[vbcol=seagreen]
|||Once you setup the subscription you just have to change the "Snapshot file
location" to FTP in the subscription properties.
Jos.
<ciaran@.insero-technologies.com> wrote in message
news:1108066869.083530.26340@.z14g2000cwz.googlegro ups.com...
> Hi Hilary,
> Thanks for the response !
> I've looked into pulling the snapshot using FTP, and while the BOL give
> a good overview of setting this up from the Publication side, there is
> very little covered from the subscriber side (any ideas on
> references?).
> I did set up the alias for my Publisher using a fixed IP address, but I
> can't see where I would reference this setting up my subscription.
> A Confused ..
> Ciaran ;o)
> Hilary Cotter wrote:
> the
> network
> (which I
> is
> accept
> not
> there
> my
> on my
> up
>