How to add new articles to existing Transactional Replication

Adding new articles is simple but following steps will avoid publishing snapshot of old articles.

1. Change allow_anonymous and immediate_sync to FALSE.

sp_changepublication @publication = 'Publication_Name', @property='allow_anonymous', @value='FALSE'
sp_changepublication @publication = 'Publication_Name', @property='immediate_sync', @value='FALSE'

2. Include new articles
..a. SSMS -> Server/Instance -> Replication -> Local Publications
..b. Right click and select Properties of the Publication -> Select Articles -> Uncheck ‘Show only checked articles in the list’
..c. Add new articles to be included into the publication and Click OK
.. ..i. Note: Only Tables with Primary key can be included into the publication
3. Initiate Snapshot Agent
..a. Right click on Publication, select View Snapshot Agent Status
..b. Click on Start button to initiate Snapshot push of new articles added
4. Revert back the configuration changes

sp_changepublication @publication = 'Publication_Name', @property='immediate_sync', @value='TRUE'
sp_changepublication @publication = 'Publication_Name', @property='allow_anonymous', @value='TRUE'

Thanks to Rammohan who shared this information!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s