Friday, March 30, 2012

Peer to Peer replication and cascading deletes

I was hoping someone could explain how cascading deletes work in Peer to Peer replication on SQL 2005 SP2, the child tables are also being replicated.

Did some of my own testing and here's the results:

Peer to Peer Replication Testing FK Cascading deletes Test 1 1 Create 2 tables with a foreign key dependency, both tables are being replicated. 2 Create FK relationship with the various option below. FK Options Behaviour SQL1 SQL2 Note FOR REPPLICATION Rows are removed as expected All rows are delete as with SQL1, not errors are generated. NOT FOR REPLICATION Rows are removed as expected All rows are delete as with SQL1, not errors are generated. Test 2 1 Create 2 tables with a foreign key dependency, only 1 table is replicated replicated. 2 Create FK relationship with the various option below. FK Options Behaviour SQL1 SQL2 Note FOR REPPLICATION Rows are removed as expected All rows are delete as with SQL1, not errors are generated. NOT FOR REPLICATION Rows are removed as expected The cascading delete failed, only the primary record was removed. This does not break replication as the table that retained the row is not replicated.

No comments:

Post a Comment