Distribution of data is the physical storage of data in different locations with access provided to the necessary data regardless of its location. Sometimes data is distributed for historical reasons; for example, when separate applications use different software and hardware. Sometimes data is distributed for a specific purpose: to speed up access by moving it closer to the end user, to improve reliability in the face of network failure, or because network connections are only occasionally available.

SQL Anywhere Studio 9 offers four ways to implement distributed data: proxy tables, Replication Server, SQL Remote, and MobiLink.

SQL Remote and MobiLink both work by occasionally copying data between a single consolidated database and a virtually unlimited number of remote databases. Both products ship with SQL Anywhere Studio 9, and both are fully supported by iAnywhere Solutions. This excerpt only discusses MobiLink, and in particular MobiLink as it is used with ASA remote databases; UltraLite databases can be used with MobiLink but the subject of UltraLite is not covered in this book.

The term "replication" is used to describe what SQL Remote does, as opposed to "synchronization" for MobiLink. The difference is that MobiLink guarantees that the consolidated and remote databases are "in synch" at the point data is transferred, whereas with SQL Remote there are communication delays that prevent that guarantee.

How MobiLink Works
MobiLink software consists of two main components: a client and a server. The client initiates a synchronization session by connecting to a remote database, gathering all the rows changed since the previous synchronization, and uploading those rows to the server. The server then applies the uploaded changes to the consolidated database, selects rows to be downloaded, and sends those rows to the client. Finally, the client applies the downloaded changes to the remote database and sends an acknowledgment back to the server.

Many thousands of remote databases, each with its own copy of the MobiLink client component, may be synchronized with a single consolidated database.

MobiLink is very powerful and very flexible, but not particularly easy to set up. Effective use requires an understanding of its architecture, described here in terms of its main characteristics.

MobiLink is designed for central administration. The original design was oriented toward a mobile workforce with no "remote DBA" available for hands-on maintenance of the remote databases. MobiLink has since been used for large, stationary remote databases but the orientation remains the same: most administrative functions are performed on the consolidated database.

MobiLink supports occasional connections rather than continuous operations. MobiLink synchronization is characterized by high latency or long time lags between data entry and transmission to other databases. Synchronizations are typically run once or twice a day - every few minutes is even possible - and so can be part of an automated schedule, but MobiLink is not intended for continuous real-time replication.

Entire rows are transmitted rather than differences or changes. Multiple changes to the same row will be transmitted as a single, final copy of the row rather than individual updates. This applies to both upload and download although the details are very different.

MobiLink is optimized for large numbers of low-volume synchronizations. This is another result of the orientation toward a mobile workforce. MobiLink can and has been used to transmit millions of rows in a single synchronization but there is a price to be paid. In particular, the entire upload and download streams are each applied as single transactions with single commit operations. A large synchronization can cause concurrency problems at both ends; a giant download might as well be run as a standalone process.

Synchronization is session-based rather than file- or message-based. MobiLink software connects to both the consolidated and remote databases during the synchronization process, and all changes are applied during the session rather than stored and forwarded for later application. At the point the synchronization process reaches completion the two databases are known to be "in synch."

Synchronization is hierarchical rather than peer-to-peer. Changes to be passed from one remote database to another remote database must first be uploaded to the consolidated database in one session, and then downloaded to the other remote database in a later session, rather than passed directly.

MobiLink works with different kinds of consolidated databases. ASA, Sybase ASE, Oracle, Microsoft SQL Server, and IBM DB2 UDB are all officially supported, and other software has been used successfully. The limiting factors are the capabilities of the ODBC drivers and database stored procedure languages.

MobiLink only works with ASA and UltraLite remote databases. This book doesn't cover UltraLite, so only remote databases using ASA are discussed.

The synchronization process is asymmetrical. The MobiLink client and server components use completely different techniques for processing the upload and download streams. In particular, there are four different processes, all using different techniques: the creation of the upload stream by the client component, the application of the upload stream by the server, the creation of the download stream by the server, and the application of the download stream by the client.

The upload stream is automatically constructed from the transaction log. The MobiLink client reads the transaction log to find which rows have been inserted, updated, or deleted since the previous successful synchronization. It then builds the upload stream using the final versions of those rows, and sends them marked as "inserts," "updates," or "deletes." It is this characteristic more than any other that limits MobiLink to ASA and UltraLite remote databases: Proprietary information about the transaction log is required and that is not available for databases like Oracle.

The upload stream is applied to the consolidated database by scripts that you write. You have to write one script for each kind of operation applied to each table - insert, update, and delete - if they are expected, as well as scripts to handle update conflicts if you expect those as well.

The download stream is constructed from the consolidated database by scripts that you write. You have to write one script for each table to select all the rows to be inserted and updated on the remote database, and, if necessary, another script to delete rows from the remote table.

The download stream is automatically filtered to remove any rows that were just uploaded. This makes it easier to write the download scripts because you don't have to worry about excluding those rows to reduce unnecessary network traffic - it's done for you.

The download stream is automatically applied to the remote database. Inserts and updates are not identified as such in the download stream, but are matched against the remote database by primary key: if a row with that key already exists, the downloaded row is applied as an update, otherwise it is inserted. Deleted rows are downloaded separately; they are identified as deletes and are handled as such. This implies that MobiLink requires all tables in the remote database to have primary keys.

By default, the MobiLink client automatically resolves referential integrity violations caused by changes downloaded to the remote database. This automatic resolution sometimes causes downloaded changes to be silently ignored, and sometimes causes existing rows to be deleted as if ON CASCADE DELETE had been specified. The main reason for this behavior is to reduce administrative effort even when mistakes are made.

MobiLink requires you to write a multitude of scripts. For even a simple implementation without a lot of complex business rules affecting synchronization, and without large schema differences between the consolidated and remote databases, writing and testing all the MobiLink scripts is a labor-intensive process. However, it is these scripts that give MobiLink its great power and flexibility; you can use them to solve complex problems and accommodate great differences in database design.

MobiLink synchronization is driven by the remote database schema. MobiLink upload and download scripts are named for tables in the remote database even though the scripts execute on the consolidated database. Uploaded rows are applied to the consolidated database in an order that would preserve referential integrity as it is defined on the remote database, not the consolidated database. In other words, the upload stream is sorted according to the foreign key order on the remote database: Parent rows are inserted first and deleted last, and so on.

MobiLink scripts run on the consolidated database and have no access to the remote database. MobiLink synchronization might be session-based but that doesn't mean your code has access to both databases at the same time; it doesn't. The MobiLink scripts might be named for tables on the remote database but the scripts themselves only have access to tables on the consolidated database. This can be a huge source of initial confusion when the schema is different between the consolidated and remote databases, but once understood it's easy to deal with.

MobiLink scripts may be written in Java, .NET languages like C#, as well as the SQL supported by the consolidated database software. The examples here are all written in SQL for SQL Anywhere 9.

MobiLink does not offer any form of system-wide locking. There's nothing to stop you from inserting two rows with the same primary key on two different remote databases or from applying different updates to the same row on two different databases. These rows will cause problems when they are uploaded to the consolidated database. You must design your application so these errors and conflicts do not occur, or you must write scripts to handle them. In particular, primary key collisions must be avoided.

Failure handling is done at the level of upload and download streams. If processing of the upload stream fails, all the uploaded changes to the consolidated database are rolled back and synchronization stops before the download process begins. If synchronization is reattempted after a failure during upload, the entire upload stream will be reconstructed, and the whole process will be repeated as if it had never been attempted before. You never have to write any special code to handle the reconstruction of the upload stream because the MobiLink client program does it for you.