INF: How to Manually Remove Replication

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

This article explains the steps to manually remove (uninstall) replication, using stored procedures and Transact-SQL commands.

The information in this article may be useful in situations where you suspect that some elements of replication have not been removed correctly, and you want to manually remove those elements. If replication is not removed correctly, the transaction log of a previously published database may fill up because the log cannot be truncated. (See step 3 below for details on how to diagnose this possibility.)

This article may also be useful if you need to create a script or programmatic procedure to uninstall replication.

The following list is a summary of the steps necessary to manually remove replication. Details for each step are provided in the "MORE INFORMATION" section of this article.

1. Drop all subscriptions.

2. Drop all articles and publications.

3. Clear the transaction log of any "undistributed" replicated transactions.

4. Remove the database's published and subscribed status.

5. Delete unneeded remote servers and remote logins.

6. Remove a server's publisher, subscriber, and distributor status.

7. Delete any remaining replication tasks.

8. Delete the .sch and .tmp files in the Repldata directory.

9. Drop the distribution database.

10. Reset the MSlast_job_info table.

11. Verify the replication registry entry.

In SQL Server 6.5, you can use SQL Enterprise Manager to remove replication by clicking Replication Configuration/Uninstall Publishing on the Server menu. For more details, see "Uninstalling Replication" in Part 3 of the What's New In SQL Server 6.5 book.

SQL Server 6.0 does not have a replication uninstall option, but you can do the equivalent of steps 1 through 7 using SQL Enterprise Manager. For SQL Server 6.0, you will still need to do steps 8 through 11 manually. For more details, see "Stopping Replication" in Chapter 14 of the SQL Server 6.0 Administrator's Companion.

Note that you should not use the dump and load mechanism to transfer a database with publications and subscriptions to a different server. (In this case, a "different server" means a server other than the one on which the database was originally published.) In many cases, the procedures in this article will not be sufficient to remove replication from a database that has been dumped from one server and loaded onto another server.

If you must move a published database to a different server, you should consider using Transfer Manager or the bulk copy program (BCP). You can safely use the dump and load mechanism to transfer a published database to a different server only if you drop all subscriptions and all publications in the published database before dumping the database.

MORE INFORMATION

Below are the steps to manually remove (uninstall) replication are described in detail. See the SQL Server Books Online for more information on the commands and stored procedures discussed below.

Step 1 - Drop All Subscriptions

You can run sp_helpsubscription in the published database to determine whether or not there are any subscriptions.

Run the command below to drop all subscriptions to all publications within a published database. This command must be run in each database that is published and has subscriptions. Running this command also deletes the distribution tasks associated with the subscriptions that are dropped.

   sp_dropsubscription 'all', 'all', 'all'

• There is a distribution database installed.

  -and-

• The publishing server has correct remote server information for all subscribing servers. Remote server information is defined automatically when you configure replication. You can verify the remote server information by running sp_helpserver.

Step 2 - Drop All Articles and Publications

You can run sp_helppublication in the published database to determine whether or not there are any publications.

You can run "select * from sysarticles" in the published database to determine whether or not there are any articles.

Run the command below to drop all articles and subscriptions within a published database. This command must be run in each database that is published and has publications. Running this command will also delete the synchronization task associated with the publications that are dropped.

   sp_droppublication 'all'

         Transactions

You will not be able to truncate the transaction log as long as there are any replicated transactions that have not been distributed. An "undistributed" replicated transaction is a transaction that has been marked for replication in the transaction log of the published database, but has not been "distributed" by the log reader task.

You can see if there are any "undistributed" replicated transactions in a published database by running the following command:

   dbcc opentran(<published_database_name>) with tableresults

If the above command does return the oldest distributed and oldest non- distributed row IDs, and if these row IDs are not the same, you have one or more undistributed replicated transactions in that database. If the row IDs are the same, you do not have any undistributed replicated transactions in the database. For more information, see the DBCC statement in the Transact- SQL Reference book.

If you have undistributed replicated transactions, run the following command to mark all replicated transactions as "distributed" so that you can truncate the log:

   sp_repldone 0, 0, null, 0, 0, 1

The sp_repldone command can only be run if the database is marked as published. If necessary, you can temporarily mark a database as published by running the following command:

   sp_dboption <database_name>, pub, true

   sp_dboption <database_name>, pub, false

Step 4 - Remove the Database's "Published" and "Subscribed" Status

Run sp_helpdb to verify which databases have a status of "published" or "subscribed."

Run the following command to remove the "published" status from a database. Running this command also deletes the log reader task for that database.

   sp_dboption <database_name>, published, false

   sp_dboption <database_name>, subscribed, false

Step 5 - Delete Unneeded Remote Servers and Remote Logins

Replication automatically defines remote servers and remote logins when you install replication. The remote servers and remote logins are necessary to run remote procedure calls. You may delete the remote servers and remote logins if they are not required for other SQL Server applications.

NOTE: You should not drop the local server.

The local server is the one that has server ID 0 when you run sp_helpserver. For more information on remote servers and remote users, see Chapter 10 in the SQL Server Administrator's Companion.

You can run "sp_helpserver" to see a list of remote servers. You can run "sp_helpremotelogin" to see a list of the remote logins. Run the following command to drop a remote login:

   sp_droplogin remoteserver_name [, loginame [, remotename]]

   sp_dropserver server_name [, droplogins]

Step 6 - Remove a Server's Publisher, Subscriber, and Distributor Status

Run sp_helpserver to verify the status of the servers. Then run the following command to remove a server's publisher ("pub") status:

   sp_serveroption <server_name>, pub, false

   sp_serveroption <server_name>, dpub, false

   sp_serveroption <server_name>, sub, false

   sp_serveroption <server_name>, dist, false

Step 7 - Delete Any Remaining Replication Tasks

Open the Manage Scheduled Tasks window in SQL Enterprise Manager by double-clicking the SQL Executive icon. Delete any tasks that have a type of "Logreader," "Distribution," or "Sync." Also delete any replication cleanup tasks. Cleanup tasks have a name in the following format:

   <publisher_server_name>_<subscriber_server_name>_cleanup.

Step 8 - Delete the .Sch and .Tmp files in the Repldata Directory

Delete the .sch (published table schema script) and .tmp (synchronization data) files in the MSSQL\Repldata directory on the distribution server.

Step 9 - Drop the Distribution Database

Run the following command to drop the distribution database:

   drop database distribution

Step 10 - Reset the MSlast_job_info Table

The MSlast_job_info table exists in each subscribing database and keeps track of the last job that was replicated to that database from each publishing database. The MSlast_job_info table is created by the distribution task after you subscribe to a publication.

If a subscribing database is not currently subscribed to any publishing databases, you can simply use the following command to drop the MSlast_job_info table in each subscribing database:

   drop table MSlast_job_info

   delete MSlast_job_info
   where publisher = '<publishing_server_name>'
   and publisher_db = '<publishing_database_name>'

Step 11 - Verify the Replication Registry Entry

Before you can reinstall replication, the "DistributionDB" value under the HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Repliction registry key must have a null string.

You can run the following command from the Master database to make the string null:

   xp_regwrite 'HKEY_LOCAL_MACHINE',
   'SOFTWARE\Microsoft\MSSQLServer\Replication', 'DistributionDB',
   'REG_SZ', ''