locked
Deleting data after replication. RRS feed

  • Question

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

    1. Data is replicated from production server to archive server.
    2. Data is deleted from production server
    Thanks in advance...
    Wednesday, August 8, 2007 4:08 PM

Answers

  • 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

     

    Friday, August 17, 2007 3:57 PM

All replies

  • 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

    Tuesday, August 14, 2007 5:45 AM
  • 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.

    Thursday, August 16, 2007 4:21 AM
  • 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?

    Friday, August 17, 2007 10:32 AM
  • 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

     

    Friday, August 17, 2007 3:57 PM
  • Thanks for the idea, Greg.  A couple of questions:

     

    1) If we use a sproc to do pruning on the publisher and don't replicate it's execution, do we still get deletes going through the distributor even if we aren't replicating DELETE statements?  Or do we need to do both - replicate the sproc exec and choose not to replicate deletes in order to minimize distributor usage?

     

    2) There are so many changes to publications and articles, schema changes, etc that cause reinitialization of the subscription.  For an archive DB, this would be a disaster.  Are there techniques to resych/reinit that allow us to keep the archived data?

     

    Thanks,

     

    -Peter 

    Thursday, October 18, 2007 5:16 PM