We have developed a mobile system that use 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
