October 4, 2009 at 12:27 pm
I have encountered an issue with foreign key options is_not_for_replication and is_not_trusted (viewed in sys.foreign_keys) are being changed from 0 to 1. After each schema update I drop and recreate all foreign keys. The ALTER statement to create foreign keys doesn't specify the is_not_for_replication option. It simply looks like: ALTER TABLE table1 ADD FOREIGN KEY (column1) REFERENCES table2.
After the foreign keys have been created the below query doesn't return any results.
select * from sys.foreign_keys
where is_not_for_replication = 1
or is_not_trusted = 1
Something is then going in and setting both is_not_for_replication and is_not_trusted to 1. The modify_date in sys.foreign_keys for all 1288 foreign keys returned by the above query is within 1 second of each other.
I'm using Transactional Replication, without immediate updating subscribers, the subscriber already has the schema and data so I don't use the initial snapshot to initialize the data or the snapshot agent (I disable it). I've scoured all the jobs that run against the database and have come back with nothing. I'm guessing that replication could be behind this but I really am grasping at straws.
This issue was uncovered when a delete statement was executed at the publishing database and replication pushed the delete to the subscriber. Foreign keys at the subscriber did not check the replicated delete and allowed parent data to be deleted while there was still child data.
Any help or info would be appreciated!
October 5, 2009 at 12:08 pm
After some testing we concluded that this is an ordering issue.
If replication is configured and the distribution agent is initially pushed to the subscriber before foreign keys are created at the subscriber,
THEN foreign keys applied to the subscriber,
the foreign keys are created correctly and never modified by replication.
If foreign keys are applied to the subscriber
then the distribution agent is initially pushed to the subscriber,
the distribution agent sets the options is_not_for_replication = 1 and is_not_trusted = 1.
This seems to be done only the first time the distribution agent runs.
This is done even though we don’t initialize the schema or the data at the subscriber and the publication is created with replicate_ddl = 0.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply