Prevent updates, inserts and deletes from being replicated

Business requirements may require that inserts, updates and deletes are not replicated. Find out how to prevent replication in this tip, one of 15 tips in 15 minutes.

Published: 26 Jul 2005

The following is one of 15 replication tips in 15 minutes. Click here to return to the full list of tips.

15 of 15 replication tips: Prevent updates, inserts and deletes from being replicated

Sometimes your business requirements require that inserts, updates and deletes not be replicated. To do this, right click on your Publication, select Properties, click on the Articles tab, and click on the Browse button to the right of each table you wish to modify. Click on the Commands tab. For the Insert command (or Update or Delete as required), replace the stored procedure name with the work NONE.

Occasionally you will need to do deletes on a daily basis, but do a batch delete on a monthly basis. You do not want the batch delete to be replicated, but you do want the day-to-day deletes to flow. There are several approaches to this:

  • Drop the subscription, do the deletes, and then do a no-sync subscription.
  • Comment out the logic in the replication stored procedures on the subscriber when you are doing the deletes; uncomment them when you are done; and use the Continue on Data Consistency Errors profile.
  • Apply a filter on your publication that always equates to true (i.e. where 1=1). Replication uses filter-stored procedures on the publisher. You can change these filters to return false when you want to skip commands. Then do your deletes. After you are done, change these filter-stored procedures to return false when the filter is false.

    Sample script 2 illustrates this: 

    create database filter
go
create database filtersub
go
use filter
go
create table filter
(pk int not null identity constraint pk primary key, 
charcol char(20),
date_time datetime default getdate())
go
insert into filter (charcol) values('test')
go
insert into filter (charcol) values('test')
go
insert into filter (charcol) values('test')
go
insert into filter (charcol) values('test')
go
sp_dboption 'filter','published','true'
go
sp_addpublication 'filter',@status='active'
go
sp_addpublication_snapshot 'filter'
go
create procedure [dbo].[FLTR] for replication as if exists (select * from

 [dbo].[filter] where 1=1) return 1 else return 0
go

sp_addarticle 'filter','filter','filter',@destination_table='filter_sub',
@type='logbased manualfilter', 

@filter='FLTR',@filter_Clause='1=1'
go
--we are making a subscription in the local database
sp_addsubscription 'filter','filter',@@servername
go
insert into filter (charcol) values('before change')
go
waitfor delay '00:01:00'
go
select * from filter_sub 
go
--observer that the row has travelled
--now we will modify the proc to alway return false
alter procedure [dbo].[FLTR] 
for replication as if exists (select * from 
[dbo].[filter] where 1=1) return 0 else return 0
go
insert into filter (charcol) values('after change')
go
waitfor delay '00:01:00'
go
select * from filter_sub 
go
--observe that the row has not arrived
--now let's delete everything
delete from filter
go
waitfor delay '00:01:00'
go
select * from filter
go
select * from filter_sub 
go
--notice that there are no rows in filter, but all the rows are in filter_sub

    Click here to return to 15 replication tips in 15 minutes.

    About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.

    • Dig Deeper on SQL Server Database Modeling and Design

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close