Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #771

Re: Replication Filtering what gets replicated by DML action

Show all headers | View raw

Mark D Powell (Mark.Powell2@hp.com) writes:
> I just recently started looking into MS SQL Server 2005/8
> replication.  We have a vendor product where the data is purged at
> time N.  The vendor wants us to reduce the amout of data we are
> keeping while the users would like to keep the data longer so the idea
> the users had was if we could create an archive database where we
> could hold the data for a much longer time period while purging it
> more aggressively from the current production system.
> 
> As one of the potential methods for creating and maintaining this data
> I started to look at the MS SQL Server replication featue.
> Unfortunately the couple of books on line articles I read had verbage
> that indicated the replication would be all DML.  We would want the
> insert and updates but not the deletes.  I am wondering if the
> replication feature has configuration options that allow control of
> what DML activity is replicated and where in the BOL I would find the
> information.
 
As Bob says, you can configure replication to replicate INSERT and UPDATE, 
but not DELETE.

The problem, as you might realise, is that DELETE may be performed for two 
reasons: 1) cleanse out old data. 2) Normal deletion of incorrectly or 
invalid data.

Thus you need to be able identity the right deletions. The smoothest is
if purging is handled with partition switching - in SQL 2008, you can
tell replication to ignore that. But this requires 1) Enterprise Edition
2) Cooperation from your vendor.

Else, if the purge jobs are run in the wee hours of night where nothing else 
is happening, you could flip a switch in replication, maybe turn it off.

I would say that whatever you do, you have a substantial amount of work
ahead of you.

I need to add that I have very limited experience of replication myself,
and I would recommend that you consult the people in
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/threads.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Replication Filtering what gets replicated by DML action Mark D Powell <Mark.Powell2@hp.com> - 2011-11-01 06:40 -0700
  Re: Replication Filtering what gets replicated by DML action "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-11-01 09:12 -0500
    Re: Replication Filtering what gets replicated by DML action Mark D Powell <Mark.Powell2@hp.com> - 2011-11-01 08:55 -0700
      Re: Replication Filtering what gets replicated by DML action "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-11-01 11:20 -0500
  Re: Replication Filtering what gets replicated by DML action Erland Sommarskog <esquel@sommarskog.se> - 2011-11-01 22:27 +0100
    Re: Replication Filtering what gets replicated by DML action Mark D Powell <Mark.Powell2@hp.com> - 2011-11-02 06:18 -0700
      Re: Replication Filtering what gets replicated by DML action Erland Sommarskog <esquel@sommarskog.se> - 2011-11-02 15:09 +0000

csiph-web