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

ith the proliferation of mobile devices such as BlackBerrys, Tablet PCs, and regular laptops, some critical applications need to store a cache of their data locally at all times. Whether or not the application has a good connection to the corporate network should be transparent to the end user. Data should be available at all times, and end users expect to work the same way whether they are in the office or on the road without Internet access. Merge replication is the core of many of these intermittently connected applications.

Recognizing the importance of this technology, Microsoft published a detailed description of the breaking changes the SQL Server 2005 release introduced in replication. Some of these changes, however, are not well documented yet. One such omission involves using SQL Server 2005's merge replication topology with row filtering: if the filters in place provoke an update at the publication and a deletion at the subscriber on the same row, the update on the publication never takes place. The result of this bug is data loss for both MSDE and SQL Server Express subscribers. The behavior is different—no data loss—when the publisher is a SQL Server 2000 Enterprise instance.