New foreign key and merge replication

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.

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.

StackExchange Database Administrators Q#18502, answer score: 2