SQL Server Replication provides a comprehensive set of features for synchronising data between SQL Server databases and can be configured to suit many different scenarios.
As a business scales and its software requirements change, it’s not uncommon for the data architecture to also change. When this happens there may be a need to move data to different servers, remove existing replication configurations, and possibly set up existing hardware for other purposes.
In cases like this, or when errors have been made in the original replication setup, it can be very useful to have a quick way of completely removing replication from a server to ensure a clean slate.
In the section that follows I provide a script that will allow you to remove replication from your SQL Server and know with confidence that the process has been completed successfully.
Replication removal
It’s possible to remove most of a replication setup visually via the SQL Server Management Studio GUI.
However, it takes time to go through this process and oftentimes there are replication objects such as databases and constraints that fail to drop correctly. This leaves your system in an untidy state and potentially causes issues if you intend to set replication up again from scratch on the same server.
As a result, my recommendation is to use a SQL script that executes the stored procedures that are needed to remove the replication objects. This means that you can check the results of the procedures to ensure that they have completed execution with no errors.
The script
In my experience, I have found the following script to be a very reliable way of completely obliterating all replication objects from your SQL Server instance (for the parameters specified).
CAUTION
The script below is designed to remove ALL replication objects from your server. Please make sure that you only execute this script if you are 100% sure that you want to completely wipe your entire replication setup.
Note that before running the script, you must update all of the variables to match your setup. The variables and lines of code that you will need to consider changing have a comment in CAPITALS beside them.
-- Declare and set variables. DECLARE @distributionDB SYSNAME; DECLARE @publisher      SYSNAME; DECLARE @publicationDB SYSNAME; SET @distributionDB = N'distribution'; -- CHANGE THIS TO THE DISTRIBUTION DATABASE NAME. SET @publisher      = N'SERVER\MSSQL'; -- CHANGE THIS TO THE PUBLISHER NAME. SET @publicationDB = N'AdventureWorks'; -- CHANGE THIS TO THE PUBLICATION DATABASE NAME. -- Remove all replication objects from the publication database. USE [AdventureWorks] -- CHANGE THIS TO THE PUBLICATION DATABASE NAME. EXEC sp_removedbreplication @publicationDB; -- Remove the publisher registration at the distributor. USE master EXEC sp_dropdistpublisher @publisher; -- Delete the distribution database. EXEC sp_dropdistributiondb @distributionDB; -- Uninstall the local server as a distributor. EXEC sp_dropdistributor;
Let’s walk through the above script in detail.
In the first few lines, some variables are declared and set.
In most cases, the distribution database will simply be called the default name of ‘distribution’. If you happen to have changed this when you set up replication originally, please update the value of the @distributionDB variable to match the custom name you chose.
The @publisher variable should be set to the name of the SQL Server instance that acts as the publisher. Usually, this is just the server name on its own, or the server name followed by a backslash and the instance name.
The @publicationDB variable should be set to the name of the publication database.
The next line switches to the publication database context by using the USE keyword followed by the name of the database to switch to. Make sure you have specified the name of the publication database here.
The sp_removedbreplication stored procedure removes all replication objects from the publication database. This will remove things like the ‘rowguid’ column and the constraints and triggers that are added to the database after setting up Merge Replication.
Before calling the next stored procedure, the script switches over to the primary system database.
The sp_dropdistpublisher stored procedure is used to remove the publisher registration at the distributor.
The sp_dropdistributiondb stored procedure deletes the distribution database.
Finally, the sp_dropdistributor stored procedure is used to uninstall the local server as a distributor.
If you have executed the script and there are no errors then you can be sure that your server and databases are replication-free and you can now operate from a clean slate again.
Other notes
For the most common replication setups, the above script can be executed without any tweaks, other than updating the variables etc. to the appropriate values.
In most cases, the publisher and distributor are installed on the same server, simplifying the replication setup. If you happen to have your distributor set up on a different server to the publisher, then you’ll need to execute the distributor scripts on the distribution server.
The stored procedures used in the script apply to all types of SQL Server Replication (Snapshot, Transactional, Merge). However, some of the stored procedures have arguments that can control how they affect specific types of replication setups e.g. for the sp_removedbreplication stored procedure you can specify the value of the @type argument as ‘merge’ to only remove Merge Replication objects from the database.
Summary
In this blog post, I have provided a SQL script that will completely remove all aspects of a SQL Server Replication setup.
I started by mentioning why I believe it’s important to use a script to perform the removal operation and proceeded to provide a listing of the SQL code.
I then walked through each part of the SQL script in detail to explain what it is doing and finished with some notes regarding things that you may need to consider if your replication setup is more complex.
Comments