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.
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 <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.
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]
/****** Object: StoredProcedure [dbo].[sp_MSdel_dboArticle_1] Script Date: 10/14/2011 22:00:43 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON
ALTER procedure [dbo].[sp_MSdel_dboArticle_1] @pkc1 int
RETURN delete [dbo].[Article_1]
where [Col1] = @pkc1 if @@rowcount = 0
exec sp_MSreplraiserror 20598
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.
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.
Consider testing this scenario through a simple transactional replication setup
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.
Tuesday, November 01, 2011 - 2:44:20 PM - BuntyBoy
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.
Friday, November 04, 2011 - 7:28:56 AM - Mohammed Moinudheen
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 - 8:33:42 AM - Mohammed Moinudheen
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.
Wednesday, November 09, 2011 - 7:58:07 AM - Mohammed Moinudheen
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?
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?
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.