FIX: Missing rows on subscriber when you drop and re-add article from another publication or add subscription

Gilt für: SQL Server 2008 R2 Service Pack 2SQL Server 2008 R2 StandardSQL Server 2008 R2 Enterprise

Symptoms


Assume that the following conditions about transactional replication are true:
  • You have a single publisher database that has two or more transactional publications in Microsoft SQL Server 2008 R2.
  • These publications are all serviced by the same log reader agent and each publication consists of one or more articles. There are no overlapping articles between publications.
  • The log reader agent is stopped, or has developed some latency because of high data change activity at the publisher database.
  • The distribution agents for the subscriptions are running either in continuous mode or with a very frequent schedule.
When you change the publications by one of the following methods:
  • Add a new subscription that is not initialized from a snapshot by setting the synchronization type to "replication support only," "initialize with backup," or "initialize from lsn."
  • Add a new article to a publication that already has a subscription with a synchronization type of "replication support only," "initialize with backup," or "initialize from lsn."
In this situation, some data changes that occur before you change the publication configuration may not be replicated to the subscribers. The distribution agents may fail. Additionally, you may receive one of the following errors depending on the type of skipped changes:
Error 20598, "The row was not found at the Subscriber when applying the replicated command"
Error 2627, "Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'."

Resolution


The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 13 for SQL Server 2008 R2 SP2

Workaround


To work around this issue, stop the distribution agents before you change the publication configuration. Make sure that the log reader agent is running and has caught up with the current changes before restarting the distribution agents.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.