Remove Replication from SQL Server 2012

Remove Replication from SQL Server 2012 (Part 2)

Removing replication needs some steps to be performed in proper sequence otherwise either replication will not be removed or not completely removed which leads some issues in future especially log file size related issues.

For removing replication, it is mandatory that we have one replication setup to remove. You can consider this as pre-requisite of this article. If you don’t have any replication setup to remove, you can set up one replication by following step given in my earlier article “Understanding replication with Implementation of Snapshot Replication in SQL Server 2012 (Part 1)

If you are ready with replication, let us move ahead. Follow the steps given here to remove the replication.

1.)    Delete the subscriber from Replication -> Local Publication -> “Subscriber Name”. look at following screen capture for more detail

2.)    Delete the publication from Replication -> Local Publications -> “Publication Name”

3.)    Delete the distribution database by right click on Replication and click on “Disable publishing and Distribution …”, follow the screen capture for more detail:

Once you are done with removing of Publication, Distributor and Subscriber, you can confirm whether distributor database is there or not manually. For more confirmation, you can execute the following command in the SQL Server instance where you have had your distribution database.

[sourcecode language=”sql”]exec master..sp_dropdistributor[/sourcecode]

As soon as you execute above given command in the instance where you have had your distributor database, you will come across following error as it can’t find distributor database because we have already removed it.

[sourcecode]
Msg 21043, Level 16, State 1, Procedure sp_dropdistributor, Line 50
The Distributor is not installed.
[/sourcecode]

Now let us execute following SELECT statement in principal database to confirm whether any article still having replication status or not.

[sourcecode language=”sql”]
SELECT
*
FROM
sys.sysobjects
WHERE
replinfo=1
[/sourcecode]

You should get no result from above SELECT statement as we have removed replication completely.

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

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

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.