Schema Changes not Replicated when only VIEWS and Stored Procedures are published

Content Provided by:
     Sateesh Yele
     Microsoft SQL Server Support

This problem applies to SQL Server 2005 and has been resolved in SQL Server 2008 and SQL Server 2008 R2

Symptom

Consider the following scenario.

Publisher is SQL Server 2005. Merge publication has only views, stored procedures and triggers getting replicated to the subscribers. There are no tables in the publication. When the Merge agent runs after the snapshot is created, these objects are created at the subscriber but any subsequent schema changes to views, procedures and triggers are not replicated to the subscribers. The Merge agent completes successfully and no error is throw.

Cause

Merge replication uses 2 internal tables to track the articles that are getting replicated.

Sysmergearticles: Tracks tables that are merge replicated
Sysmergeschemaarticles: Tracks schema-only articles for merge replication

Any schema changes to the articles is recorded in sysmergeschemachange table on the publisher database. The logic inside one of the internal procedures (sp_MSmerge_alterschemaonly) will query the sysmergearticles and looks for lightweight column for the rows. When there are no tables getting replicated in the same publication,  @islightweight value is set to null and hence the changes to view , procedures and triggers are not recorded in sysmergeschemachange and are not replicated to the subscribers.

Resolution

If the schema changes for views, procedures and triggers are needed to be replicated to the subscribers, include at least one table in the same publication.

As an alternative, the objects can be scripted, then script transferred and executed on the subscriber using an SSIS package.

posted by: Chris Skorlinski, Microsoft SQL Server Escalation Services