This resource kit amalgamates several resources and documents to help you get started with the creation of a custom synchronization environment with Oracle Database as the consolidated database. You may use the kit as a reference to individual topics or as a step-by-step guide by using the listed navigation below.
MobiLink is a part of the SQL Anywhere product. It is a session-based synchronization technology for exchanging data among relational databases and other non-relational data sources. With MobiLink, you can synchronize hundreds or even thousands of users with a single enterprise system, yet the server's compact size and the simplicity of its deployment means it can operate in remote sites and multi-user scenarios.
Advanced synchronization logic ensures the transactional integrity of the databases in the event a network connection is lost, and offers sophisticated strategies for the resolution of data change conflicts.
MobiLink provides a high level of integration capabilities. It supports many enterprise databases out of the box, and its object-based data flow API can be used to synchronize with non-relational data sources such as application servers, ERP systems such as SAP, Web services, XML files, or other third party relational databases.
Industry-leading database management and data movement technologies built for frontline environments.
SQL Anywhere® is a comprehensive package providing data management and data exchange technologies that enable the rapid development and deployment of database-powered applications. SQL Anywhere offers enterprise caliber databases that scale from 64-bit servers with thousands of users down to small handheld devices. SQL Anywhere's data exchange technologies extend information in corporate applications and enterprise systems to databases running in mission-critical frontline environments. Design and management tools within SQL Anywhere enable developers to implement and deploy frontline applications and equip administrators to easily manage and support them. The front lines have evolved into a mission-critical computing environment as businesses look for new ways to fuel competitive advantage and growth. Sybase iAnywhere's award-winning solutions have been built from inception to enable frontline applications-whether server, desktop, remote, or mobile-to be just as reliable, secure, and available as those running in the data centers of a traditional enterprise.
"Intuit selected SQL Anywhere as the database to power our QuickBooks Enterprise solutions, significantly improving the speed and scalability of our application. SQL Anywhere provides the performance, ease-of-use and reliability features that our mid-market customers depend on." Pankaj Shukla, VP Product Development, QuickBooks Group
"SQL Anywhere is core to our operation. It provides a platform that works not only in a mobile environment, but also supports a very large database. We can actually provide this state of the art technology not only to the large agencies that have IT staff, but also to the small agencies that may have no IT staff." Michael R. Kramer, PhD, CEO and President, HealthWyse LLC
"We found that development of the data synchronization components was far easier in SQL Anywhere than the other products, and gave us far greater capability at the end of the day." Bill Aumen, Principal, Aumen Consulting
Synchronization is a process of data exchange between MobiLink clients and a synchronization server. During this process, the client must establish and maintain a session with the synchronization server. If successful, the session leaves the remote and consolidated databases in a mutually consistent state.
Synchronization typically begins when a MobiLink remote client opens a connection to a MobiLink synchronization server. During synchronization, the MobiLink client uploads database changes that were made to the remote database since the previous synchronization. Upon receiving this data, the MobiLink synchronization server updates the consolidated database, and then downloads changes on the consolidated database to the remote database.
Synchronizing data from an Oracle database to the frontlines of an enterprise enables mobile employees and those at remote or branch offices to access and manipulate corporate data without requiring a constant network connection. This significantly reduces the costs and technical challenges often associated with network connectivity in frontline environments, and ensures that remote office workers stay productive.
The MobiLink Advantage
MobiLink is a session-based synchronization system that allows two-way synchronization between a main database, called the consolidated database, and many remote databases. The consolidated database, which can be one of several ODBC-compliant databases, holds the master copy of all the data.
MobiLink provides out of the box support for:
SQL Anywhere Server
Oracle (including RAC support)
JMS-based messaging systems and application servers
MobiLink’s synchronization capabilities include:
Bi-directional database synchronization
Synchronize over TCP/IP, HTTP, HTTPS, Palm HotSync, ActiveSync
Subset data by rows and columns
Advanced conflict detection
Programmable conflict resolution in SQL, .NET or Java
There is a number of potential data replication and synchronization options on the market today which makes it extremely hard to determine which technology is the best fit when synchronizing data from a SQL Server database. The following section outlines some of the considerations when choosing a data synchronization technology.
Efficient Data Capture
MobiLink has the ability to capture the changes that occurred in the remote database, directly from the transaction log. The transaction logs give the synchronization system an efficient way of packaging up the changes to be sent to the server. This is accomplished by scanning the transaction log for everything that occurred since the previous synchronization. There is no need to use complex queries over various change capture tables to encapsulate the information.
Multi OS Support
Devices are constantly changing. Many years ago, Palm was the OS of choice for mobile development. Today Windows Mobile Smartphones are becoming increasingly popular. When choosing a synchronization solution it is important to consider one that supports a wide range of devices and operating systems. With MobiLink, the remote databases run on virtually every major device and operating system including Windows Mobile, Palm OS, Windows, Linux, Mac OS X, and many more.
Feature Rich Data Store
With MobiLink, SQL Anywhere is supported as the remote database which gives you all of the enterprise database features you are familiar with and does not require a significant rewrite to your mobile database access code. Furthermore, SQL Anywhere includes a complete database optimizer that allows your application to continue to use complicated database queries and get results in a timely manner.
Graphical user interfaces are very useful when building prototypes quickly. However, once that prototype has been created, an administrator will need the ability to deploy it to large numbers of remote users. With SQL Anywhere all of the steps performed through the graphical user interface have a corresponding executable. This means that every step made by the user interface can be reproduced externally within batch files. Furthermore, all of the statements executed by the interface can be logged in a file. This allows the administrator to reproduce the environment as needed.
Priority Data Synchronization
Priority synchronization gives the user (or mobile application) the ability to independently control when different data subsets are synchronized based on network connectivity. The main advantages of priority synchronization include:
The ability to maximize network bandwidth usage
The synchronization of specific data changes at various times in the day, which can be useful when trying to synchronize data at “off-peak” times to minimize the server performance impact
The optimization of the amount of time synchronization takes to complete, which can be especially important when a user needs to get information as soon as possible
Advanced Conflict Detection and Resolution
MobiLink provides an extensive level of conflict resolution handling. By default, MobiLink will make the last user who made the change win the conflict and the first user to make the change receive the updated information. However, with MobiLink, conflict handling can be customized to provide a variety of different conflict mechanisms. This may include last-in wins for certain tables and first-in wins for other tables. Other times, the business rules may indicate that the values should be merged together.
Remote Schema Upgrades
With MobiLink, tools are embedded directly in the remote database engine, which allows for schema changes. During synchronization, special schema upgrade stored procedures can be executed within the database to make modifications to the remote database. Schema changes do not require a redistribution of data to the remote device, and they can also be automated. Furthermore, MobiLink includes the concept of script versioning that allows an administrator to slowly deploy schema upgrades to small groups of users while maintaining both old and new users on the same MobiLink server.
Extensive Server to Remote Table Mapping
When a query joining multiple tables is executed on an enterprise database server there will typically not be a noticeable performance impact. However, on remote systems the performance effect may be much more noticeable. As such, to increase application performance, an administrator may wish to join two or more tables on the server side into a single table on the remote side. This technique is called data denormalization. MobiLink has an extensive server to remote table mapping system that allows for this type of denormalization. Furthermore, any change that is made to the denormalized table can be tracked and applied to the (normalized) central database tables. This allows the schemas between the consolidated and the remote databases to be different.
Combining Multiple Data Sources
In many instances, the data that needs to be sent to the remote user will be scattered over multiple corporate databases. MobiLink has the ability to map synchronization logic to a number of data sources. This could include mappings from a variety of relational data sources such as Sybase, Oracle, Microsoft, or IBM, as well as application servers such as SAP, Siebel, and Service Oriented Architectures (SOA) such as Web Services. The ability to map different data sources can also become useful in the event of a corporate merger. Rather than having to immediately merge data from two different database systems, MobiLink can send some data from one database to one group of users and other data to other groups of users.
Integration with Existing Business Logic
During any point in the synchronization process, MobiLink has the ability to call customized .NET or Java logic. This means that prior to applying data changes to the central data source, data can be filtered within the corporate business logic, which may exist in a separate application server. This also allows for customization of synchronization events such as user authentication.
Pre-population of Remote Database
In circumstances where a remote database system requires a large volume of data, it is inefficient (and often expensive) to require the user to download all of their data during the initial synchronization. It is far more efficient to pre-populate the user database with information prior to deployment to ensure a user's first synchronization will only contain the “new” changes. As another option, an administrator may wish to create one template database that contains common information (such as product price lists). This template database can be used by any remote user. Upon synchronization, the user will only download the unique data that applies to them.
Efficient Data Transfer
Since the MobiLink server manages the mappings of the remote and central database tables, there is no need to send column names, data types, or SQL statements. It only requires the actual row data, table name, and before image. The before image is the data as it existed prior to being updated. This before image is used in the event of a conflict. Tests have shown that this efficiency can greatly reduce the overall size of the data stream.
Push synchronization allows information to be pushed from the central database server to remote databases. This capability is critical when important data changes are made on the server and a single user or group of users need to be notified. With MobiLink, synchronization can be initiated remotely as well as from the server. MobiLink can either send out alerts to the users effected by the data changes or physically initiate synchronization on the remote to download the set of data updates. In many cases a user may not even realize that their local database is being updated. Furthermore, the server has the ability to track the status and location of the remote. In the event that a user moves from one location to another, the server can track the users changing address and queue notifications until the user comes back into coverage.
User Based Subscriptions
MobiLink gives the administrator a wide range of options when configuring the information that is downloaded to the remote users. In most cases they will simply partition the data based on column values (such as where State is equal to NY). In other cases an administrator may wish to let the remote user choose which information they receive. This can be useful when a remote user wishes to subscribe to a specific set of information. Similarly, when the user no longer needs the information they can simply unsubscribe from it, removing the data from the local database. With MobiLink all of this is easily accomplished through the use of subscription tables and customized synchronization scripts.
Creating a System DSN
In order to easily connect to the Oracle database, create a User ODBC Data Source Name to refer to it. The following video demonstrates the process of creating a new User DSN. More detailed steps are available in the Whitepapers section of the right sidebar.
Creating a Synchronization Model
Sybase Central includes the MobiLink plug-in for creating, administrating, and deploying a synchronization model. A synchronization model defines what is synchronized between both consolidated and remote databases and how this information is exchanged. More detailed steps are available in the Whitepapers section of the right sidebar.
Deploying the Synchronization Model
The MobiLink plug-in can automatically deploy the synchronization model to the Oracle database. It can also create a new remote database for synchronization, as well as create scripts for starting up an instance of the MobiLink server. More detailed steps are available in the Whitepapers section of the right sidebar.
Creating a Compatible Remote Database
The remote database that was generated automatically by the MobiLink plug-in is ready for synchronization right away. Typically, however, realistic projects will require that the remote database schema match the consolidated database schema more closely. Thus, the following sections detail type compatibility requirements, primary key uniqueness, and special columns. It is important to note that since the remote database is a SQL Anywhere database, a wide array of rich database features are available, such as stored procedures and aggregate functions.
Data Type Compatibility
MobiLink software is designed for use, right out of the box, with different types of data sources, including Oracle Database. However, there are certain cases where schema changes may be necessary to satisfy type compatibility between the consolidated and remote databases.
Oracle allows user-defined data types, which are not directly compatible with the data types that are available when creating a new remote database schema from the MobiLink Synchronization Script Wizard. Manually creating the appropriate remote database schema or converting the types to types SQL Anywhere can recognize will circumvent the problem. More information about data type mappings can be found in the MobiLink Data Mappings Between Remote and Consolidated Databases section of the MobiLink - Server Administration documentation. An online version of this document can be found here: http://dcx.sybase.com/dcx_home.php. Otherwise, if using the wizard is desired, the incompatible data type columns will need to be dropped from the consolidated database. For the purposes of this demonstration and simplicity's sake, the incompatible data type columns will be dropped.
For the following demonstration with Oracle's Order Entry sample database, drop the user-defined type columns using the following SQL commands in SQL Plus.
ALTER TABLE customers DROP COLUMN cust_address;
ALTER TABLE customers DROP COLUMN phone_numbers;
ALTER TABLE customers DROP COLUMN cust_geo_location;
ALTER TABLE product_information DROP COLUMN warranty_period;
Ensuring Primary Key Uniqueness
Because MobiLink enables the disconnected use of a database, maintaining primary key uniqueness is less trivial than in the case where all users maintain a connection to the database. Disconnected users make changes to their own copy of the remote database, all of which are eventually synchronized back to the consolidated database. As a result, it is essential that these users do not inadvertently use the same primary keys to indentify their table entries. There are several methods of maintaining primary key uniqueness among many remote databases, including composite keys, primary key pools, and the global auto increment functionality.
Oracle Database uses sequences to achieve much the same functionality as SQL Anywhere's Global Autoincrement.
CREATE SEQUENCE seq_example_id INCREMENT BY 1 START WITH 1000001 MAXVALUE 1001000000;
Now records can be entered using the following command:
INSERT INTO Example (example_id, col_1, col_2, col_3) VALUES (seq_example_id.NEXTVAL, 1, 2, 3);
It is usually unnecessary and inefficient to delete and re-insert all rows of all tables when synchronizing. To reduce synchronization time and network access costs, MobiLink allows synchronizations to only include changes that have occurred since the last synchronization. For this purpose, it is necessary to keep track of when every row is modified. This can be done using multiple shadow tables or a timestamp column.
Typically shadow tables are used to avoid changes to the existing schema. The primary key values of modified or added columns are copied into secondary (shadow) tables. Shadow tables are generally named the same as the original table, but suffixed with _mod or _del depending on the type of shadow table. Upon synchronization, changes are retrieved by examining the shadow tables. The advantage is that this does not change the existing schema. In order to avoid conflicts, it should be ensured that primary keys are not reused after a deletion.
Alternatively, it is possible to keep track of time of last modification or time of insertion directly in a dedicated column. A "last modified" or "ModifiedDate" column can be added to all tables with an automatic default to the time of modification (default current timestamp). The advantage to this is that no additional shadow tables will need to be created or maintained.
If using the MobiLink plugin and wizards to deploy your synchronization model, a trigger will be created on the consolidated database to keep the shadow tables or timestamp column up-to-date every time a modification occurs on the original table.
Logical Delete Columns
One of the features of using MobiLink synchronization is that deletions are synchronized if desired such that deletions on the consolidated databases, any remote databases, or both are propagated to the other. However, it will be necessary to keep track of deletions. This can be done either by use of a shadow table, or by using logical deletes.
Typically, a shadow table is used to keep track of which rows have been deleted. A row that has been deleted from a table will be deleted as expected, and a copy of the row's primary key will be kept in a secondary (shadow) table. Shadow tables generally have the same name as the original tables, but are suffixed with _del. Thus, when synchronization occurs, the list of rows that were deleted is available. The benefit to this approach is that this does not change the existing schema or dependant queries. However, to avoid unwanted deletions, primary keys should not be allowed to be reused after deletion.
Alternatively, a logical delete column can be used. A logical delete column is simply a bit or flag that keeps track of whether the row has been "marked for deletion". Thus, when synchronization occurs, the list of rows that were deleted is available. This method is typically not used because it requires changes to the existing schema, as well as all dependant queries (add an 'WHERE' clause). In addition, this method will require more free space since rows will never actually get deleted, and all rows (regardless of whether they have been deleted) will gain an additional bit/flag.
If using the MobiLink plugin and wizards to deploy your synchronization model, a trigger will be created on the consolidated database to keep the shadow table or logical delete column up-to-date every time a deletion occurs on the original table.
Now that the synchronization model has been fully developed and deployed, synchronization is as simple as following these steps:
Start the MobiLink Server at the consolidated server
Run the MobiLink client program (DBMLSync) at the remote
Make changes as desired to either the consolidated or remote database
Run DBMLSync again at the remote
Verify that the changes have been synchronized to the other end
Oracle Order Entry Demo
In order to better showcase the use of MobiLink and its synchronization capabilities with an Oracle consolidated database, a demo application has been provided, built on one of the sample databases included with the Oracle Database product. The Order Entry sample provides a typical use-case. A central consolidated database exists with customer, order, and product information, which the front line sales representatives use to make their sale. The orders are entered into the remote databases, carried by the representatives, and when appropriate synchronized to the consolidated database.
A remote client application has been provided, along with the Visual Studio C# source code. Click here to download and please be sure to extract using folder names. The demo also provides documentation on how to run the remote client application. In order to ensure complete functionality, please see the whitepaper Mobilizing an Oracle Database regarding the set up of the Oracle consolidated database as well as more details on MobiLink synchronization models and how to create them. It should be noted that minimal manual changes were required to the consolidated database in order to preserve full compatibility with MobiLink and SQL Anywhere.
For a better understanding and more in-depth tutorials, please see the aforementioned whitepaper. The following topics are a subset of the information available in that document.
Configuring the MobiLink Server For Oracle's Order Entry Database
The Order Entry sample database that is included with Oracle Database uses row-level triggers. By default the MobiLink server is set up to synchronize up to 10 row inserts, updates, or deletes in a block, often improving synchronization performance. However, this interferes with certain types of triggers, such as the row-level trigger listed above. To solve this problem, the MobiLink server should be instructed to synchronize changes a single row at a time. The '-s' option is used to specify the number of rows synchronized in each block. The Deploy Synchronization Model wizard allows the user to specify this option in the MobiLink Server Options screen. More details can be found both in the Mobilizing an Oracle Database whitepaper as well as the Deploying the Synchronization Model section of this resource kit.
The Numeric Data Type
Oracle Database uses the 'Numeric' data type to identify all numeric data. The MobiLink synchronization wizard converts these numeric data types into the 'Decimal' data type, which preserves the size and precision of the Numeric type. However, for certain behaviors in SQL Anywhere, such as autoincrement which requires an integer data type, this will not be acceptable. Therefore, it is necessary to change some of the column types from their default type. Specifically, ID columns, such as ORDER_ID must be changed to one of the integer data types in order for global autoincrement to function.
Autoincrement Implementation for Demo
In the Ensuring Primary Key Uniqueness section and associated whitepaper, a general overview of maintaining primary keys across the consolidated Oracle and remote databases was presented. For the purposes of the Order Entry demo application, a more specific implementation follows, to give a concrete use-case.
The consolidated Order Entry database includes an existing sequence for the Orders.ORDER_ID field. In order to give the remote databases some room for their primary keys, reduce the maximum value of the sequence to something more manageable, such as 1,000,000. For the purpose of the demo, assume the remotes may enter up to 100,000 orders. This means with a global autoincrement partition size of 100,000, the smallest database identifier that will not interfere with the sequence on the consolidated is 10. The consolidated can now enter up to 1,000,000 orders and the first remote database, with database identifier 10, can enter orders from 1,000,001 to 1,100,000.
Of course it is always preferable to create partition sizes that are too large as opposed to too small on both the consolidated and remote databases. Keep the level of potential data growth in mind when creating the partition sizes. The values used in this example were for simplicity and may not be appropriate for other databases and circumstances.
Mobilizing an Oracle Database – This whitepaper walks you through the mobilization of an Oracle database. Using the Order Entry and Human Relations sample schemas that are provided with the Oracle installation, the paper demonstrates how to extend these Oracle databases to mobile devices using SQL Anywhere.
Chick–fil–A – Chick–fil–A selected SQL Anywhere and Replication Server to improve efficiency and increase profitability of operations. The company has enhanced its ability to respond to new opportunities and changes in business climate, to remain competitive, and has reaped savings of more than $1 million per year.
Hyundai Department Stores – Hyundai deployed a wireless, handheld point–of–sale (POS) system powered by SQL Anywhere that eliminates the need for traditional stationary registers.
Hyundai–Kia Automotive Group – Hyundai–Kia Motor Company integrated SQL Anywhere and MobiLink into its sales branch operation system, deployed to 9,000 Kia Motor employees. Sales agents have full access to business–critical information, ranging from customer history to the latest delivery status reports, and can provide quotations anytime, anywhere.
SQL Anywhere – Learn more about SQL Anywhere and MobiLink technologies.
Free Developer Edition – Download a copy of SQL Anywhere Developer Edition and start implementing your solution.
CodeXchange – Freely exchange code samples, utilities, scripts and other tools.
Support & Downloads – Should you have further questions, or require assitance, it would be iAnywhere's pleasure to accomodate you. Simply visit our Support & Downloads section for additional information.
Find Out More – If you are interested in finding out more about how MobiLink and the rest of SQL Anywhere can empower your frontline business, please contact us. It will be our pleasure to find a match between your needs and our solutions. Please call us at 1-800-801-2069, or email us to find out more.