Recently added table to merge, another process is moving the records between
servers and beating merge causing conflicts. App so big - dev will have to
spend many hours finding cause & resolving. Until then I have to deal w/
conflicts...
How do I resolve conflicts when I have thousands of them? I want to keep the
winning change for all. Is it safe to simply delete all records from the
conflict table:
delete from conflict_MergePubName_MyTableName
Thanks
CB
Chris,
if you're happy with the default conflict resolution that has already
occurred, you should be able to cursor through your records and call
sp_deletemergeconflictrow
eg: exec sp_deletemergeconflictrow 'conflict_NorthwindRegionsMerge_Region',
'[dbo].[Region]', '{9E93B574-55A3-4D72-A108-EED0C8E6B899}',
'UK-3XSW02J.pubs'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Showing posts with label conflicts. Show all posts
Showing posts with label conflicts. Show all posts
Wednesday, March 28, 2012
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
>.
>
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
>.
>
Merge Conflict Data
hi all,
i observed some conflicts in conflict viewer.i am able to see the data in
conflict tables.my question is i included one userid column in each table to
identify the data from where it is inserted or updated.now in conflict table
i am getting only publisher side userid i am not getting the subscriber side
userid.
your help is appreciated.
thanks
reddy
What process is filling in the user id that does the modification? If you
are using a default and have your publisher configured to have the higher
priority, the publisher's id will "win".
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"reddy" <reddy@.discussions.microsoft.com> wrote in message
news:2A7B22B8-3FC6-4C41-8371-F2C3A9CF25BE@.microsoft.com...
> hi all,
> i observed some conflicts in conflict viewer.i am able to see the data in
> conflict tables.my question is i included one userid column in each table
to
> identify the data from where it is inserted or updated.now in conflict
table
> i am getting only publisher side userid i am not getting the subscriber
side
> userid.
> your help is appreciated.
> thanks
> reddy
i observed some conflicts in conflict viewer.i am able to see the data in
conflict tables.my question is i included one userid column in each table to
identify the data from where it is inserted or updated.now in conflict table
i am getting only publisher side userid i am not getting the subscriber side
userid.
your help is appreciated.
thanks
reddy
What process is filling in the user id that does the modification? If you
are using a default and have your publisher configured to have the higher
priority, the publisher's id will "win".
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"reddy" <reddy@.discussions.microsoft.com> wrote in message
news:2A7B22B8-3FC6-4C41-8371-F2C3A9CF25BE@.microsoft.com...
> hi all,
> i observed some conflicts in conflict viewer.i am able to see the data in
> conflict tables.my question is i included one userid column in each table
to
> identify the data from where it is inserted or updated.now in conflict
table
> i am getting only publisher side userid i am not getting the subscriber
side
> userid.
> your help is appreciated.
> thanks
> reddy
Subscribe to:
Posts (Atom)