Merge Replication differences between SQL 2000 and SQL 2005

We have developed a mobile system that uses merge replication for SQL Mobile to SQL 2005. Previously we have developed mutliple mobile systems using merge replication for SQL Ce to SQL 2000.

Based on the knowledge we had gathered over about 4 years, we applied the synchronisation parameters for the SQL 2005 solution as we would for the SQL 2000 solution.

We have found there are some differences. Not too surprising I suppose, only some of these have us a little baffled.

For instance, there was a little flag called keep_partition_changes in SQL 2000 that is supposedly superceded by the use_partition_groups flag. However, if you don't set up your filtering to conform to the standards required by the use_pre_computed_partitions flag if you want it set to true, then the use_partition_groups flag gets set to false - also the @.partition_options falg gets set back to 0 (static or non-unique data) when we want it at 3 (Single Parition, One subscriber).

To top it all off, when you get the use_partition_groups flag working, there are restrictions on which columns you can update on the device. WTF? This seems ludicrous, to be unable to update data at the subscriber - particularly information that allows you to effectively "delete" data from your subscription.

Examples of the current behaviour are as follows,

On initialize for a subscriber, the subscriber will receive their own data as inserts, plus exact multiples of that data as updates. Say there are 100 rows in TableA, the subscriber gets 100 inserts, plus 6000 updates. TableB has 20 rows, the subscriber gets 20 inserts, 1200 updates.

Further to this, performance goes out the window when synchronising changes. Typically the data flow will be between 5 and 200 changes in both directions for a synchronisation. We are seeing sync times in the replication monitor of over 20 seconds per user. Surely the calculations do not take that long. The tables in the database are not very large.

This behaviour gets significantly worse as we load the system. The application has an auto sync function which is timed to operate evry 10 minutes. However, now that there is in excess of 50 or so users on the system, those synchronisation times blow out to multiple minutes and the server starts to thrash. We have looked at indexing and maintenance but to no avail.

Everything still points to the merge replication setup.

So, it seems obvious to me that we are mising some key information about how to set up merge replication in SQL 2005. We woudl be very gratefull if someone could point out the errors of our ways.

Sorry for the convoluted post. Hope someone can help us.

Cheers
Steve

Check if you are hitting the issue described in: http://support.microsoft.com/kb/917476/en-us

|||Thank you Mahesh.

that has certainly resolved the issue of the excessive data when a subscriber initialises.

Now, if I can just work out how to make this a more performant installation ...|||What other issues are you having regarding performance?|||The issues can be best described in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=514509&SiteID=1&mode=1
which is another post from me that is really related to this one.

Essentially there are various times when the performance just goes out the window and our perception here is that we have not fully understood, let alone implemented the changes we need to bring our replication scenario from it SQL 2000 guise to the SQL 2005 version that works best for us.

In the meantime we continue to experiment with the settings in the hope that we find our way.

We feel that our problem lies around the way we set up the partition_options flag.

Thanks for your help.|||I will follow up with you on that thread.