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

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

Re: Replication Filtering what gets replicated by DML action

Show all headers | View raw

On Nov 1, 10:12 am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

I do not think built-in replication can do this either, but I have
only read the high level articles so I though I would ask.  I will try
to find the time to look at log shipping, but I would not expect to be
able to skip chunks of activity and have the logs successfully apply.
Still, I will put it on my list.

I told the customer contact that I expected custom code to be
required.  The problem is we do not know the vendor design and I
really do not want to spend the time to try to figure out what data
would need to be copied across since the applicaiton has over 100
tables.

Thank you.
-- Mark D Powell --

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