Replication option

When a new relationship between tables there is an option 'Enforce
Relationship for Replication'
What does it technically mean?
Thank you,
SamuelIf you uncheck this box when you have cascading updates and/or deletes the
initial DML will be replicated along with the cascading update and delete.
If on the subscriber side you also have cascading updates and deletes the
replicated cascade insert/update will fail.
Consider this with this option checked. You delete a parent. The cascading
delete deletes the 5 child rows. The parent delete is replicated, and on the
subscriber the parent row is deleted and the cascading delete deletes the 5
child rows. Everyone is happy and its a beautiful thing.
With the option unchecked. You delete a parent, The cascading delete deletes
5 rows. The parent delete is replicated and the 5 child rows are also
replicated. The parent row is deleted from the subcriber, and the cascading
delete deletes the 5 child rows. Then the replicated cascading child deletes
come replicating to the subscriber and the rows they are attempting to
delete aren't there, and so to maintain the transactional boundary, the 5
child deletes and the 1 parent is rolled back and you have a message about
foreign key violation. Everyone is unhappy and its not a beautiful thing
cause the error message complains about foreign key violations and your
parent and child tables look fine.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:eqPE06n5GHA.4616@.TK2MSFTNGP05.phx.gbl...
> When a new relationship between tables there is an option 'Enforce
> Relationship for Replication'
> What does it technically mean?
> Thank you,
> Samuel
>|||Thank you for your help,
Just to make sure I understood, eventually in either case all entries will
be replicated except that it will happen in the second round
Setting the option to True means that the server expects that in the first
place
Now to my problem
I set a new Transactional Replication and during the snapshot phase the
replication failed because of foreign key constraint
1. The constraints existed in the publisher (meaning that there was no
problem in the publisher database) as well how come there will be a problem
in the subscriber
2. When I remove this option everything is fine. Why and how can I sort out
this situation
Thank you,
Samuel
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZ5ap3o5GHA.940@.TK2MSFTNGP03.phx.gbl...
> If you uncheck this box when you have cascading updates and/or deletes the
> initial DML will be replicated along with the cascading update and delete.
> If on the subscriber side you also have cascading updates and deletes the
> replicated cascade insert/update will fail.
> Consider this with this option checked. You delete a parent. The cascading
> delete deletes the 5 child rows. The parent delete is replicated, and on
> the subscriber the parent row is deleted and the cascading delete deletes
> the 5 child rows. Everyone is happy and its a beautiful thing.
> With the option unchecked. You delete a parent, The cascading delete
> deletes 5 rows. The parent delete is replicated and the 5 child rows are
> also replicated. The parent row is deleted from the subcriber, and the
> cascading delete deletes the 5 child rows. Then the replicated cascading
> child deletes come replicating to the subscriber and the rows they are
> attempting to delete aren't there, and so to maintain the transactional
> boundary, the 5 child deletes and the 1 parent is rolled back and you have
> a message about foreign key violation. Everyone is unhappy and its not a
> beautiful thing cause the error message complains about foreign key
> violations and your parent and child tables look fine.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:eqPE06n5GHA.4616@.TK2MSFTNGP05.phx.gbl...
>> When a new relationship between tables there is an option 'Enforce
>> Relationship for Replication'
>> What does it technically mean?
>> Thank you,
>> Samuel
>|||Yes, accept the defaults. Leave this option checked.
The problem with the snapshot is a different one. Right click on your
publication, select properties, article properties, drill down on the three
ellipses beside your table, click the snapshot tab, and uncheck include DRI.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OX0lUXs5GHA.3808@.TK2MSFTNGP06.phx.gbl...
> Thank you for your help,
> Just to make sure I understood, eventually in either case all entries will
> be replicated except that it will happen in the second round
> Setting the option to True means that the server expects that in the first
> place
> Now to my problem
> I set a new Transactional Replication and during the snapshot phase the
> replication failed because of foreign key constraint
> 1. The constraints existed in the publisher (meaning that there was no
> problem in the publisher database) as well how come there will be a
> problem in the subscriber
> 2. When I remove this option everything is fine. Why and how can I sort
> out this situation
> Thank you,
> Samuel
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eZ5ap3o5GHA.940@.TK2MSFTNGP03.phx.gbl...
>> If you uncheck this box when you have cascading updates and/or deletes
>> the initial DML will be replicated along with the cascading update and
>> delete. If on the subscriber side you also have cascading updates and
>> deletes the replicated cascade insert/update will fail.
>> Consider this with this option checked. You delete a parent. The
>> cascading delete deletes the 5 child rows. The parent delete is
>> replicated, and on the subscriber the parent row is deleted and the
>> cascading delete deletes the 5 child rows. Everyone is happy and its a
>> beautiful thing.
>> With the option unchecked. You delete a parent, The cascading delete
>> deletes 5 rows. The parent delete is replicated and the 5 child rows are
>> also replicated. The parent row is deleted from the subcriber, and the
>> cascading delete deletes the 5 child rows. Then the replicated cascading
>> child deletes come replicating to the subscriber and the rows they are
>> attempting to delete aren't there, and so to maintain the transactional
>> boundary, the 5 child deletes and the 1 parent is rolled back and you
>> have a message about foreign key violation. Everyone is unhappy and its
>> not a beautiful thing cause the error message complains about foreign key
>> violations and your parent and child tables look fine.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:eqPE06n5GHA.4616@.TK2MSFTNGP05.phx.gbl...
>> When a new relationship between tables there is an option 'Enforce
>> Relationship for Replication'
>> What does it technically mean?
>> Thank you,
>> Samuel
>>
>|||I assume you refer to the Declared Referencial Integrity
Unfortunately, the box is checked and disabled
Samuel
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23QaDsBt5GHA.2264@.TK2MSFTNGP02.phx.gbl...
> Yes, accept the defaults. Leave this option checked.
> The problem with the snapshot is a different one. Right click on your
> publication, select properties, article properties, drill down on the
> three ellipses beside your table, click the snapshot tab, and uncheck
> include DRI.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OX0lUXs5GHA.3808@.TK2MSFTNGP06.phx.gbl...
>> Thank you for your help,
>> Just to make sure I understood, eventually in either case all entries
>> will be replicated except that it will happen in the second round
>> Setting the option to True means that the server expects that in the
>> first place
>> Now to my problem
>> I set a new Transactional Replication and during the snapshot phase the
>> replication failed because of foreign key constraint
>> 1. The constraints existed in the publisher (meaning that there was no
>> problem in the publisher database) as well how come there will be a
>> problem in the subscriber
>> 2. When I remove this option everything is fine. Why and how can I sort
>> out this situation
>> Thank you,
>> Samuel
>> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>> news:eZ5ap3o5GHA.940@.TK2MSFTNGP03.phx.gbl...
>> If you uncheck this box when you have cascading updates and/or deletes
>> the initial DML will be replicated along with the cascading update and
>> delete. If on the subscriber side you also have cascading updates and
>> deletes the replicated cascade insert/update will fail.
>> Consider this with this option checked. You delete a parent. The
>> cascading delete deletes the 5 child rows. The parent delete is
>> replicated, and on the subscriber the parent row is deleted and the
>> cascading delete deletes the 5 child rows. Everyone is happy and its a
>> beautiful thing.
>> With the option unchecked. You delete a parent, The cascading delete
>> deletes 5 rows. The parent delete is replicated and the 5 child rows are
>> also replicated. The parent row is deleted from the subcriber, and the
>> cascading delete deletes the 5 child rows. Then the replicated cascading
>> child deletes come replicating to the subscriber and the rows they are
>> attempting to delete aren't there, and so to maintain the transactional
>> boundary, the 5 child deletes and the 1 parent is rolled back and you
>> have a message about foreign key violation. Everyone is unhappy and its
>> not a beautiful thing cause the error message complains about foreign
>> key violations and your parent and child tables look fine.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:eqPE06n5GHA.4616@.TK2MSFTNGP05.phx.gbl...
>> When a new relationship between tables there is an option 'Enforce
>> Relationship for Replication'
>> What does it technically mean?
>> Thank you,
>> Samuel
>>
>>
>