RDA, sometimes so good, sometimes so bad.  I post about data synchronization to mobile systems a lot because its one of those interesting problems to me.  There are many ways to do it, Merge Replication is one.  Merg Rep comes with so many drawbacks I could write a decent sized essay about it.  For now I'll just say that if you are considering merge replication you should try to talk to someone with experience implementing it and make sure you want to go down that path.  Synchronizing a read-only database is easy with merg rep or without using custom code.  Synchronizing a read-write database with custom code is a non-trivial endeavor.

Having written and re-written such things several times I have tried various methods.  After my first attempts at remote rebuilding a 100 meg database on a 200mhz strong arm over the internet using web services I decided that RDA needs to be a part of any solution.  RDA allows one to pull down SQL Sever data in a nice binary format that automagically turns into an SQLCE table schema and table data as it streams down to a mobile device.  You can push SQL data using RDA as well, but I don't like doing so for various reasons.  Since RDA is streaming data over HTTP it also gets you around some ugly memory issues.  Consider your woe when your XML message containing 10,000 rows of data is easily spit out by your server but runs the mobile systems out of memory every time.  Segmenting large messages using many XML calls quite frankly feels dirty as well.  As far as rebuilding that 100meg database over the internet, RDA is great.

On my current project we do frequent data synchronization over an AirLink modem.  These things are truly great but they have data rates that are woefully slow in a world that has become accustomed to cable modems and burstable ethernet.  We were having situations where in-office testing would have a sync time of say a minute and a half for a good amount of data.  However in the field some locations were so bad (-110 db signal strength or worse) that this same data packet was taking 20 minutes to process.  This is unacceptable.  Since I happen to have a method to FTP the handheld log files to myself I started doing some log file forensics.

In addition to low data rate, these CDMA modems have high latency, 500ms ping and such.  Every additional trip to the server was incurring large overhead in just making the request.  My data sync that was making an RDA.Pull("select blah blah from blah blah where DateLastChanged > myLastSync") for each table that was fine in our office (where the connection SUCKS butt apparently not as suck as some of our remote locations) but not fine over a low data rate, high latency, in-a-black-hole parking lot where our trucks must operate.  In testing with the SQL server DBA we have noticed an inexplicable overhead for each RDA operation that is about six seconds over a good connection and skyrockets exponentially on a bad one.  While we work out issues with other modem providers (Sprint, Verizon, and Cingular are the big players, Sprint is currently working but connectivity sucks in many areas) a solution was needed.  In the process of making my remote operationgs more "transactional" (after studying F# and what it's all about a tiny bit I dislike the common usage of the term idempotent, which I will explain in a future post) I ended up building multiple code paths for the data sync based on how much data was to be exchanged.  A full rebuild or "very large messages" will still use RDA and execute one pull per table, but in many cases the data package can be sent and received as one large XML message.  I had avoided this approach as long as possible due to the bloat of encoding everything as XML and the potential for memory problems on the clients.  I've found that an "average" XML message is about 179kb which makes me nervous given the CF 1.0 max stack size of 640k and my assumption that the webservice client must load the entire message into memory before it can deserialize it.  If this does become an issue I will do some column mapping such that <FuelDeliveryTransaction><FuelDeliveryTransactionId>12345</FuelDeliveryTransactionId><TotalPricePerGallon>$2.78</TotalPricePerGallon></FuelDeliveryTransaction> in a message becomes <FuelDeliveryTransaction><0>12345</0><1>$2.78</1></FuelDeliveryTransaction> and thereby saves many bytes of data over hundreds or thousands of rows.

I would say "Bring on the high speed wireless connection to every corner of the globe", but Johnny Mneumonic had a traumatizing effect on me.