Background:
This can be useful in the case you want to add a table to a publication already containing tables with many rows and you don’t want those to be re-initialized when you create the snapshot.
Setup / Code:
1. Enable replication on Publisher.
2. Enable distribution on Publisher.
3. Setup new Publication
4. Add your new Subscriber
5. Verify replication is running.
6. When it comes time to add a new Article, set these options on your Publisher:
Exec sp_changepublication
@publication = 'pub name',
@property = 'allow_anonymous',
@value ='false'
Go
Exec sp_changepublication
@publication = 'pub name',
@property = 'immediate_sync',
@value ='false'
Go
When executed you should get the output:
”The publication was updated successfully”
7. Go back to your publication and add the new Article(s).
8. Run the Snapshot Agent and you will now see a snapshot of only the newly added Article(s) is generated.
9. If you want future snapshots to be a full copy of Articles, you need to reset the publication settings with the following:
Exec sp_changepublication
@publication = 'pub name',
@property = 'immediate_sync',
@value ='true'
Go
Exec sp_changepublication
@publication = 'pub name',
@property = 'allow_anonymous',
@value ='true'
Go
Result:
Information on Terms:
- Allow Anonymous – This has to do with the subscriber and is used typically in Merge Replication where the subscriber and the publisher are not always connected. This allows you to setup the snapshot without validating the articles against the publisher.
- Immediate_Sync – determines whether the entire set of synchronization files has to be available at the distributor for every subscriber re-initialization or if set false, then only articles not yet sent to the subscriber need to be present at the distributor. They are made available for the period of time defined by the distributor retention settings.