RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server 2005 Bug Alert: Merge Replication Could Mean Data Loss : Page 3

The SQL Server 2005 merge replication engine introduced breaking changes that can cause data loss for both MSDE and SQL Server Express subscribers. Find out how this bug manifests itself in a real-world scenario.

Signs of Data Loss
The Brangelina team is now up to date on the assistant's actions. She leaves the house and proceeds to go shopping for her second order. After a few hours, she receives a page from the Brangelina team: make sure you don't buy the pizzas from just anywhere, as they have to be organic.

What is going on? She swears she bought the pizza from one of the approved places and filled in that information accordingly. As expected, she can no longer see this order to update the details. She will have to go back and ask for a receipt at the pizza place.

All this trouble began when the savvy DBA informed the assistant that he was upgrading a server. Now the Brangelina household is misinformed all the time.

The data on the Publication database looks like you see in Figure 11 and Figure 12.

Click to enlarge
Figure 11. Order Table on the Publication Database:
The Order table looks like this on the Brangelina publication database.
  Click to enlarge
Figure 12. OrderDetail Table on the Publication Database:
The OrderDetail table looks like this on the Brangelina publication database. Notice the data loss.

The data on the subscriber database looks like you see in Figure 13 and Figure 14.

Click to enlarge
Figure 13. Order Table on the Subscriber Database:
The Order table looks like this on the Brangelina subscriber database.
  Click to enlarge
Figure 14. OrderDetails Table on the Subscriber Database:
The OrderDetails table looks like this on the Brangelina subscriber database. The row filtering took place as expected.

The assistant calls the DBA and informs him that Brangelina's family cannot see the order details, and this had never happened before the server upgrade. The DBA checks for conflicts at the publication and finds one (see Figure 15 and Figure 16).

Click to enlarge
Figure 15. The Conflict Viewer Interface of the Conflict:
The conflict is recorded in the conflict viewer.
  Click to enlarge
Figure 16. The Subscriber Is Selected as the Loser:
The updates on that row never take place at the publisher.

The DBA tried to set up an MSDE subscriber, and the result was very discouraging: the data loss continued, but the conflict was not recorded at the publication. He decides to resolve this situation manually by keeping the subscribers with SQL Server Express and submitting the conflict loser. He finds it odd that he never saw this conflict recorded before when he had the SQL Server 2000 Enterprise edition as publisher and the MSDE client as subscriber. Data is not lost for now, but he might have to develop a custom conflict resolver.

The Brangelina family is now happy because they see the pizzas were purchased at a fully organic place, while the DBA is working full time on a temporary workaround and a final custom conflict resolver.

Update-Delete Operations at the Publisher
The behavior of update-delete operations at the publisher differs greatly from SQL Server 2000 to SQL Server 2005. Microsoft implemented this behavior change by design. The Microsoft SQL Server 2005 Books Online example offers the following explanation:

The Merge Agent detects update-delete conflicts when an update of data at one node conflicts with a delete at another. In this case, the Merge Agent updates a row; however, when the Merge Agent searches for that row at the destination, it cannot find the row because it has been deleted. If the winner is the node that updated the row, the delete at the losing node is discarded and the Merge Agent sends the newly updated row to the conflict loser. The Merge Agent logs information about the losing version of the row to the MSmerge_conflicts_info table.
The Brangelina assistant scenario demonstrated how this behavior could occur in the real world:
  1. Deletion takes place at the subscriber as expected.
  2. When the merge engine tries to delete this row at the publication, a conflict is reported.
  3. The row is then rolled back to its original state, losing all the updates that occurred at the subscriber.

Lizet Pena de Sola has eight years of experience in software development. She's an MCAD and MCTS in SQL Server 2005. She currently works as technical lead at Computer Aid Canada and is involved in the development of SmartClient applications.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date