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 differentno data losswhen the publisher is a SQL Server 2000 Enterprise instance.
This article serves to alert the SQL Server community of the possible data loss that the changes in the SQL Server 2005 merge replication engine can cause. It uses a topology and test environment example to demonstrate how to reproduce the data loss. The intermittently connected application scenario is Brad Pitt and Angelina Jolie's assistant taking grocery orders on her Tablet PC, connecting the Tablet PC to the network when she's at the "Brangelina" home, and then taking all the orders with her when she goes to the supermarket.
Description of the Environment
The topology example uses SQL Server 2005 Developer Edition with SP2 as publisher and distributor and SQL Server Express with SP2 instances as subscribers. The operating system in this case is not relevant, but the test environment runs Microsoft Windows 2000 and the subscribers run Windows XP Professional Edition.
The publisher database uses the following SQL Server 2005:
Microsoft SQL Server 2005 - 9.00.3050.00 (Intel X86) Mar 2 2007 20:01:28 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
The subscriber database uses the following SQL Server 2005:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
The merge replication is set up as a pull subscription (agent runs at the subscribers). The example uses the Windows XP Synchronize Tool to start the merge agent. The publication compatibility level is 80RTM to keep backward compatibility with MSDE subscribers.
The example also has row filtering in place to reduce the amount of rows replicated to each subscriber. In a real-world scenario, each end user works with its own set of data only. The example filters the data that is sent to each subscriber based on the ownership of this data. Data partitions are not implemented in this scenario.