Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.