Adding Articles to and Dropping Articles from Existing Publications

While checking on the forum found many questions about how to add/drop articles from existing replication. so though of blogging on it.

Everyone should follow this (must read) which is the base/reference for this blog .

>>Snapshot/Transactional Replicaiton:-

Adding Articles:-

We can add article(s) into snapshot/transaction type replication at any time irrespective subscriber created (before and after)  using Management studio, T-Sql script or RMO.

Management studio: just add the article(s) (check box) for publisher property -articles.

T-Sql : sp_addarticle stored procedure.

e.g. sp_addarticle ‘publisherDB’,’ArtileName’,’sourceObject’,’DestinationObject’

RMO:( see here).

WE MUST HAVE TO CREATE NEW SNAPSHOT FOR THE PUBLICATION AND RUN THE DISTRIBUTION JOB TO CREATE SCHEMA AT  THE SUBSRIBER-this will not reinitialize the entire publication.

Dropping Articles:-

If you have created a subscription, You cannot drop the article if you have created the subscriber. you have to drop the subscriber and then recreate and synchronize it.

drop subsriber you can use this, following commands:

at publisher:

sp_dropsubscription

eg. EXEC sp_dropsubscription @publication =N'<Publication name>’, @subscriber = N'<Subscriber server name>’, @article = N’all’, @destination_db = N'<Destination database name>’

at subscriber:

sp_droppullsubscription

eg. EXEC sp_droppullsubscription @publisher = N'<Publisher server name>’, @publisher_db = N'<Publisher database name>’, @publication = N'<Publication name>’

and then drop the article from publisher replication using

Management studio-> replication->Articles ->Unchecked article’s

T-Sql: sp_droparticles

e.g. EXEC sp_droparticle   @publication = ‘PublicationDB’,   @article = ‘article’,  @force_invalidate_snapshot = 1

RMO:(see Here)

then reconfigure the replication and syncronize it.

 Again for detail information I would highly recommend to follow/read  here (same as above link) this includes merge replication adding and dropping articles as well.

Hope this help to someone as it helped me.

Thanx.

Vinay

Advertisements
This entry was posted in Add/remove, Replication and tagged . 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