I have some questions about how the merge-agents deal with batches and its
effect on the order of merge replication.
As described in
http://support.microsoft.com/default...B;EN-US;307356 the processing
order of the merge agent can result in foreign key conflicts when Parent and
child changes are split across generation batches.
In my db-system, when inserting large volumes of data, these foreign key
conflicts occur when replicating. So I have increased the
-UploadGenerationsPerBatch and -DownloadGenerationsPerBatch parameters to
their maximum of 2000. But the foreign key conflicts keep happening.
I don't understand this, because when I check the MsMerge contents table,
there are not even 2000 generations. This table contains 122 generations and
10500 datachanges.
My questions are:
- Why do I keep the FK conflicts, while the number of generations is below
2000 ?
- Is there also a maximum to the number of changes in a batch?
- What do the parameters MaxDownloadChanges, MaxUploadChanges,
UploadReadChangesPerBatch, DownloadReadChangesPerBatch,
UploadWriteChangesPerBatch, DownloadWriteChangesPerBatch mean with regard to
the parameters UploadGenerationsPerBatch, DownloadGenerationsPerBatch ?
These params seems to conflict eachother.
Unfortunately setting the foreign keys on NOT FOR REPLICATION is not a good
option for my db-system.
thanks in advance,
Marco Broenink
To that would mean the the microsoft article of
http://support.microsoft.com/default...B;EN-US;307356 is not
completely correct, because it says : 'You can increase the
-UploadGenerationsPerBatch and the -DownloadGenerationsPerBatch parameters
discussed previously to avoid splitting parent and child changes across
generation batches.'
Setting the NFR attribute of Foreign keys have as side-effect that
replication can result in a db-state in which violating Foreign Keys exist.
For example when Site A adds child X to parent Y while concurrently Site B
deletes parent Y. After replication, child X contains a reference to a
non-existing parent Y. How can I avoid such a situation on database level ?
Thanks for your help,
Marco Broenink
"Paul Ibison" wrote:
> Marco,
> it is my experience that the order of application of
> merge records can't be controlled, regardless of the size
> of the -UploadGenerationsPerBatch and -
> DownloadGenerationsPerBatch parameters, so the NFR
> attribute is still needed. Incidentally, in SQL Server
> 2005 it is entirely controllable.
> As for the conflicting parameters, I would expect that
> the generations parameters are primary, with the changes
> parameters secondary in importance. I'll check this when
> I get a moment.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Marco,
this phrase: 'to avoid splitting parent and child changes
across generation batches.' doesn't mention the ordering
of applied changes at individual row level, ie the child
record could still be processed before the parent, even
if they are in the same batch.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
The concerned article tells also about the order in which changes are
replicated.
My understanding of the article is:
- The merge agent processes the changes in two groups.
- All articles that are involved in joinfilters or Foreignkey (DRI)
relations are put in the second group. This group contains all parent child
related changes.
- INSERTs of a paticular group are processed in ascending nickname order.
- The nickname of a parent is smaller then the nickname of a child (I've
checked this in my database).
- So this all would imply that an insert of a parent is replicated before
the insert of a child.
Please tell me at what point my understanding is wrong.
Thanks, Marco
"Paul Ibison" wrote:
> Marco,
> this phrase: 'to avoid splitting parent and child changes
> across generation batches.' doesn't mention the ordering
> of applied changes at individual row level, ie the child
> record could still be processed before the parent, even
> if they are in the same batch.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Marco,
I'll look into this in more detail and will post back
asap.
Regards,
Paul
>--Original Message--
>Paul,
>The concerned article tells also about the order in
which changes are
>replicated.
>My understanding of the article is:
>- The merge agent processes the changes in two groups.
>- All articles that are involved in joinfilters or
Foreignkey (DRI)
>relations are put in the second group. This group
contains all parent child
>related changes.
>- INSERTs of a paticular group are processed in
ascending nickname order.
>- The nickname of a parent is smaller then the nickname
of a child (I've
>checked this in my database).
>- So this all would imply that an insert of a parent is
replicated before[vbcol=seagreen]
>the insert of a child.
>Please tell me at what point my understanding is wrong.
>Thanks, Marco
>"Paul Ibison" wrote:
changes[vbcol=seagreen]
ordering[vbcol=seagreen]
child[vbcol=seagreen]
even
>.
>
|||Marco,
as promised:
http://www.replicationanswers.com/Me...derArticle.htm
Rgds,
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:14eb01c52b0c$73c6af90$a601280a@.phx.gbl...[vbcol=seagreen]
> Marco,
> I'll look into this in more detail and will post back
> asap.
> Regards,
> Paul
> which changes are
> Foreignkey (DRI)
> contains all parent child
> ascending nickname order.
> of a child (I've
> replicated before
> changes
> ordering
> child
> even
|||Paul,
thanks for the link. Unfortunately I get a Page Not Found when clicking it.
Is the link temporarily disabled?
greetings, Marco
"Paul Ibison" wrote:
> Marco,
> as promised:
> http://www.replicationanswers.com/Me...derArticle.htm
> Rgds,
> Paul
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:14eb01c52b0c$73c6af90$a601280a@.phx.gbl...
>
>
|||I've read the article on
http://www.replicationanswers.com/Me...derArticle.asp
Thanks!
Marco
"Marco Broenink" wrote:
[vbcol=seagreen]
> Paul,
> thanks for the link. Unfortunately I get a Page Not Found when clicking it.
> Is the link temporarily disabled?
> greetings, Marco
> "Paul Ibison" wrote:
2012年2月16日星期四
Batches, generations and replication-order
标签:
batches,
database,
described,
generations,
itseffect,
merge,
merge-agents,
microsoft,
mysql,
oracle,
order,
replication,
replication-order,
server,
sql
订阅:
博文评论 (Atom)
没有评论:
发表评论