Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx