---

 

Integrating Remote Workgroups & Occasionally Connected Devices with the Enterprise— The Sybase Strategy

 

Purpose

This whitepaper examines the differences between message-based and session-based synchronization, and explores how these methods are employed by Sybase’s SQL Remote and MobiLink technologies. SQL Remote and MobiLink are included in Sybase’s SQL Anywhere Studio, the industry-leading mobile database technology built to enable the design and delivery of critical information to workgroup, mobile and embedded database systems. While both SQL Remote and MobiLink technologies enable two-way data updates between databases, each method has unique strengths and capabilities that make it suitable for certain circumstances. This paper describes situations where SQL Remote or MobiLink is better suited to provide a technical solution, helping customers choose the most appropriate synchronization method for their environment.

Contents

Why Distribute Corporate Data?

Overview of Message-based and Session-based synchronization

Sybase Synchronization Products

Introduction to SQL Remote synchronization

Introduction to MobiLink synchronization

Benefits of SQL Remote

Benefits of MobiLink

Chart: Features of SQL Remote and MobiLink

Why Distribute Corporate Data?

At one time, corporate data was stored in one central location in a mainframe. Remote users accessed the information they needed by establishing direct connections to the central site, or by requesting printed reports from headquarters. The direct connections could be expensive and limited in number, while the reports were inflexible and not timely.

Distributed data systems brought inexpensive and powerful computing resources to all corners of a company. The ability to share corporate information effectively using these new resources has become an important competitive advantage for many organizations.

The main benefit that follows from data distribution and synchronization is the increased access to company data; businesses can hold all corporate data in a consolidated database at headquarters, while giving remote offices local access to the data they need most through the data distribution system. This local access translates into increased efficiency for employees; network travel time is reduced when data is locally accessible, resulting in faster system response time and decreased connection costs. Instead of having to be continuously connected to the network in order to access data, employees can work in an occasionally connected environment where data is available locally, connecting only periodically to upload and download changes made to data. Consider this example:

A large retailer with hundreds of locations needs a method to efficiently manage their financial reporting system across the entire enterprise. Instead of dealing with cumbersome and time-consuming paperwork, the retailer seeks to develop a bi-directional automated electronic reporting process that updates inventory, ordering, accounting, and pricing information and shares this information with all locations.

To accomplish this, the company installs a database system with bi-directional synchronization capabilities. This technology enables employees in the distributed locations to have access to relevant data in the consolidated database at headquarters. Employees make changes to financial data as necessary, then synchronize the changed data with the rest of the enterprise. Modifications made at headquarters are also synchronized out to all locations several times a day, ensuring accurate and efficient financial reporting statistics.

With this synchronization technology, the retailer has the power to distribute accurate and timely information across the enterprise, keeping even the most remote locations in synch with the rest of the company.

 

Overview of Message-based and Session-based Synchronization

Synchronization is the transaction-based process that tracks changes made to a set of databases and adjusts these databases so that they all contain the most up-to-date information. Synchronization occurs when two or more databases are reconciled so that each contains the same, most current information. Two different methods of synchronization— message-based and session-based— are described below.

Message-based synchronization

Message-based (also known as "store and forward") technology requires no direct connection between the remote computer and the consolidated database, using a messaging system such as e-mail to transfer changed data to and from the company database. A message-based technology allows users to make changes to data anywhere and then synchronize those changes back to the main database via e-mail when it is convenient to connect.

A key benefit of message-based synchronization is that the technology allows users to replicate data without having to be connected to a network. Messages created during data updates can be kept on the hard drive of the remote computer or saved to a floppy diskette until the user has an opportunity to connect with the message system. The messages containing the data changes are then uploaded to and/or downloaded from the message system for synchronization. This message-based synchronization method enables users to do business anywhere, connecting only occasionally to a messaging system. This kind of synchronization is thus ideal for situations where telecommunications systems are not readily available. By using message-based synchronization, a user in a remote area that has no connection system can make changes to data, save the messages to a disk, and then, for example, courier the information to a location that has connection capabilities. Because no direct connection to a network is required, message-based synchronization is also suitable for companies that do not allow outside direct connections.

Session-based synchronization

Session-based synchronization, unlike the message-based method, is the process in which a remote database directly connects to the consolidated database site via a TCP/IP or other direct connection. The remote database establishes and maintains the connection until the synchronization session is closed. Session-based technology enables remote users to work with and make changes to data at their remote sites, and then connect with the company network to reconcile their data with the rest of the network.

Because the remote database is connected to the consolidated database, at the end of the synchronization session there is the implicit guarantee that the remote database will have the most up-to-date information. This makes session-based synchronization appropriate for companies that need to ensure that their remote databases contain the most current information when they synchronize.

Sybase Synchronization Products

Sybase’s SQL Anywhere Studio product includes two synchronization technologies; SQL Remote, a message-based system, and MobiLink, a session-based system. These two technologies allow customers the flexibility to choose the most appropriate solution for their environment.

 

SQL Remote

Sybase SQL Remote uses message-based synchronization to update databases. SQL Remote connects with SQL Anywhere remote databases and SQL Anywhere or Adaptive Server Enterprise (ASE) consolidated databases to achieve synchronization. SQL Remote synchronization technology is designed to simplify administration duties; all maintenance and administrative functions are carried out at the consolidated database. This enables a company to have a large mobile workforce with many unique databases without having to individually maintain each remote database. In addition, SQL Remote operates invisibly to the end user.

In order to run efficiently, SQL Remote uses memory economically. This allows companies to use SQL Remote on existing remote computers without having to invest heavily in new hardware. Therefore, synchronization is possible to and from remote computers that have limited space; only directly relevant data is replicated from the consolidated database to the remote users.

Users access corporate data through a "publish and subscribe" process. All data is arranged in publications at the consolidated database site. These publications consist of articles (i.e., the contents or a subset of the contents of a data table). The database administrator at the consolidated site then subscribes remote users to the publications they require to do business.

If the same section, or row, of data is updated at more than one site, a conflict can arise. SQL Remote uses conflict triggers to resolve this and to ensure that data is kept consistent throughout the company. Because the "master" copy of data is stored in the consolidated database, all conflict resolution is carried out at the consolidated site.

SQL Remote Synchronization Process

 

 

Below are the main steps involved in the SQL Remote synchronization process:

1. At each database participating in synchronization, consolidated and remote, there is a Message Agent and a transaction log that manage synchronization. The transaction log records and stores all committed changes to data.
2. Periodically, the Message Agent on the consolidated database scans the transaction log and packages all the committed transactions made to each publication (section of data) into messages, sending the relevant changes to remote users who are subscribed to those publications. The Message Agent sends these changes using a messaging system. SQL Remote supports MAPI, VIM, and SMTP e-mail systems, as well as FTP and FILE.
3. The Message Agent at the remote site accepts the messages sent from the consolidated database and applies the transactions to the remote database.
4. At any time, the remote user can run the Message Agent to package the transactions made at the remote site into messages and send them back to the consolidated database.
5. The Message Agent at the consolidated site processes the messages from the remote site and applies the transactions to the consolidated database.

 

Introduction to MobiLink

MobiLink is Sybase’s session-based synchronization technology that enables remote users to seamlessly synchronize data directly to any major enterprise database. The MobiLink synchronization logic is built into remote and handheld applications, keeping track of any changes made to the database.

MobiLink synchronization employs Open Database Connectivity (ODBC) to update and query the consolidated database; this enables users to synchronize their remote database with any consolidated database, such as Sybase, Oracle, Microsoft, or IBM. For synchronization to remote databases, MobiLink connects to UltraLite remote databases.

For synchronization to a remote database, MobiLink automatically keeps track of changes made to data since the last successful synchronization and uploads these changes. In the consolidated database, MobiLink uses SQL scripts written in the native SQL of the consolidated database to determine what data to download from the consolidated database.

A variety of synchronization techniques can be used depending on how regularly information in the database will change. The timestamp-based technique only synchronizes records that have changed since the last time synchronization occurred. This method is used in situations where data rarely changes. The snapshot method synchronizes all data every time the database is synchronized; this method is ideal for companies whose data changes daily. Each table in the database can use a different technique to achieve synchronization.

In order to resolve data conflicts, MobiLink compares all changed data with existing information. If there is a conflict, MobiLink runs a "resolve conflict" script that uses the defined business rules to determine what information is correct, and then enters that data into the database.

MobiLink Synchronization Process

 

 

Session-based synchronization with MobiLink involves four main steps.

1. The remote application prepares and sends an upload stream to the synchronization server containing a list of all data rows that have been updated, inserted, or deleted since the last time the database was synchronized.
2. The synchronization server prepares and sends a download stream containing data changes from the consolidated database to the remote application.
3. The remote application then sends a confirmation message back to the synchronization server verifying that the changes have been made to the data. The transaction is recorded in the consolidated database.
4. The synchronization server sends the remote application a message confirming that synchronization is complete. The application receives this message and closes the connection to the server.

 

 

 

 

Benefits of SQL Remote Synchronization

Network Security

SQL Remote allows messages to be encoded for transport over the various message links it supports (FILE, FTP, MAPI, VIM, SMTP). Customers can also provide their own custom encoding system; this gives administrators the flexibility and security to ensure only their company can process the messages from SQL Remote.

Depending on security requirements, some companies do not allow outside, remote access (i.e., a direct TCP/IP connection) to their network. SQL Remote solves these security issues with its message-based synchronization system. With SQL Remote, messages can be created in a "staging" area outside the main network. The network administrator for the company has full control over when messages are moved into the main network; this maintains the security of the company network while still allowing for two-way synchronization of data.

 

Stable Transaction Ordering

SQL Remote technology maintains the original order of changes replicated to or from the consolidated database. For some database applications, the order in which changes are replicated is crucial in sustaining the integrity of the data. To maintain this order, SQL Remote scans the database transaction log and replicates the changes to the database being replicated to in exactly the same order. For example, if a user on a remote laptop performs an INSERT, followed by an UPDATE, then DELETES the record, SQL Remote replicates all three statements to the consolidated database in the same order, instead of simply deleting the record without making the first two changes.

Ease of Use

SQL Remote is designed to be easy to use for both database administrators and end users. SQL Remote enables database administrators to set up and manage large numbers of remote users easily. Database administrators can create basic publications simply by dragging and dropping files in the Windows environment. SQL Remote helps remote users leverage corporate data outside of the office, enabling them to replicate on demand. When remote users retrieve their database synchronization messages, they do so simply by accessing their email and downloading the new mail. Email and database synchronization messages are all received in the same simple operation.

 

Supports large remote workforce

SQL Remote is ideal for companies with large remote workforces. The SQL Remote transaction log stores all database changes made by remote users. When the transaction log is scanned, it generates messages all at once for every remote user who has uploaded changes to the log; this means that changes from a large number of remote users can be replicated in just one sweep of the transaction log. Because users transfer synchronization messages through their messaging system instead of connecting directly to the central database, companies can support a large workforce solely through their email system. The size and expense of the central database hardware is also reduced; companies do not need to worry about supporting a large number of direct remote connections to the consolidated database.

Benefits of MobiLink Synchronization

Heterogeneous Interoperability

MobiLink synchronization is designed to use Open Database Connectivity (ODBC) to update and query the consolidated database. This allows users to synchronize with any major database, including Sybase, Oracle, Microsoft, and IBM. Therefore, when customers use MobiLink, they can leverage their existing backend database as their consolidated database.

Recoverability

If there is a communication failure during synchronization, MobiLink will continue synchronization on the next attempt. This ensures that transactions that were initiated but not finished before the failure are completed and fully accounted for.

 

Flexibility

MobiLink synchronization provides customers with a very flexible solution. The MobiLink synchronization server uses events to fire scripts during synchronization. Customers can choose to perform actions at many different points during the synchronization process to allow increased versatility in a company's design. The actions can be changed at any time, without affecting existing remote users.

Users also have flexibility in how conflict resolution is set up (i.e., MobiLink can handle insert, update, and delete conflicts, while other database technologies may only deal with update conflicts).

Customized synchronization

MobiLink synchronization allows for scripts to be written that specify exactly what data gets synchronized. These scripts are created in the native language of the backend database. Most synchronization scripts follow a common form, so once the customer is familiar with the language of the backend system, customizing these scripts is not difficult. If changes to data need to be re-sent to the consolidated database or to a remote site, the synchronization scripts can specify exactly which rows will be synchronized, without having to re-enter the transaction. MobiLink also enables customers to customize synchronization for particular users; different remote users can receive customized rows of synchronized data. This feature enables companies to put data where it is most needed, while not filling up a remote user’s database with rarely used information.

 

Features of SQL Remote and MobiLink

	SQL Remote	MobiLink
Message-based	ü
Session-based		ü
Two-way synchronization	ü	ü
Connects to any ODBC database	Requires middleware	ü
Direct Network Connection required		ü
Supports remote workforce	ü	ü
Easy to use/administer	ü	ü
Customizable		ü
Transaction Ordering	ü
Flexible design		ü