Stored Procedure schema changes are not being replicated when using ”initialize with backup”
SQL Replication allows you to replicate user-defined Stored Procedures code to subscribers similar to replicating tables. When the sp is modified, the new schema/script is transferred to the subscriber. This ensures all subscribers have the most recent version of the SP.
Transactional Replication also allows you to setup a subscriber using a backup of the Publisher. This option is called ”initialize with backup”. We encountered a problem in SQL 2005/2008 replicating a stored procedure schema change if the subscriber was initialized using the ”initialize with backup” feature. The Log Reader would not distributed the SP schema change to the Distribution database and therefore not update the subscribers. We’re still investigating the root cause but wanted to provide these ways to avoid the problem.
sp_addsubscription . . . @sync_type = N’initialize with backup’,
Solutions
——————-
1.a) Create 1 subscriber (even if on a same publisher) using Snapshot Replication
1.b) Drop the “snapshot” subscriber, the SP changes are correctly replicated.
2) Create ALL subscribers using Replication Snapshot or Backup/Restore @sync_type = N’replication support only’.
3) Don’t Replicate user-defined SP schema modifications. Instead using Scripting to SSIS packages to move the modified SP code.
We're successfully using "initialize with backup" and seeing our stored procedure DDL changes replicate. If this bug has been fixed in a subsequent CU, you should update this blog posting — we held off trying "initialize with backup" for some time because we had seen this post and thought it wouldn't work, but we found that the "replication support only" option never got the LSNs right, so we always had row-not-found errors.
I'll second a desire to see an update on this issue. We use "initialize with backup" and am having trouble with DDL changes. I suspect a creation a snapshot agent is what enables the replication of the DDL changes but any further info / shortcuts would be great.