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.

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.

  • This was first published in July 2005

    Dig deeper on SQL Server Database Modeling and Design

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close