Delete Article from Transactional Replication in SQL Server 2012

Delete Article from Transactional Replication in SQL Server 2012

I have already demonstrated how to established snapshot replication as well as transactional replication in SQL Server 2012. Apart from that we have already seen how to remove replication and how to add new article (database objects like table, views, functions, stored procedures etc.) in transactional replication along with few other tips and tricks of replication. Now it is time to remove some of the unwanted articles from transactional replication. I will use the same transactional replication which I have created in my earlier article.

Removing article from replication and removing objects from replicated (subscription) database is two different things. Replication will stop replicating the changes in schema/data from publisher to subscriber once you remove article from replication but it will not remove schema/data from the subscriber database, you have to do it manually after removing object from replication.

Let us now proceed towards removing article from transactional replication.

1.)  open the replication option under your SSMS and move to the publication, right click on publication and click on “Properties” from the popup menu.

2.) Publication Properties dialog box, go to “Articles” option from the menu in left hand side and deselect the checkbox of article you want to remove from replication. I have remove “Sales.Customer” table and click on “Ok” button.

3.) Once you will click on OK button from Publication Properties dialog box above, replication will stop populating new data change to the “Sales.Customer” table but now I want to update my snapshot of replication so right click on “Replication” option and click on “Launch Replication Monitor” option from the popup menu.

4.) From the replication monitor, you can find “Agent” tab under your publication, you have to right click on “Snapshot Agent” and click on “Start Agent” from popup menu. This action will update snapshot which takes time based on the size of database.

All these four steps will successfully remove articles from replication now if you wish; you can manually go to subscriber database and drop objects which you have removed from replication.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

3 thoughts on “Delete Article from Transactional Replication in SQL Server 2012”

Comments are closed.