cascade delete and merge rep

Hey everyone,
I'm currently running sql 2k sp3 and win2k3 with all of
the latest patches. I'm running merge replication in a
clustered environment. My question is in regards to the
cascade delete option for foreign keys. Whenever a delete
transaction is executed against the parent table with a
foreign key to a child table and the cascade delete option
enabled, merge replication refuses to replicate the
changes due to the merge profile (ChangesPerHistory =
2000, DownloadGenerationsPerBatch = 2000). If I change
the profile so the changes and generations are set to 10,
then it works, but it's painfully slow. Also, just
executing the delete takes forever. Deleting just 10
records from the parent table using the clustered
index/primary key column in the where clause takes almost
1 minute. Without the cascade option enabled, it takes
less than 1 second. Any ideas on why the cascade delete
option isn't working? If it helps, the two tables above
are fairly large, over 120 million records between the two
of them. Thanks.
What you should do is disable the enforcement of constraint for replication.
What this will mean is that the replication processs can add children
without parents.
What happens with merge replication is sometimes a child delete/update is
replicated before the corresponding parent child delete/update. By
increasing the generationsper batch hopefully the parent and child
modifications will happen in the same batch. If the child record
delete/update occurs before the corresponding parent delete/update the child
delete/update is rolled back and goes into a retry queue for that batch, and
when all the transactions are processed for that batch the records in the
retry queue are tried again. This time hopefully the child record makes it
in as the parent exists.
When you disable this constraint for replication, children can be added
without parents (assuming the parent will be replicated later).
Let me know how this works for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:28c501c4a1a9$7327f540$a601280a@.phx.gbl...
> Hey everyone,
> I'm currently running sql 2k sp3 and win2k3 with all of
> the latest patches. I'm running merge replication in a
> clustered environment. My question is in regards to the
> cascade delete option for foreign keys. Whenever a delete
> transaction is executed against the parent table with a
> foreign key to a child table and the cascade delete option
> enabled, merge replication refuses to replicate the
> changes due to the merge profile (ChangesPerHistory =
> 2000, DownloadGenerationsPerBatch = 2000). If I change
> the profile so the changes and generations are set to 10,
> then it works, but it's painfully slow. Also, just
> executing the delete takes forever. Deleting just 10
> records from the parent table using the clustered
> index/primary key column in the where clause takes almost
> 1 minute. Without the cascade option enabled, it takes
> less than 1 second. Any ideas on why the cascade delete
> option isn't working? If it helps, the two tables above
> are fairly large, over 120 million records between the two
> of them. Thanks.
|||Leon,
We are currently improving this area for SQL 2005. Would you be willing to
work offline with me on reproducing your current problem?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OemHAtboEHA.596@.TK2MSFTNGP11.phx.gbl...
> What you should do is disable the enforcement of constraint for
> replication.
> What this will mean is that the replication processs can add children
> without parents.
> What happens with merge replication is sometimes a child delete/update is
> replicated before the corresponding parent child delete/update. By
> increasing the generationsper batch hopefully the parent and child
> modifications will happen in the same batch. If the child record
> delete/update occurs before the corresponding parent delete/update the
> child
> delete/update is rolled back and goes into a retry queue for that batch,
> and
> when all the transactions are processed for that batch the records in the
> retry queue are tried again. This time hopefully the child record makes it
> in as the parent exists.
> When you disable this constraint for replication, children can be added
> without parents (assuming the parent will be replicated later).
> Let me know how this works for you.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Leon" <anonymous@.discussions.microsoft.com> wrote in message
> news:28c501c4a1a9$7327f540$a601280a@.phx.gbl...
>
|||Philip,
Absolutely. I'll send you an email from my work account
so we can discuss.
Hilary,
I'll see if that works. Thanks for your help.

>--Original Message--
>Leon,
>We are currently improving this area for SQL 2005. Would
you be willing to
>work offline with me on reproducing your current problem?
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
message[vbcol=seagreen]
>news:OemHAtboEHA.596@.TK2MSFTNGP11.phx.gbl...
constraint for[vbcol=seagreen]
can add children[vbcol=seagreen]
child delete/update is[vbcol=seagreen]
delete/update. By[vbcol=seagreen]
parent and child[vbcol=seagreen]
child record[vbcol=seagreen]
delete/update the[vbcol=seagreen]
queue for that batch,[vbcol=seagreen]
the records in the[vbcol=seagreen]
child record makes it[vbcol=seagreen]
children can be added[vbcol=seagreen]
later).[vbcol=seagreen]
message[vbcol=seagreen]
the[vbcol=seagreen]
delete[vbcol=seagreen]
option[vbcol=seagreen]
10,[vbcol=seagreen]
almost[vbcol=seagreen]
delete[vbcol=seagreen]
above[vbcol=seagreen]
two
>
>.
>