Use SyncML for Heterogeneous Database Replication

Http://www.csai.cn Author: Jayanthi Source: Saidi

Developing distributed applicationsProgramAvailability and performance are the main considerations. However, using data storage to solve these problems may cause data synchronization between heterogeneous data storage. In this article, Jayanthi suryanarayana and Neil tunnicliffe will provide a solution to achieve universal Database Data Replication Using JDBC and SyncML standards.

In Design Availability and performance must be considered for distributed applications. The general solution is to include data storage on the client system. Generally Resources are limited, and the client needs a lightweight data storage. This method poses a challenge for data synchronization between heterogeneous data storage. One solution to this problem is to use Java-based methods to replicate heterogeneous databases using JDBC and SyncML standards.

Overview

Replication refers to the process of copying all or part of the database between two environments. To maintain consistency, the changes made to the source database are propagated to the copied database. Replication can be unidirectional or bidirectional. Bidirectional replication may be more difficult because changes made to any database may produce inconsistent data. When these changes are transmitted between two databases, a policy is required to mediate these differences to maintain consistency.

Id Processing

As a basic requirement for replication, We need to uniquely identify each data unit to be copied. For bidirectional replication, A ing scheme that identifies the corresponding data units between databases is also required. Various solutions can be used based on replication requirements. For one-way replication, the primary database can require IDs to be generated for data units. For bidirectional replication, the ing scheme must be defined based on the Application ID. The ID generation and ing schemes can be mutually exclusive ranges of IDs used by each database. In this range, IDS exactly match. More complex examples may include ID generation. Service Or database-specific solutions. In this case, Id ing must be maintained between databases.

Change Detection

The next step in the replication process is to find out which data units have been changed. In relational databases, you can use additional fields to record the status and status change timestamps of data units. Alternatively, you can use triggers to automate part of the change detection process. The insert/update/delete trigger appended to a table can detect changes made to the data unit and record these changes in the change log table. In this way, you can use the change log table to determine the changes made to the data unit at any specified time.

Copy

The target of replication is to generate multiple consistent copies of Public datasets. To achieve this, you must exchange the changes detected in each database and make them consistent. Here, you are facing a design challenge. You can make the following options:

The changed data format to be exchanged.
Transmission mechanisms, such as HTTP, FTP, and JMS, depend on application requirements.
Exchange protocols between entities.
Conflict Detection and resolution between data units.
SyncML defines a synchronization protocol that uses XML messages to add, delete, and modify data units. It also allows security information exchange so that the node can perform authentication.
Sync4j is an open-source Java implementation of SyncML. It supports multiple transmission mechanisms. Sync4j also provides a Conflict Detection Framework and resolution strategy based on application requirements.

Example

This document describes a simple data replication example. This example shows how to copy two-way customer data records between two heterogeneous databases. Each database contains a customer table with a similar structure. Use a unique customer ID to identify each copied record. We have added two columns to the table to record the status and last status change timestamp of each record. In this example, the state and timestamp columns must be filled by an application or manually. As mentioned above, a trigger is an appropriate solution to automatically fill the changed data in the Change Record table. However, the database used in this example currently has limited trigger capabilities, so we choose not to use triggers to implement this task. In addition, this instance does not include a two-way replication Conflict Resolution Policy. Therefore, conflicts may cause the replication process to fail.

We need to configure the server environment for running the sync4j server in the Tomcat servlet container, and use Derby as the source database. In addition, you must configure the client environment, use HSQLDB as the target database, and run the sync4j client program on it. (In this example, we assume that j2se SDK and ant have been installed .)

Server Installation

To run the sample application, you must install three open source components on the server: tomcat, sync4j server, and database engine. To install Tomcat, sync4j, and Derby, follow these steps to install the three main server components (see references for a download link) and download Tomcat 5.0.29. In this article, Tomcat is used to represent the installation directory of Tomcat. Set the environment variable j2ee_home to Tomcat. Download and expand sync4j server 4.0.2. In this article, sync4j is used to represent the installation directory of the sync4j server. Download and install derby. In this article, Derby is used to represent the Derby installation directory.

Configuration example application

Next, you need to configure an exampleCode. Click the code icon at the top or bottom of this article to download j-sync4j.zip. Decompress the file to a directory that contains the client code, server code, and SQL script used in this example. In this article, testsync is used to represent this directory.

Set the JDBC Driver Class path

Set the JDBC Driver Class path: Copy db2jcc. jar and db2jcc_license_c.jar from Derby \ Lib to Tomcat \ common \ Lib and sync4j \ Lib respectively. The Derby installation script for the sync4j server. Next, move the directory testsync \ Derby to sync4j \ Default \ SQL. The provided scripts are similar to other database scripts provided by the sync4j server, but must be slightly modified for derby. To build the sync4j server module, follow these steps:

Set syncserver_dir in testsync \ module \ build. BAT to point to sync4j.
Run testsync \ module \ build. bat.
Configure the sync4j Server

To configure sync4j, follow these steps:
Create a database called sync4jdb in Derby. The JDBC settings are described in the Code in step 3. These settings assume that a database called sync4jdb in Derby has the username sync4j and password sync4j.
Use the IJ tool that comes with Derby to create and set this database (for details about the Derby tool, see references ).
Set the properties in sync4j \ install. properties as follows (replace derby with the actual path in the JDBC setting ):

DBMS = Derby JDBC. classpath = Derby/lib/db2jcc. jar; Derby/lib/db2jcc_license_c.jar; JDBC. driver = com. IBM. db2.jcc. db2driver JDBC. url = JDBC: Derby: Net: // localhost: 1527/"derby/sync4jdb" JDBC. user = sync4j JDBC. password = sync4j modules-to-install = Foundation-1.0, pdi-1.1, testsync-1.0

Set the Derby Database

Run Derby \ frameworks \ networkserver \ bin \ startnetworkserver. BAT to start Derby as a network server. For more information about starting Derby as a network server, see the Derby manual. Run testsync \ create_table_server. SQL with the IJ tool and create customer1 in the derby database. To install the sync4j server for Tomcat and the example module, install the sync4j server for the Tomcat server and set the sync4j database, follow these steps:

Run the following command from sync4j: sync4j \ bin \ install. CMD tomcat. When a command prompt is given, Press Y to rebuild the database of the testsync-1.0 module. Run sync4j \ bin \ sync4j-tomcat.cmd from sync4j and then start the Tomcat server. Pay attention to the server settings. Now you can install the client.

Client installation

You must install the database engine HSQLDB on the client. Then install and configure the sample code. To set the client application and database, follow these steps to set the client application database:

Download and decompress the hsql database. In this article, HSQLDB is used as the installation directory of the hsql database. Start hsql as a network server with a database named test. Run testsync \ create_table_client. SQL to create a similar table (customer2) IN THE hsql database ). Copy the hsql JDBC driver HSQLDB \ Lib \ HSQLDB. jar to testsync \ Client \ Lib. Set the JDBC attribute of the client in testsync \ Client \ config \ SPDs \ sources \ testsync. properties, as shown below:

Jdbcdriver = org. HSQLDB. jdbcdriver urlconnection = JDBC: HSQLDB: hsql: // localhost/test

Set the user name and password correctly. Then, run testsync \ Client \ build. BAT to build the client application. Run the client application to insert records to the customer1 table in the derby database. For example:

Insert into mermer1 (userid, password, cl_op_type, clo_op_time) values ('user1', 'pass1', 'n', current_timestamp );

Insert records to the customer2 table in the hsql database (make sure that the userid value is different from the value recorded in customer1 ). For example:

Insert into mermer2 (userid, password, cl_op_type, clo_op_time) values ('user2', 'pass2', 'n', curtime ());

Run testsync \ Client \ output \ testsync. bat. Customer1 should contain records in the mer2 table just inserted, while the customer2 table should contain records from the customer1 table.

Conclusion

In this article, we discuss some important concepts and problems involved in designing a general database replication solution. When designing complex distributed applications, the availability of embedded Java databases with few resources is a good choice for you, and it also allows you to use heterogeneous databases. A good understanding of the replication process and related issues helps us improve the design of such systems. We encourage you to extend this example to use conflict resolution policies to process bidirectional replication and use records to change the automatic records of data.