February 23, 2009 at 11:02 pm
Hi forum, I am using transactional replication. I found that after the replication is complete, all the foreign keys and data of the destination DB(Table) got washed.Please tell me a way that the keys of table of the destination Db is not affected by replication.
February 24, 2009 at 12:06 am
Right Click Publication Properties --> Articles --> Click on ... on table name --> Snapshot --> Tick on "Include declared referential integrity"
Check for pros and cons of doing this since it may affect your replication. Implement some scripts to monitor replication and monitor for some time!
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 24, 2009 at 3:00 am
Hi Sir, I am using SQL 2005 enterprise edition. I cud not get the option you
mentioned. However there were options in set article properties as "Drop existing object and create new" and "keep existing object unchanged". The default is "Drop..." so i think the keys might be dropped. But when i use the second option i.e. keep existing object unchanged, i face a lot of error like
"the schema script tablename2.trg could not be propagated". I am new to replication please help.
February 24, 2009 at 11:50 am
ritesh (2/24/2009)
Hi Sir, I am using SQL 2005 enterprise edition. I cud not get the option youmentioned. However there were options in set article properties as "Drop existing object and create new" and "keep existing object unchanged". The default is "Drop..." so i think the keys might be dropped. But when i use the second option i.e. keep existing object unchanged, i face a lot of error like
"the schema script tablename2.trg could not be propagated". I am new to replication please help.
The option you are looking for is "Copy foreign key constraints" under the table article properties.
The triggers if you are using them must be marked as NOT FOR REPLICATION (NFR)
In addition if you are using "multiple streams" your foreign keys must also be marked as NFR.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply