Options to not replicate SQL Server DELETE commands

Problem

I have transactional replication configured in my production environment. The business team has requested that I do not replicate delete operations on certain articles.  In this tip we look at a couple of options to not replicate DELETE commands.

Solution

In some cases older data may be required on the subscriber, but deleted at the publisher. In order to meet this requirement we could use either of the options outlined below. Note, both options assume transactional replication is already configured between two databases.

---

Option 1: Modifying Replication Settings

In this option we will use SSMS to make the changes, but this could be done using T-SQL commands as well.

In SSMS go to Replication -> Local Publications and right click on your publication and select Properties.  In the Publication Properties window click on 'Articles' and select the relevant article.  Go to "Article Properties" and select "Set Properties of Highlighted Table Article" as shown below.



In the article properties window, change the "DELETE delivery format" to "Do not replicate DELETE statements".



After the change click OK and you will see the below prompt.  As the article property has been changed the subscriptions need to be reinitialized. Click "Mark for Reinitialization" which causes the snapshot to be applied to the subscriber.



In SSSM, navigate to Replication and right click and select "Launch Replication Monitor" as shown below.  Go to your publication and click View Details as shown below to see the snapshot progress.



After clicking "View Details", you can see the details of the snapshot generation for the subscriber.



As you can see the entire snapshot is getting applied to the subscriber.  This option must be used with caution as there may be cases where your subscriber database is used as an archive and may have more data than your publisher database.  However, this depends on your environment and if your application is fine with getting the entire snapshot you could use this method.  Also, for very large databases you may not want to reinitialize the entire snapshot either, so again use caution.

Once this change has been made and if there is a need to revert to replicating deletes, just perform similar steps as above to allow delete operations by calling the replication delete stored procedure at the subscriber. Call is the default syntax for DELETE operations for SQL Server transactional replication. Once you select Call , you will also need to provide the DELETE stored procedure name for this specific article which would be available in the subscriber database.

---

Option 2 : Modifying the replication stored procedure in the subscriber database

For transactional replication, by default, stored procedures get created in the subscriber database for insert, update and delete operations. These could be viewed as shown in the screenshot below.


REP_S is the subscriber database and we could see the insert, update and delete stored procedures that were created for each subscribed article.  In option (1), we need to be concerned about the snapshot getting applied when we modify the article property. However, in order to overcome this shortcoming, there were some discussions I came across on the internet to make the replication delete procedure in the subscriber database to just RETURN and not actually do the DELETE. This would ensure that the replication delete procedure in the subscriber database exits unconditionally even though it is called. As per books online, RETURN in T-SQL enables you to "Exit unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed." (Source - http://msdn.microsoft.com/en-us/library/ms174998.aspx).

Using this concept, the delete procedure in the subscriber database could be modifed as shown. I have just added the RETURN statement right after the BEGIN, so the stored procedure exits immediately without doing the actual DELETE.  With this approach, we need not worry about the snapshot getting applied again to the subscriber.

--Include RETURN statement in stored procedure and alter the stored procedure in the subscriber database
USE [REP_S]


GO


/****** Object:  StoredProcedure [dbo].[sp_MSdel_dboArticle_1] Script Date: 10/14/2011 22:00:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[sp_MSdel_dboArticle_1] @pkc1 int

as
begin    

RETURN
delete [dbo].[Article_1]

where [Col1] = @pkc1 if @@rowcount = 0

    if @@microsoftversion>0x07320000

        exec sp_MSreplraiserror 20598

end

 
GO

In order to revert to the older configuration, to allow SQL Server to replicate deletes, you would need to just comment or remove the RETURN statement in the stored procedure.

Summary

Both options could be tested easily with a simple publication of a single article and performing the sequence of steps as shown above.  Also you should use Replication Monitor to see if there are any errors.
Note: the above steps were performed using SQL Server 2008 R2.

Next Steps

• Consider testing this scenario through a simple transactional replication setup
• Refer to other related tips on replication to get familiar with the concepts

 ------------------
By: Mohammed Moinudheen
Mohammed is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

Reference: http://www.mssqltips.com/sqlservertip/2520/options-to-not-replicate-sql-server-delete-commands/