Non-Convergence Bug in SQL2005
I recently reported an SP2 bug that creates non-convergence in merge publications with horizontal and join filters to MS. They are currently investigating. It only seems to affect new SQL2005 SP2 publications and SQL2000 that has been 'directly' upgraded to SQL2005 SP2 before having the snapshot run.
I recommend you check out that this bug does not affect any new publications you have planned. It can catch you unawares since you just expect the functionality to work, it having worked so well in the past. I have found that correctly fixing non-convergence on partitioned data in a production environment where you have lots of tables and filtering is a distinctly non trivial activity, especially as the bug will typically move over new child records, but not the originals!
My current workaround is to delete the out of sync parent and child (more complex in my real life scenario as there are multiple join filters at various levels) and reinsert with the correct partition data already set. Fortunately, there are not many of these errors to contend with and the custom application now checks for this new 'feature' so the data can be corrected quickly. The normal fix for non-convergence, sp_mergedummyupdate, does not work in this scenario, when run on either the parent or child record(s). Newly inserted child records move to the correct partition however. So in summary, the child records that should be moved into the new partition at the time of partition change remain trapped in a non-converging limbo.
A potential workaround is to change the value of the merge publication option @use_partition_groups to false, as this forces(?) the older 'setupbelongs' functionality (evaluate partition membership at sync time) to be used. Unfortunately, due to the nature of this bug you cannot change the value of this setting in an existing publication, create a new snapshot and be on your way. You must first drop existing publications and create them with the @use_partition_groups set to false. You will need to experiment on your test environment for what works best for you. I did not test all eventualities in this area. The problem is that the merge GUI nearly always chooses the new feature precomputed partitions.
To give you a feel of what this bug might do in practise I have created a simplified version in the form of a story - the poor DBA. ...

Comments