3

We are experiencing an issue when creating a new foreign key to an existing table, which is being replicated to another SQL Server 2008. More specifically, we are adding a new foreign key to this replicated table Foo which references a non-replicated table Bar:

ALTER TABLE Foo WITH CHECK ADD CONSTRAINT [FK_Foo_Bar2] FOREIGN KEY([BarId2]) REFERENCES Bar ([BarId])

After executing this script on the main database, the merge replication seems to send it to the subscriber (and a new line is added to the sysmergeschemachange table). And this is where things go wrong: Since Bar is not being replicated, the constraint/fk cannot be added on the subscriber, and thus replication stops completely, because the schema change script fails. This has caused us to re-setup the whole replication several times now!

I wonder why SQL Server 2008 would send this script to the subscriber in the first place? After all, other relations to non-replicated tables had (correctly) not been added in the initial snapshot either.

Improve this question

1 Answer 1

Reset to default
2
+50

So it did pretty much what it is described in Make Schema Changes on Publication Databases:

If a publication is set to allow the propagation of schema changes, schema changes are propagated regardless of how the related schema option is set for an article in the publication. For example, if you select not to replicate foreign key constraints for a table article, but then issue an ALTER TABLE command that adds a foreign key to the table at the Publisher, the foreign key is added to the table at the Subscriber. To prevent this, disable the propagation of schema changes before issuing the ALTER TABLE command.

To boot, your exact case is give as an example :) Also, the solution is given there: disable schema changes replication before issuing the ALTER TABLE.

As to why it behaves this way, I would say that a system that would make 'smart' decision what schema changes to replicate and what not would be not only very hard to design, but also very error prone and would cause a lot of surprises and break the principle of least astonishment a lot of times. The gist of it is the relations between objects that are published and objects that are not are often ambiguous or hard to detect (think why is the subject of dependency tracking so vast...). And even when detected, the semantics of a change that affects both a published article and a non-published object are ambiguous to say the least: allow the change only locally? Block the change? I can assure you that whichever answer you venture forward, someone will complain it breaks his case... because the answer is dependent on specific soft business rules, not hard language rules.

Improve this answer
3
  • I am a bit bummed out that I overlooked this part of the documentation. :) Anyways, your answer explains the behaviour perfectly and even gives some nice insight on why Microsoft has implemented it this way, thank you! I guess I had hoped that the merge replication could be as good with foreign keys in the replication process as it is when building the initial snapshot, which apparently does not contain fks to non-replicated tables. :) Commented Jun 8, 2012 at 11:16
  • Don't take my comments as 'insight', is more of a speculative educated guess. Commented Jun 8, 2012 at 11:18
  • What about if you then add the new table to replication? I still get errors even though the new article has been added and a new snapshot created, and I'd prefer to not have to reinitialize. Commented Aug 11, 2013 at 21:14

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.