0

SQL Server 2012, 2014, 2016 transactional replication

  1. Publication is created. (copy Foreign Keys is false, the default)
  2. Subscription is created.
  3. Snapshot and sync.
  4. Turn off synchronization.
  5. Upgrade the publication database.
  6. Upgrade the subscriber database for tables affected by modified views.
  7. Set the snapshot to only gather information for changes.
  8. Restart sync.

There is now an error at the subscriber because the two new columns exist and the snapshot is trying to create them but with foreign keys.

Typically it hasn't cared but now it seems to because of the FK creation it wants to do. If I manually delete the two new columns the sync will now create them again but with FKs.

The same operation happens for other new fields but we've never run into this issue before.

Looking to understand why FKs are being sent and if there is a workaround or setting.

Dale K
  • 25,246
  • 15
  • 42
  • 71
JeffM23
  • 3
  • 2
  • Have you tried using the NOT FOR REPLICATION option where the FKs are declared in the source table? – Greg Low Feb 07 '20 at 03:18
  • I have not. I thought SQL put that condition on the subscriber tables automatically. Did know it could be set. I will try that. – JeffM23 Feb 07 '20 at 12:20
  • No, didn't work. FKs still created but they do show Not For Replication. – JeffM23 Feb 07 '20 at 14:12
  • For a workaround I'm dropping the columns after the views are created. Then the sync adds them back (with FKs :( ). I then have a manual script to drop all FKs in the database. That's not pretty but it's working. I'd really like to find out what this is occurring. – JeffM23 Feb 10 '20 at 15:55

0 Answers0