Reproducing the Data-Loss Scenario
This section explains how to reproduce two data-loss scenarios: Repro 1 and Repro 2.
Repro 1 addresses the scenario described in the previous section (bug number 442076).
Repro 2 addresses the data loss that occurs on a table where the join filters are in the direct order of PK-FK (not an active bug).
Repro 1
The relevant partition parameters for Repro 1 are as follows:
@keep_partition_changes=N'false'
@use_partition_groups = N'false'
@compatibility_level=N'90RTM'
The replication loses data on the Order table. The data that was filtered from the subscriber on a previous replication is lost on the second replication at the publisher.
Take the following steps to reproduce this data loss (download the scripts for Repro 1 here):
- Create Publication Database.sql.
- Run the script Insertions at the Publication Database.sql.
- Create Test Merge Publication.sql.
- Create Subscriber Database.sql.
- Run Snapshot Agent. Note: You should configure your distributor for the publisher if it hasn't been before.
- Create the subscription at subscriber. (Create Subscription.sql).
- Replicate (you can use Windows XP Synchronization Manager if you're using SQLE subscribers).
- Run the script First Batch Update (@Subscriber).sql.
- Replicate again. (This will make sure the replication works as expected.)
- Run the script Second Batch Update (@Subscriber).sql.
- Replicate again.
Data loss will occur, as the updated records from Step 8 (running the First Batch Update (@Subscriber).sql) are gone from the publication database after Step 11. Also, no conflict is recorded.
Repro 2
The relevant partition parameters for Repro 2 are as follows:
@keep_partition_changes=N'false'
@use_partition_groups = N'false'
@compatibility_level=N'90RTM'
Some factors to note about this scenario are:
- The enumeration of changes does not account for the deletions in the ApprProperty table at the publisher.
- The filter joins in this case are in direct order of PK-FK (The Comment table has a FK to the Order table).
- The hierarchy is as follows:
- The table User is filtered based on username. The PK on this table is UserId.
- The table Order has a FK to User (UserId). The PK on this table is OrderId.
- The table Comment has a FK to Order (OrderlId).
Take the following steps to reproduce the Repro 2 data loss (download the scripts for Repro 2 here):
- Create Publication Database.sql.
- Run the script Insertions (@Publication).sql.
- Create Publication.sql.
- Run Snapshot Agent.
- Create Subscriber Database.sql.
- Create the subscription. (Create Subscription.sql.)
- Replicate.
- Run the script First Insertions in Comments (@Subscriber).sql.
- Replicate again. (This will make sure the replication works as expected.)
- Run the script First Update_Order (@Subscriber).sql. (This update changes the order rows for filtering.)
- Replicate. (The filter works properly and all comments are there.)
- Replicate again.
Comments will lose data after Step 12. It should contain three comments, but the comments inserted in Step 8 (running the First Insertions in Comments (@Subscriber).sql) are lost.
This information should be useful to DBAs and database developers who use merge replication systems with data filtering in SQL Server 2005.