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
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.