SQL Server 2005 Bug Alert: Merge Replication Could Mean Data Loss : Page 2
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.
by Lizet Pena de Sola
Nov 2, 2007
Page 2 of 3
Steps to Reproduce the Data Loss
The following steps walk you through reproducing the SQL Server 2005 data loss and are interjected with real-world examples from the Brangelina assistant scenario:
Create the publication database. Find the script for creating the publication database in the code download.
Create the tables inside the database. This script is also provided in the code download. The database's design is simplistic (see Figure 1) and suitable only for demonstration purposes.
Figure 1. The Simplistic Database Design Diagram: This database design is suitable only for demonstration purposes.
Insert data for the tests. (At this point, the orders are created blank with an OrderStatus=0, not completed.) In the example scenario, this is done when the assistant enters new orders on her Tablet PC:
DECLARE @userid intINSERT INTO [MergeRep_Test].[dbo].[User]
SET @userid= SCOPE_IDENTITY();
DECLARE @orderdetailid intINSERT INTO [MergeRep_Test].[dbo].[OrderDetail]
INSERT INTO [MergeRep_Test].[dbo].[Order]
, 'Order some pizza for the kids'
Create a publication that publishes all the tables in the database, and maintain SQL Server 2000 compatibility levels to test this scenario with MSDE. (Use the create publication scripts from the code download.) The Brangelina family's savvy DBA created the publication on the family's SQL Server 2005.
The row filtering should look like you see in Figure 2 (The snapshot agent security is chosen to run with the SQL Server Agent account in this case).
Figure 2. Row Filtering at the Publication: This publication publishes all the tables in the database.
Now it is time to run the snapshot agent. Create the subscriber database and the subscription (see the scripts in code download). The subscription is set to run the agent at each subscriber (pull subscription), and the merge agent security is "By impersonating the process account." The merge agent is located at the subscriber and should run on demand only.
The Brangelina family's database developer creates the subscriber database on the SQL Server Express instance running on the assistant's Tablet PC and creates the subscription. The assistant takes her Tablet PC from the developer and connects it to the house's network. She launches the replication process through her client application so she can review the orders the family needs for the day.
Figure 3. Windows XP Synchronization Tool: This is the Windows XP Synchronization Tool interface for replication.
Figure 4. The First Synchronization Is Complete: This is the Windows XP Synchronization Tool interface for a successful synchronization.
The assistant now reviews the orders on her client application and changes the status to "Open" ( OrderStatus=1). The data she now has on the subscriber looks like you see in Figure 5. She then rushes to the closest supermarket to buy the merchandise.
Figure 5. Data on the Assistant's Tablet PC: This is the data sitting on the assistant's Tablet PC after the first synchronization.
While she fulfills the first order, she updates both the Order and the OrderDetail tables. Finding suitable organic pizza can be a very time-consuming task, but she finally managed to find an Italian shop that uses organic flour, cheese, tomatoes, and ham. She buys the pizza there and marks her order as complete.
Run the update script on the subscriber database. (See the scripts in the code download.) The assistant's local data looks like you see in Figure 6 and Figure 7 after she completes her update.
Figure 6. Assistant's Order Table: The assistant's Order table looks like this after her update.
Figure 7. Assistant's OrderDetail Table: The assistant's OrderDetail table looks like this after her update.
The publisher database at Brangelina's house looks like you see in Figure 8 and Figure 9.
Figure 8. Order Table on Brangelina's Publisher Database: The Order table looks like this on the Brangelina publisher database.
Figure 9. OrderDetail Table on Brangelina's Publisher Database: The OrderDetail table looks like this on the Brangelina publisher database.
The assistant now wants to inform Brad Pitt that the first order (order some pizza for the kids) has been fulfilled. She synchronizes her client application with the publication database, to which Brad has select access. (This over-simplified scenario uses Windows XP synchronization again.) The client application will not notify the assistant of these conflicts (after all, the example is set to have the server win in every situation).