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日星期四
2012年2月13日星期一
Batch insert (OLAP)
Hi can any one help me with the skeleton script (sample one)of running Bulk insert in batches...... I need to do it in batches as the input data is huge....
The logic is I have to insert thru bcp in fact table...
After that batch execution for 50,000 thousand record... wise... if any of the batch failes i need to identify and have to rerun from that point onwards..... this is OLAP thing...what's the data source?
50,000 ain't that much btw|||50,000 record comes in one batch that waht I meant...... so if there is 1 million record 20 batches will be there...
The source come from DB2 or so which we get it as source file... we create staging table for that in Sql Server... Now we need to do the rest porting data to Fact & Dimension tables|||what script?
you can use create a failover process in dts to allow x number of error rows to pass through the ETL and then you can clean them up the next day if you prefer. its part of the error reporting process in dts tasks.|||So the data is already in a table and you're trying to limit the impact to the logs...can you describe your process how are you building your warehouse..|||:eek: I didnt get u I never asked for error reporting stuff!!!!!|||http://www.winnetmag.com/article/articleid/42903/42903.html
I was trying to do the same way how the above article describe to solve such kinda stuff
The logic is I have to insert thru bcp in fact table...
After that batch execution for 50,000 thousand record... wise... if any of the batch failes i need to identify and have to rerun from that point onwards..... this is OLAP thing...what's the data source?
50,000 ain't that much btw|||50,000 record comes in one batch that waht I meant...... so if there is 1 million record 20 batches will be there...
The source come from DB2 or so which we get it as source file... we create staging table for that in Sql Server... Now we need to do the rest porting data to Fact & Dimension tables|||what script?
you can use create a failover process in dts to allow x number of error rows to pass through the ETL and then you can clean them up the next day if you prefer. its part of the error reporting process in dts tasks.|||So the data is already in a table and you're trying to limit the impact to the logs...can you describe your process how are you building your warehouse..|||:eek: I didnt get u I never asked for error reporting stuff!!!!!|||http://www.winnetmag.com/article/articleid/42903/42903.html
I was trying to do the same way how the above article describe to solve such kinda stuff
Batch Failing
I run two batches at night time:
1) A batch that updates all the records one by one to
calculate special interest on accounts.
2) The databas optimization batch that cleans up pages,
free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column
information is insufficient or incorrect. Too many rows
were affected by update." I do know that batch 2 is
running in tbe background still. They both take around 3-4
hours each. It's hard to schedual them apart enough to
keep them from running at the same time because their run
lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason RoozeeThis is a multi-part message in MIME format.
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
My guess is that batch 1 is using a cursor or raw ADO to do updates on a =table that does not have a primary key. Ensure that all tables have =primary keys and that you make every effort to replace row-by-row =processing with set-level processing.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
I run two batches at night time:
1) A batch that updates all the records one by one to calculate special interest on accounts.
2) The databas optimization batch that cleans up pages, free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column information is insufficient or incorrect. Too many rows were affected by update." I do know that batch 2 is running in tbe background still. They both take around 3-4 hours each. It's hard to schedual them apart enough to keep them from running at the same time because their run lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason Roozee
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
My guess is that batch 1 is using a =cursor or raw ADO to do updates on a table that does not have a primary key. =Ensure that all tables have primary keys and that you make every effort to replace row-by-row processing with set-level processing.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee" wrote in =message news:2bb701c373b9$29=99dfc0$a401280a@.phx.gbl...I run two batches at night time:1) A batch that updates all the =records one by one to calculate special interest on accounts.2) The =databas optimization batch that cleans up pages, free space, rebuilds =indexes, etc.My batch 1 is failing with this error: "Key column =information is insufficient or incorrect. Too many rows were affected by =update." I do know that batch 2 is running in tbe background still. They both take =around 3-4 hours each. It's hard to schedual them apart enough to keep =them from running at the same time because their run lengths are so unpredictable. Any ideas?My updates are using ado 2.7Jason Roozee
--=_NextPart_000_012C_01C37398.83003D00--|||This is a multi-part message in MIME format.
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ste-level process avoids the use of cursors and loops. Here, you would =use, say, a single UPDATE statement and apply the calculation across all =of the target rows.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2c0e01c373bb$e9832330$a401280a@.phx.gbl...
Yes, all the tables have a primary key and it's included in the select list of fields in my ADO record set...
"SET LEVEL" processing' Explain.
Jason
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jason Roozee" <jason@.camcoinc.net> wrote in message news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
>I run two batches at night time:
>1) A batch that updates all the records one by one to >calculate special interest on accounts.
>2) The databas optimization batch that cleans up pages, >free space, rebuilds indexes, etc.
>My batch 1 is failing with this error: "Key column >information is insufficient or incorrect. Too many rows >were affected by update." I do know that batch 2 is >running in tbe background still. They both take around 3-
4 >hours each. It's hard to schedual them apart enough to >keep them from running at the same time because their run >lengths are so unpredictable. >
>Any ideas?
>My updates are using ado 2.7
>Jason Roozee
>
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ste-level process avoids the use of =cursors and loops. Here, you would use, say, a single UPDATE statement and =apply the calculation across all of the target rows.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee" wrote in =message news:2c0e01c373bb$e9=832330$a401280a@.phx.gbl...Yes, all the tables have a primary key and it's included in the select =list of fields in my ADO record set..."SET LEVEL" processing' Explain.Jason>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Jaso=n Roozee" wrote in message news:2bb701c373b9$29=99dfc0$a401280a@.phx.gbl...>>I run two batches at night time:>>1) A batch that updates =all the records one by one to >calculate special interest on accounts.>>2) The databas optimization batch that cleans =up pages, >free space, rebuilds indexes, etc.>>My batch 1 is =failing with this error: "Key column >information is insufficient or =incorrect. Too many rows >were affected by update." I do know that batch 2 =is >running in tbe background still. They both take around 3-4 >hours each. It's hard to schedual them apart enough to =>keep them from running at the same time because their run >lengths are so unpredictable. >>>Any ideas?>>My =updates are using ado 2.7>>Jason Roozee>
--=_NextPart_000_0166_01C3739A.E6D14CA0--
1) A batch that updates all the records one by one to
calculate special interest on accounts.
2) The databas optimization batch that cleans up pages,
free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column
information is insufficient or incorrect. Too many rows
were affected by update." I do know that batch 2 is
running in tbe background still. They both take around 3-4
hours each. It's hard to schedual them apart enough to
keep them from running at the same time because their run
lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason RoozeeThis is a multi-part message in MIME format.
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
My guess is that batch 1 is using a cursor or raw ADO to do updates on a =table that does not have a primary key. Ensure that all tables have =primary keys and that you make every effort to replace row-by-row =processing with set-level processing.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
I run two batches at night time:
1) A batch that updates all the records one by one to calculate special interest on accounts.
2) The databas optimization batch that cleans up pages, free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column information is insufficient or incorrect. Too many rows were affected by update." I do know that batch 2 is running in tbe background still. They both take around 3-4 hours each. It's hard to schedual them apart enough to keep them from running at the same time because their run lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason Roozee
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
My guess is that batch 1 is using a =cursor or raw ADO to do updates on a table that does not have a primary key. =Ensure that all tables have primary keys and that you make every effort to replace row-by-row processing with set-level processing.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee"
--=_NextPart_000_012C_01C37398.83003D00--|||This is a multi-part message in MIME format.
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ste-level process avoids the use of cursors and loops. Here, you would =use, say, a single UPDATE statement and apply the calculation across all =of the target rows.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2c0e01c373bb$e9832330$a401280a@.phx.gbl...
Yes, all the tables have a primary key and it's included in the select list of fields in my ADO record set...
"SET LEVEL" processing' Explain.
Jason
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jason Roozee" <jason@.camcoinc.net> wrote in message news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
>I run two batches at night time:
>1) A batch that updates all the records one by one to >calculate special interest on accounts.
>2) The databas optimization batch that cleans up pages, >free space, rebuilds indexes, etc.
>My batch 1 is failing with this error: "Key column >information is insufficient or incorrect. Too many rows >were affected by update." I do know that batch 2 is >running in tbe background still. They both take around 3-
4 >hours each. It's hard to schedual them apart enough to >keep them from running at the same time because their run >lengths are so unpredictable. >
>Any ideas?
>My updates are using ado 2.7
>Jason Roozee
>
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ste-level process avoids the use of =cursors and loops. Here, you would use, say, a single UPDATE statement and =apply the calculation across all of the target rows.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee"
--=_NextPart_000_0166_01C3739A.E6D14CA0--
订阅：
博文 (Atom)