Not replicate everything in Subscriber -- sql server replication

Kathy
1/30/2007 10:44:01 AM

Hi, I would like to implement Transactional Replication in SQL Server 2005 to
meet requirement for reporting functionality. The idea is to replicate data
in a replica in which reporting will be perfomed. The following is additional
requirement:
Deletes of aged data in source datebase should not be replicated in the
target as we would like to have all of historical data in the target for
reporting.
My questions are:
1) Is it possible to disable 'delete' while allowing only 'insert' and
'update'?
2) Is it possible to disable replication for a certain time period so that
'deletes' would not go to the target?
Since I am allowed to perform 'deletes' of aged data in a dedicated time
period, either of the two would work for me. Please reply with technical
details.

Kathy
1/30/2007 1:05:00 PM

Thanks. my next problem is I am not allowed disable all kinds of 'deletes'
althoug I know what 'deletes' I don't want to replicate. Do I have
flexibility to configure selectivity of delete based on login, time or
anything else?
Thanks

[quoted text, click to view]

Kathy
1/30/2007 1:52:00 PM

Thanks again for your prompt response. Do I have option to temporarily stop
agent so that certain delete will be unknown to replication? The real reason
behind this is I need to prune my source database periodically and I don't
want the same pruning to occur in my target database. I do have control over
the time when pruning happens and no other application will run at the time.

[quoted text, click to view]

Kathy
1/30/2007 3:24:01 PM

Thanks. I will take a look at it. You are helpful.

[quoted text, click to view]

Paul Ibison
1/30/2007 7:31:55 PM

Kathy,
using 'NONE' in the @del_cmd value or sp_addarticle will prevent the
replication of deletes selectively. This is equivalent to adding 'NONE' in
the article properties dialog box, (commands tab from memory).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Paul Ibison
1/30/2007 9:37:33 PM

Kathy - this property isn't configurable like that - only unsubscribed
articles can be changed. As far as I can see, the best you could do is to
change the text of the stored procedure on the subscriber to prevent the
delete, as the result of a job based on the other criteria (but sounds like
a maintenance headache :)).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Paul Ibison
1/30/2007 11:15:02 PM

The log reader agent will still read all these transactions afterwards so
they'll still be there. One thing I haven't yet tried out is
sp_setsubscriptionxactseqno - I think this might solve your issue:
http://msdn2.microsoft.com/en-us/library/ms151331.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : Not replicate everything in Subscriber