Merge Conflicts and Referential Integrity

I am trying to set up merge replication between a
publisher and 1 subscriber. I have selected the "include
declared referential intetegrity" option. I have
added "Not for Replication" to all of my triggers.
Occasionally, I end up with replication conflicts (either
at the subscriber or the publisher) saying that a row
could not be inserted because it violates a referential
constraint.
I don't understand why this is happening. According to
the online help, the declared referential integrity option
should ensure that articles are processed in order based
on the relationships (ie. inserting parent rows before
related child rows). The replication works most of the
time but I can usually recreate the problem by doing
multiple inserts at both the subscriber and publisher in a
short period of time. I have the replication schedule set
to 1 minute for my testing.
This causes a major issue for me because I can not resolve
the data even though the referential constraint would no
longer apply (ie. the row that it was complaining about is
there now). I am using an identity column and Replication
automatically adds constraints at the publisher and the
subscriber on this field. Therefore, it will not allow me
to resolve the conflict (ie. by adding the row now) since
its id is outside of the identity range.
After the snap shot was applied, I ensured that all of the
referential relationships, indexes, etc were copied over
and are still active. I have the latest service pack (ie.
SP3).
Any help would be greatly apreciated. I have included
part of my replication script to show how the articles are
set up.
exec sp_addmergearticle @.publication = N'BESMgmt',
@.article = N'UserComponentMap', @.source_owner = N'dbo',
@.source_object = N'UserComponentMap', @.type = N'table',
@.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver =
null, @.subset_filterclause = null, @.vertical_partition =
N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'true', @.pub_identity_range = 100,
@.identity_range = 100, @.threshold = 80,
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'BESMgmt',
@.article = N'UserComponentAffinity', @.source_owner =
N'dbo', @.source_object = N'UserComponentAffinity', @.type =
N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver =
null, @.subset_filterclause = null, @.vertical_partition =
N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'true', @.pub_identity_range = 100,
@.identity_range = 100, @.threshold = 80,
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
This problem is related to how changes are processed, sometimes child
records are processed before their parents are. The way to get around this
is to set the number of generations processed in a batch, preferrably to
2000.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim" <jmisasi@.rim.com> wrote in message
news:036a01c4abdc$648948d0$a301280a@.phx.gbl...
> I am trying to set up merge replication between a
> publisher and 1 subscriber. I have selected the "include
> declared referential intetegrity" option. I have
> added "Not for Replication" to all of my triggers.
> Occasionally, I end up with replication conflicts (either
> at the subscriber or the publisher) saying that a row
> could not be inserted because it violates a referential
> constraint.
> I don't understand why this is happening. According to
> the online help, the declared referential integrity option
> should ensure that articles are processed in order based
> on the relationships (ie. inserting parent rows before
> related child rows). The replication works most of the
> time but I can usually recreate the problem by doing
> multiple inserts at both the subscriber and publisher in a
> short period of time. I have the replication schedule set
> to 1 minute for my testing.
> This causes a major issue for me because I can not resolve
> the data even though the referential constraint would no
> longer apply (ie. the row that it was complaining about is
> there now). I am using an identity column and Replication
> automatically adds constraints at the publisher and the
> subscriber on this field. Therefore, it will not allow me
> to resolve the conflict (ie. by adding the row now) since
> its id is outside of the identity range.
> After the snap shot was applied, I ensured that all of the
> referential relationships, indexes, etc were copied over
> and are still active. I have the latest service pack (ie.
> SP3).
> Any help would be greatly apreciated. I have included
> part of my replication script to show how the articles are
> set up.
>
> exec sp_addmergearticle @.publication = N'BESMgmt',
> @.article = N'UserComponentMap', @.source_owner = N'dbo',
> @.source_object = N'UserComponentMap', @.type = N'table',
> @.description = null, @.column_tracking = N'true',
> @.pre_creation_cmd = N'drop', @.creation_script = null,
> @.schema_option = 0x000000000000CFF1, @.article_resolver =
> null, @.subset_filterclause = null, @.vertical_partition =
> N'false', @.destination_owner = N'dbo',
> @.auto_identity_range = N'true', @.pub_identity_range = 100,
> @.identity_range = 100, @.threshold = 80,
> @.verify_resolver_signature = 0,
> @.allow_interactive_resolver = N'false',
> @.fast_multicol_updateproc = N'true', @.check_permissions = 0
> GO
> exec sp_addmergearticle @.publication = N'BESMgmt',
> @.article = N'UserComponentAffinity', @.source_owner =
> N'dbo', @.source_object = N'UserComponentAffinity', @.type =
> N'table', @.description = null, @.column_tracking = N'true',
> @.pre_creation_cmd = N'drop', @.creation_script = null,
> @.schema_option = 0x000000000000CFF1, @.article_resolver =
> null, @.subset_filterclause = null, @.vertical_partition =
> N'false', @.destination_owner = N'dbo',
> @.auto_identity_range = N'true', @.pub_identity_range = 100,
> @.identity_range = 100, @.threshold = 80,
> @.verify_resolver_signature = 0,
> @.allow_interactive_resolver = N'false',
> @.fast_multicol_updateproc = N'true', @.check_permissions = 0
> GO
|||Jim,
BTW this is not an issue in SQL 2005, where PKs are processed before PKs
without any extra work on behalf of the programmer.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am new to replication and SQL Server for that matter.
So, how do I increase the number of generations processed
in a batch.
I am guessing I need to set this somewhere in my agent
profile. I am using the Default Agent Profile which has
the following parameters and values:
BcpBatchSize 10000
ChangesPerHistory 100
DestThreads 4
DownloadGenerationsPerBatch 100
DownloadReadChangesPerBatch 100
DownloadWriteChangesPerBatch 100
FastRowCount 1
HistoryVerboseLevel 1
KeepAliveMessageInterval 300
Login Timeout 15
MaxDownloadChanges 0
MaxUploadChanges 0
MetaDataRetentionCleanup 1
NumDeadlockRetries 5
PollingInterval 60
QueryTimeout 300
SrcThreads 3
StartQueueTimeout 0
UploadGenerationsPerBatch 100
UploadReadGenerationsPerBatch 100
UploadWriteGenerationsPerBatch 100
Validate 0
ValidateInterval 60
Do I set the Upload and Download Generations to 2000? Do
I set the Read and Write values as well? It looks like I
will have to create my own profile which is fine, but am I
on the right track?
Thank you,
Jim

>--Original Message--
>This problem is related to how changes are processed,
sometimes child
>records are processed before their parents are. The way
to get around this
>is to set the number of generations processed in a batch,
preferrably to[vbcol=seagreen]
>2000.
>
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"Jim" <jmisasi@.rim.com> wrote in message
>news:036a01c4abdc$648948d0$a301280a@.phx.gbl...
the "include[vbcol=seagreen]
(either[vbcol=seagreen]
option[vbcol=seagreen]
in a[vbcol=seagreen]
set[vbcol=seagreen]
resolve[vbcol=seagreen]
is[vbcol=seagreen]
Replication[vbcol=seagreen]
me[vbcol=seagreen]
since[vbcol=seagreen]
the[vbcol=seagreen]
(ie.[vbcol=seagreen]
are[vbcol=seagreen]
100,[vbcol=seagreen]
= 0[vbcol=seagreen]
@.type =[vbcol=seagreen]
N'true',[vbcol=seagreen]
100,[vbcol=seagreen]
= 0
>
>.
>
|||Jim,
Increase the -UploadGenerationsPerBatch and -
DownloadGenerationsPerBatch Merge Agent parameters to
their maximum value of 2000, which virtually eliminates
the possibility of processing a child article's
generation in a batch separate from the parent article's
generation.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Believe it or not, I am still getting conflicts (although
not as many). Is there anything else I can do?
Jim

>--Original Message--
>Jim,
>Increase the -UploadGenerationsPerBatch and -
>DownloadGenerationsPerBatch Merge Agent parameters to
>their maximum value of 2000, which virtually eliminates
>the possibility of processing a child article's
>generation in a batch separate from the parent article's
>generation.
>HTH,
>Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>.
>
|||I suppose you could have 2 separate publications - one for the parent and
one for the child, and run them as parent then child yourself. However as
far as I understand this shouldn't be necessary unless you have an enormous
number of records before synchronization. How many records do you have?
Could you synchronize more frequently perhaps to avoid a backlog?
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Jim,
the order of processing is not so straightforward (unlike
SQL 5005). Have a look at this article to better
understand the process:
http://support.microsoft.com/default.aspx?scid=kb;
[LN];307356.
You might try setting the FKs to NOT FOR REPLICATION so
this bypasses the constraint checks.
HTH,
Paul Ibison

>.
>