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

Groups > comp.databases.ms-sqlserver > #764 > unrolled thread

Replication Filtering what gets replicated by DML action

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#764 — Replication Filtering what gets replicated by DML action

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

[toc] | [next] | [standalone]

#766

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.

[toc] | [prev] | [next] | [standalone]

#767

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 --

[toc] | [prev] | [next] | [standalone]

#768

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

Well, I'm wrong. I should have done some research.You can configure
replication to ignore deletions:
http://www.sqlservercentral.com/articles/Replication/3202/

[toc] | [prev] | [next] | [standalone]

#771

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

[toc] | [prev] | [next] | [standalone]

#772

Gentlemen, thank you for the update.  I will now try to find the time to review the referenced link material.  I will need to go back to the first article since I have never set up replication. I also have to deal with the fact that neither I nor the customer contact has any clue how the vendor application works.

Mark D Powell

[toc] | [prev] | [next] | [standalone]

#776

Mark D Powell (Mark.Powell2@hp.com) writes:
> Gentlemen, thank you for the update.  I will now try to find the time to
> review the referenced link material.  I will need to go back to the
> first article since I have never set up replication. 

Once you have set up replication, you will have some more articles to 
review. Sorry, bad pun!

> I also have to deal with the fact that neither I nor the customer
> contact has any clue how the vendor application works. 
 
Ouch! That's a major challenge. No chance that the vendor will help you?
You may also have to review the license terms to see what the vendor 
supports.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web