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

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

Re: Replication Filtering what gets replicated by DML action

Show all headers | View raw

Mark D Powell wrote:
> 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.
>
> Thanks.
>
> Mark D Powell

Have you looked into partitioning instead of archiving-and-deleting? It's
easier to set up and maintain, but it requires Enterprise Edition, so if you
only have Standard you cannot consider this.

I don't think you can replicate only non-deletions.
Perhaps log shipping is the answer. Prior to performing the deletions,
checkpoint and force the logs to be shipped. Then shut off log shipping
during the deletions, and turn it back on after the deletions are performed
and the transaction log is backed up to truncate it. I'e never tried this so
I'm not sure it's possible.

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