Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Options to not replicate SQL Server DELETE commands

MSSQLTips author Mohammed Moinudheen By:   |   Read Comments (18)   |   Related Tips: More > Replication
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.

sql publication properties

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

sql server article properties

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.

reinitialize subscriptions

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.

sql server replication monitor

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

sql distributor to subscriber history

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 <stored procedure> is the default syntax for DELETE operations for SQL Server transactional replication. Once you select Call <stored procedure>, 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.

sql replication stored procedures

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


Last Update: 10/26/2011


About the author
MSSQLTips author Mohammed Moinudheen
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, February 20, 2014 - 9:51:32 PM - Rama Read The Tip

Hi,

I have a production database which replicates to replication database. I would like to perform archving on production database where I would like keep three months old data and delete rest of the data but dont want to replicate such actions to replication database. Whatever you mentioned in the article should work but I have a scenario to consider where as part of my application, there are operations that delete records on database. So if I stop replicating deletes completely,  how could I replicate inentional deletes. In short I want to replicate deletes but dont wanna replication purge action from production database.

 

Thanks in advance!


Thursday, June 27, 2013 - 6:51:18 PM - Mohammed Read The Tip

@Edward,

Thanks for the question. It appears you may be correct. Didn't get to test it though.

Regards,

Mohammed


Thursday, June 27, 2013 - 11:39:25 AM - Edward Hixon Read The Tip

On question that I have regarding option #1; As I am turning off DELETES temporarily and want to turn then back on after the deletes on the publisher are correct, and if I follow the "Mark for Reinitialization" step, won't that replace the subscription data and thus result in the deletes being effectivly replicatated? 


Tuesday, March 05, 2013 - 11:34:03 PM - MM Read The Tip

Do we have a similar option for snapshot replication as well?


Tuesday, February 05, 2013 - 12:26:17 PM - Sapen Read The Tip

Nice Article. Thanks for the post.  Is there a way that I can restrict the updates specific only to a column? Lets say if updates occur on source table on column a, column b and column c then I dont want any thing to get updated in the destination. But if update occurs on column d on source table then I want it to get updated in destination table too. Is this possible?

 


Friday, January 25, 2013 - 9:11:52 AM - Bill M Read The Tip

Exactly what we were looking for ...thanks!


Thursday, August 02, 2012 - 8:22:28 AM - Mohammed Moinudheen Read The Tip

Thank you Hy, appreciate your feedback.


Thursday, July 19, 2012 - 1:00:53 AM - Hy Chan Han Read The Tip

It really help mes.

My problem has been occured for a long time it is now resolved by this useful article.

Thank for posting and keep your good works.

 

 


Wednesday, November 09, 2011 - 7:58:07 AM - Mohammed Moinudheen Read The Tip

Ron, Thanks for your inputs. Very useful.


Tuesday, November 08, 2011 - 5:51:22 PM - Ron Read The Tip

We take another route in that all deletes are done via stored procedure calls on the publisher.  We take advantage of that to replicate execution of these delete stored procedures to a target procedure on the subscribers that does nothing.  This has advantages of not requiring a new snapshot, not touching any of the generated code, and continuing to work even if a new snapshot is performed (which would typically wipe out your modifications to the generated procedures).  It does not protect the data from direct deletions, so it only works if you do not allow your users direct access to deleting table data.


Friday, November 04, 2011 - 8:33:42 AM - Mohammed Moinudheen Read The Tip

Jason, Thanks for your comments.


Friday, November 04, 2011 - 7:37:51 AM - Jason Bunn Read The Tip

With option #2, you have to be careful, since any schema change, if replicated, causes a regeneration of the stored procedures, and if you are manually editing the delete proc, you run the risk of losing that change.  A safer option may be to use "register custom scripting" to generate custom stored procedures for the delete statements.  That way, you are using a procedure that you develop, rather than manually modifying a system generated procedure, and this will ensure that your functionality will survive a schema change.


Friday, November 04, 2011 - 7:28:56 AM - Mohammed Moinudheen Read The Tip

Steve, Thanks for your comments. I thought option 2 is used if you don't want to apply the snapshot.


Friday, November 04, 2011 - 5:51:45 AM - Steve Johnson Read The Tip

Option 2 is completely misleading and will cause data loss on the subscriber when the snapshot is applied - the delete stored proc, is used when replicating transactions post-snapshot application.

The publication option 'Destination Object-->Action if name is in use' - which has a default value of 'Drop existing object and create a new one' will remove the existing data at the subscriber, before the snapshot data is bcp'd, regardless of your suggested stored proc. hack.


Tuesday, November 01, 2011 - 2:44:20 PM - BuntyBoy Read The Tip

Very good article Moinu!!!!!

===========================================
Better try and fail, instead of not trying at all...

Database Best Practices


Wednesday, October 26, 2011 - 11:36:21 PM - Mohammed Moinudheen Read The Tip

Tony,

Thanks for your comments. For not replicating insert or update, you could try the same options as described and select the correct format under the 'statement delivery' section of article properties. Or you could try including the 'return' statement in the relevant stored procedure in the subsriber database.

Thanks,

Mohammed Moinudheen

 

 


Wednesday, October 26, 2011 - 8:34:44 AM - Tony Read The Tip

BTW - Great article!!!


Wednesday, October 26, 2011 - 8:33:44 AM - Tony Read The Tip

I am new to replication.  Are there any options to not replicate insert or update commands?  I assume select statements are not replicated.




 
Sponsor Information