Mobilize your Enterprise and Achieve Global Scalability with Windows Mobile and SQL Server Compact

This Session at tech-ed was held by Rob Tiffany, about how to utilize SQLCe with merge replication to achieve a scalable solution with SQL Merge replication to and from devices.

This is the typical session that I wish I’ve been to 3 years ago. We’re using merge replication to devices In our current product (not the new on though). We’ve had moderate success with it, but Rob’s recommendations could definitely improve our synchronization performance.

In general most of the what Rob recommended is about the setup of replication devices, and the performance of the hardware on which they run. There were also some suggestions of organizing the database. Ill try to summarize.

• Organize your data across replication servers based on geographical location, it is easier for an SQL server to handle a subset of the overall data based on location. Location is also natural when it comes to mobile devices.
• Have the replicated databases synchronize their subsets of business hours.
• Make sure that the database reserves most of the data, but leave some memory left. This is to make sure the database works intensively with memory without thrashing the memory access for the operating systems services. Swapping memory to disk for common operating system services can reduce the performance of the server.
• Keep as short a span as possible for the subscription timeout date. The faster a subscription times out the less meta-data is stored.
• Have the hidden system tables run on a different physical disc than the database itself.
• Having row-level tracking increases performance but can increase the amount of conflicts.
• Try to keep join-filter depth as low as possible, and reduce the amount of filters. The database structure sets the limits of how good performance you can achieve with merge-replication.
• Make sure the thread-pool settings equals the numbers of cores of the hardware, this is to avoid thrashing the CPU with alot of thread switching.
• Try to defrag or rebuild the indexes on the replicators often. A good tool to check for indexing hot spots is the performance dashboard
• For the IIS have the .dll file used for merge replication located on a separate physical disc.

All in all, alot of suggestions on how to make your merge replication work better on mobile devices and scale across geographical locations.