Merge replicaton: How to customize conflict detection, resolution?

We have a simple problem:
We have a table Assets[asset_id, title] and a table
Asset_Versions[asset_id, version_no]. In addition, there are columns
added by replication for storing the ROWGUID.
After synchronzation, assume both Node1 and Node2 have
Assets(1,'XYZ') and Assets_Versions(1,1)
Before syncing again a new version is added at both nodes so that both
Node1 and Node2 have
Assets(1,'XYZ') and Assets_Versions(1,1)(1,2)
Now when it syncs again, the publisher will make another entry of
Assets_Versions(1,2) in the subscriber thus disturbing the business
logic because the Subscriber will now have 2 entries for version_no=2.
How can we make Replication identify this as a conflict. Can we write
a conflict resolver for such a situation?
Thanks and regards,
YashWhat is the PK on Assets_Versions? I think if it includes the id and version
,
then this will get identified as a conflict.
HTH,
Paul Ibison|||Actually I can't test it here but I believe I was incorrect earlier and that
you'll just get an error instead - perhaps you can test this for me. What I'
m
thinking of now is that you could just have id and version in the
asset_versions table and update rather than add new versions:
ID, version, guid
1,1,xxx
on next version we have:
ID, version, guid
1,2,xxx
If this fits in with your application it'll work as this will cause a
conflict if done on 2 nodes before syncing.
HTH,
Paul Ibison

Merge replicaton: How to customize conflict detection, resolution?

We have a simple problem:
We have a table Assets[asset_id, title] and a table
Asset_Versions[asset_id, version_no]. In addition, there are columns
added by replication for storing the ROWGUID.
After synchronzation, assume both Node1 and Node2 have
Assets(1,'XYZ') and Assets_Versions(1,1)
Before syncing again a new version is added at both nodes so that both
Node1 and Node2 have
Assets(1,'XYZ') and Assets_Versions(1,1)(1,2)
Now when it syncs again, the publisher will make another entry of
Assets_Versions(1,2) in the subscriber thus disturbing the business
logic because the Subscriber will now have 2 entries for version_no=2.
How can we make Replication identify this as a conflict. Can we write
a conflict resolver for such a situation?
Thanks and regards,
Yash
What is the PK on Assets_Versions? I think if it includes the id and version,
then this will get identified as a conflict.
HTH,
Paul Ibison
|||Actually I can't test it here but I believe I was incorrect earlier and that
you'll just get an error instead - perhaps you can test this for me. What I'm
thinking of now is that you could just have id and version in the
asset_versions table and update rather than add new versions:
ID, version, guid
1,1,xxx
on next version we have:
ID, version, guid
1,2,xxx
If this fits in with your application it'll work as this will cause a
conflict if done on 2 nodes before syncing.
HTH,
Paul Ibison

Merge replicaton: How to customize conflict detection, resolution?

We have a simple problem:
We have a table Assets[asset_id, title] and a table
Asset_Versions[asset_id, version_no]. In addition, there are columns
added by replication for storing the ROWGUID.
After synchronzation, assume both Node1 and Node2 have
Assets(1,'XYZ') and Assets_Versions(1,1)
Before syncing again a new version is added at both nodes so that both
Node1 and Node2 have
Assets(1,'XYZ') and Assets_Versions(1,1)(1,2)
Now when it syncs again, the publisher will make another entry of
Assets_Versions(1,2) in the subscriber thus disturbing the business
logic because the Subscriber will now have 2 entries for version_no=2.
How can we make Replication identify this as a conflict. Can we write
a conflict resolver for such a situation?
Thanks and regards,
Yash
What is the PK on Assets_Versions? I think if it includes the id and version,
then this will get identified as a conflict.
HTH,
Paul Ibison
|||Actually I can't test it here but I believe I was incorrect earlier and that
you'll just get an error instead - perhaps you can test this for me. What I'm
thinking of now is that you could just have id and version in the
asset_versions table and update rather than add new versions:
ID, version, guid
1,1,xxx
on next version we have:
ID, version, guid
1,2,xxx
If this fits in with your application it'll work as this will cause a
conflict if done on 2 nodes before syncing.
HTH,
Paul Ibison

Merge replicaton: How to customize conflict detection, resolution?

We have a simple problem:
We have a table Assets[asset_id, title] and a table
Asset_Versions[asset_id, version_no]. In addition, there are columns
added by replication for storing the ROWGUID.
After synchronzation, assume both Node1 and Node2 have
Assets(1,'XYZ') and Assets_Versions(1,1)
Before syncing again a new version is added at both nodes so that both
Node1 and Node2 have
Assets(1,'XYZ') and Assets_Versions(1,1)(1,2)
Now when it syncs again, the publisher will make another entry of
Assets_Versions(1,2) in the subscriber thus disturbing the business
logic because the Subscriber will now have 2 entries for version_no=2.
How can we make Replication identify this as a conflict. Can we write
a conflict resolver for such a situation?
Thanks and regards,
YashWhat is the PK on Assets_Versions? I think if it includes the id and version,
then this will get identified as a conflict.
HTH,
Paul Ibison|||Actually I can't test it here but I believe I was incorrect earlier and that
you'll just get an error instead - perhaps you can test this for me. What I'm
thinking of now is that you could just have id and version in the
asset_versions table and update rather than add new versions:
ID, version, guid
1,1,xxx
on next version we have:
ID, version, guid
1,2,xxx
If this fits in with your application it'll work as this will cause a
conflict if done on 2 nodes before syncing.
HTH,
Paul Ibison|||A primary key or unique index on Assets_Versions should prevent such a
double insert/
--
relevantNoise - dedicated to mining blogs for business intelligence.
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
<yashgt@.gmail.com> wrote in message
news:1187325787.241826.116170@.m37g2000prh.googlegroups.com...
> We have a simple problem:
> We have a table Assets[asset_id, title] and a table
> Asset_Versions[asset_id, version_no]. In addition, there are columns
> added by replication for storing the ROWGUID.
> After synchronzation, assume both Node1 and Node2 have
> Assets(1,'XYZ') and Assets_Versions(1,1)
> Before syncing again a new version is added at both nodes so that both
> Node1 and Node2 have
> Assets(1,'XYZ') and Assets_Versions(1,1)(1,2)
> Now when it syncs again, the publisher will make another entry of
> Assets_Versions(1,2) in the subscriber thus disturbing the business
> logic because the Subscriber will now have 2 entries for version_no=2.
> How can we make Replication identify this as a conflict. Can we write
> a conflict resolver for such a situation?
> Thanks and regards,
> Yash
>sql

Merge replication, conflict resolution question...

Hi,
I'm trying to resolve a merge replication conflict problem, and I'm not sure
how to do it, additionally to this, I imagine that this is a very common
problem, so, some expert may point me on the right direction.
I have 3 tables that needs to be replicated, 1 publisher, 10 anonymous
subscribers that do a 'pull' and each subscriber synchronize automatically
once a day, a requirement is that all conflicts must be resolved without
human intervention.
These are the structures of the tables I'm using :
CREATE TABLE [dbo].[States] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Cities] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StateId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Properties] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[PublicationDate] [datetime] NOT NULL ,
[Address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CityId] [uniqueidentifier] NOT NULL ,
[Price] [money] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[States] WITH NOCHECK ADD
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
ALTER TABLE [dbo].[Cities] WITH NOCHECK ADD
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
ALTER TABLE [dbo].[Properties] WITH NOCHECK ADD
CONSTRAINT [PK_Properties] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
ALTER TABLE [dbo].[States] ADD
CONSTRAINT [DF_States_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [IX_States] UNIQUE NONCLUSTERED
(
[Name]
) ON [PRIMARY]
ALTER TABLE [dbo].[Cities] ADD
CONSTRAINT [DF_Cities_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [IX_Cities] UNIQUE NONCLUSTERED
(
[Name]
) ON [PRIMARY]
ALTER TABLE [dbo].[Properties] ADD
CONSTRAINT [DF_Properties_Id] DEFAULT (newid()) FOR [Id]
ALTER TABLE [dbo].[Cities] ADD
CONSTRAINT [FK_Cities_States] FOREIGN KEY
(
[StateId]
) REFERENCES [dbo].[States] (
[Id]
)
ALTER TABLE [dbo].[Properties] ADD
CONSTRAINT [FK_Properties_Cities] FOREIGN KEY
(
[CityId]
) REFERENCES [dbo].[Cities] (
[Id]
)
Ok. the three tables needs to be merged and I want to resolve this problem :
Subscriber1 enter this data :
State [1, 'state1']
City [1, 'city1', 1]
Subscriber2 enter this data :
State [2, 'state1']
City [2, 'city2', 2]
Now subscriber1 synchronize and then subscriber2, and the data on the
publisher is :
State [1, 'state1']
City [1, 'city1', 1]
(the second state1 cant be inserted because of the unique contraint on the
name and the city2 cant be inserted because the fk does not exists)
(this is with the default resolver)
And I want this to be resolved as :
State [1, 'state1']
City [1, 'city1', 1]
City [2, 'city2', 1]
So, the questions are :
Can this be resolved with a custom stored procedure resolver ?
It seems that I need to run an stored procedure on the publisher just after
a subscriber upload his data, so the modified keys are properly changed at
the subscriber when the data back to him... obiously, for this to work I
need to remove the name's unique constraints, and handle this uniqueness
manually... can this solution be applied ?
I would really apressiate some help, thanks.
Paul.
Please any advice ?
"-ND-" <ndeath@.gmx.net> wrote in message
news:%23WwDRSvxEHA.1168@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to resolve a merge replication conflict problem, and I'm not
sure
> how to do it, additionally to this, I imagine that this is a very common
> problem, so, some expert may point me on the right direction.
> I have 3 tables that needs to be replicated, 1 publisher, 10 anonymous
> subscribers that do a 'pull' and each subscriber synchronize automatically
> once a day, a requirement is that all conflicts must be resolved without
> human intervention.
> These are the structures of the tables I'm using :
> CREATE TABLE [dbo].[States] (
> [Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[Cities] (
> [Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [StateId] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[Properties] (
> [Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [PublicationDate] [datetime] NOT NULL ,
> [Address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [CityId] [uniqueidentifier] NOT NULL ,
> [Price] [money] NOT NULL
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[States] WITH NOCHECK ADD
> CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Cities] WITH NOCHECK ADD
> CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Properties] WITH NOCHECK ADD
> CONSTRAINT [PK_Properties] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[States] ADD
> CONSTRAINT [DF_States_Id] DEFAULT (newid()) FOR [Id],
> CONSTRAINT [IX_States] UNIQUE NONCLUSTERED
> (
> [Name]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Cities] ADD
> CONSTRAINT [DF_Cities_Id] DEFAULT (newid()) FOR [Id],
> CONSTRAINT [IX_Cities] UNIQUE NONCLUSTERED
> (
> [Name]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Properties] ADD
> CONSTRAINT [DF_Properties_Id] DEFAULT (newid()) FOR [Id]
> ALTER TABLE [dbo].[Cities] ADD
> CONSTRAINT [FK_Cities_States] FOREIGN KEY
> (
> [StateId]
> ) REFERENCES [dbo].[States] (
> [Id]
> )
> ALTER TABLE [dbo].[Properties] ADD
> CONSTRAINT [FK_Properties_Cities] FOREIGN KEY
> (
> [CityId]
> ) REFERENCES [dbo].[Cities] (
> [Id]
> )
>
> Ok. the three tables needs to be merged and I want to resolve this problem
:
> Subscriber1 enter this data :
> State [1, 'state1']
> City [1, 'city1', 1]
> Subscriber2 enter this data :
> State [2, 'state1']
> City [2, 'city2', 2]
> Now subscriber1 synchronize and then subscriber2, and the data on the
> publisher is :
> State [1, 'state1']
> City [1, 'city1', 1]
> (the second state1 cant be inserted because of the unique contraint on the
> name and the city2 cant be inserted because the fk does not exists)
> (this is with the default resolver)
> And I want this to be resolved as :
> State [1, 'state1']
> City [1, 'city1', 1]
> City [2, 'city2', 1]
> So, the questions are :
> Can this be resolved with a custom stored procedure resolver ?
> It seems that I need to run an stored procedure on the publisher just
after
> a subscriber upload his data, so the modified keys are properly changed at
> the subscriber when the data back to him... obiously, for this to work I
> need to remove the name's unique constraints, and handle this uniqueness
> manually... can this solution be applied ?
> I would really apressiate some help, thanks.
> Paul.
>

Merge Replication Conflict Resolution Latency?

I've been having a hard time finding a definitive answer about merge
replication and any sort of latency on a transaction between a
subscriber and publisher. I apologize in advance for the lengthy post,
but it's difficult to really "cut to the meat" of this one
Here's my scenario: I'm developing a .NET CF application on a PocketPC
that stores data in a SQL CE database that merges with a SQL 2000
publisher when the PDA is docked. Generally speaking, what goes up is
properly reflected when it comes back down via a "is complete" row
filter that simply looks for a flag that is set to 1. For example:
-Publisher has 128 rows
-PDA has 128 rows, 28 rows have been "completed"
-Instantiate merge with publisher
-Result is that there are 128 rows on publisher, 28 are complete, row
filter disregards 28 complete records and sends down 100 records that
are still incomplete to PDA
This seems to work great. Now enter conflicts. I have the resolver set
such that the subscriber always wins. We've had several cases where the
server has been under moderate to heavy load, and there have been a lot
of conflicts. This was an actual occurrence:
-Publisher has 79 rows
-PDA has 79 rows, 69 rows have been "completed"
-Publisher had 79 rows *removed*
-Instantiate merge with publisher
-Result is that there are 69 conflicts at publisher, the subscriber wins
all, but there are still *79* rows on the PDA after merge finishes
-I wait a minute and try again, this time there are about 50 rows on the
subscriber
-I repeat this process several times over the course of 5 minutes until
finally, I am left with the correct number of records on the PDA (0)
My question is, is this normal behavior for the resolver? Are conflicts
generally allowed to be "handed off" and the merge allowed to complete
without reflecting their outcome? If so, is there any way to tune or
change this behavior?
This also has me concerned about a normal merge without any conflicts.
Would there ever be a case in which a merge would get out of sync in
this way? My understanding is that instantiating a merge will always
result in both the supplying subscriber and publisher ending up with the
same data at that moment. My merge agents are all using the default
profile.
Thanks a BUNCH for any help you can offer. I've had an impossible time
answering these questions and I'm a programmer before a DBA
-Mike
Mike wrote:

> -Publisher has 79 rows
> -PDA has 79 rows, 69 rows have been "completed"
> -Publisher had 79 rows *removed*
> -Instantiate merge with publisher
> -Result is that there are 69 conflicts at publisher, the subscriber wins
> all, but there are still *79* rows on the PDA after merge finishes
> -I wait a minute and try again, this time there are about 50 rows on the
> subscriber
> -I repeat this process several times over the course of 5 minutes until
> finally, I am left with the correct number of records on the PDA (0)
I apologize, I had my scenarios crossed. This should have read:
-Publisher has 79 rows
-PDA has 79 rows, 69 rows have been "completed"
-Publisher had all 79 rows *modified*
-Instantiate merge with publisher
-Result is that there are 69 conflicts at publisher, the subscriber wins
all and they are noted as resolved in the agent history, but there are
still *79* rows on the PDA after merge finishes
-I wait a minute and try again, this time there are about 50 rows on the
subscriber
-I repeat this process several times over the course of 5 minutes, each
time the number of rows at the subscriber decreases, until finally, I am
left with the correct number of records on the PDA (10)
Thanks
-Mike