Changing Article Properties without a new Snapshot

In our replication environment, the subscriber is initially set up with an snapshot of the publisher database. However, after that, the subscriber and publisher are different and we can never re-initialize from a snapshot again (we purge data on the publisher to reduce the database size but do not purge the same data on the subscriber; we do this by stubbing out the stored procedures on the subscriber that purge data on the publisher).

If an article is added or dropped from the publication, using snapshot and synchronize, just these changes are propagated to the publisher (without an entire new snapshot).

However, if an Article Property is changed (change SCALL to MCALL under Statement Delivery options for the UPDATE statement), the interface REQUIRES an entire new snapshot. Is there any way I can avoid the new Snapshot? It overwrites the subscriber database and this cannot happen!

Linda

Adding/dropping article(s) from an existing publication requires generates a new snapshot in general. This is to make sure the data convergence.

Do you mind sharing the business purpose - 'avoiding the new Snapshot' and 'can not overwrite the subscriber database'?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Hi Linda,

Yes, changing CALL format will require a new snapshot.

But there is another way which might do what do you need (although requires more steps and a little intrusive). You can drop the subscription and publication. It will delete the replication, but not the data at subscriber. Then you can create the publication and subscription again. But when you create the subscription, you can choose to initialize subscription without snapshot. BOL has instruction (http://msdn2.microsoft.com/en-us/library/ms151705.aspx) on how to do it. Just follow the instructions under "Initializing a subscription with an alternative method".

Thanks,

Peng

|||

Thanks again, Peng. I have used this method in the past (breaking replication and restoring without a snapshot). I will test it for this scenario and let you know the results.

Linda

|||

Peng,

This works for changing article properties. Thanks!

Under what conditions will SQL2005 Replication generate an entire new snapshot?

Our configuration will not allow the subscription database to be re-initialized with a snapshot. The snapshot from the publisher will only be used for the initial subscription.

I need to identify all cases when a new snapshot will be created and use the alternative method of breaking replication, make the changes and then re-add the subscription without a snapshot.

Examples:

Adding or dropping a table: the snapshot agent just applies changes from the 1 item I changed without breaking replication.

Dropping a view: I dropped a view and the snapshot agent recreated the entire snaphot. Why?

How can I tell if my changes to replication are going to cause a new snapshot to be generated?

Linda