How to: Specify that Deletes Should Not Be Tracked (Replication Transact-SQL Programming)

By default, merge replication synchronizes DELETE commands between the Publisher and Subscriber. Merge replication enables you to retain rows in the subscription database even when they have been deleted from the publication, and vice versa. You can programmatically specify that DELETE commands be ignored when creating a new article or you can enable this functionality at a later time using replication stored procedures.

Important

Enabling this functionality will result in non-convergence, which means that data present at the Subscriber will not accurately reflect data at the Publisher. You must implement your own mechanism for manually removing deleted rows.

To specify that deletes be ignored for a new merge article

  1. At the Publisher on the publication database, execute sp_addmergearticle (Transact-SQL). Specify a value of false for @delete_tracking. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

    Note

    If the source table for an article is already published in another publication, the value of delete_tracking must be the same for both articles.

To specify that deletes be ignored for an existing merge article

  1. To determine if error compensation is enabled for an article, execute sp_helpmergearticle (Transact-SQL) and note the value of delete_tracking in the result set. If this value is 0, deletes are already being ignored.

  2. If the value from step 1 is 1, execute sp_changemergearticle (Transact-SQL) at the Publisher on the publication database. Specify a value of delete_tracking for @property, and a value of false for @value.

    Note

    If the source table for an article is already published in another publication, the value of delete_tracking must be the same for both articles.

See Also

Other Resources

Optimizing Merge Replication Performance with Conditional Delete Tracking

Help and Information

Getting SQL Server 2005 Assistance