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

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

On Nov 1, 10:12*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
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 --

Mark D Powell wrote:
Well, I'm wrong. I should have done some research.You can configure
replication to ignore deletions:
http://www.sqlservercentral.com/arti...lication/3202/

Mark D Powell (Mark.Powell2 (AT) hp (DOT) com) writes:
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/For...cation/threads.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

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

Mark D Powell (Mark.Powell2 (AT) hp (DOT) com) writes:
Once you have set up replication, you will have some more articles to
review. Sorry, bad pun!

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx