Introduction

Data synchronization has become an important part of many corporate information systems. Synchronizing high availability databases and setting up synchronization systems without single points of failure are two challenges that organizations face as they bring synchronization systems to the 24/7 requirements of modern data systems.

Data Synchronization

In data synchronization environments, there is a transport mechanism to facilitate data transfer from the remote database to the consolidated database and vice-versa.

Each database management solution has different mechanisms to resolve data synchronization. Sybase iAnywhere’s MobiLink Synchronization software provides a highly scalable session-based synchronization system that allows bi-directional synchronization between a main database, called the consolidated database, and many remote databases. The consolidated database can be one of several ODBC-compliant databases, including SQL Anywhere, Sybase Adaptive Server Enterprise, Oracle, Microsoft SQL Server, and IBM DB2.

To create a synchronization system without a single point of failure, redundancy must be built in to each component of the system.

Database Mirroring

As corporations move towards always available data and occasionally connected applications, it is increasingly important to ensure enterprise database management systems remain functional and online in 24/7 environments. Database mirroring is an availability and disaster-recovery technology for database management systems to ensure data and data synchronization is available anytime.

A database mirroring system is a configuration of either two or three database servers, each running on separate computers, which cooperate to maintain copies of a database. Database mirroring consists of a primary server, mirror server, and arbiter server. The primary server and mirror server each maintain a copy of the database files while the arbiter server helps determine which of the other two servers can take ownership of the database. The arbiter server does not maintain a copy of the database.

High Availability for Consolidated Database

Database mirroring builds high availability into the consolidated database. The dotted line in figure 4 represents the failover of the synchronization server if a role switch occurs in the database mirroring environment.

High Availability for Synchronization Servers

Configuring a database mirroring environment still leaves a single point a failure in the data synchronization system. Figure 4 shows a single point of failure with the synchronization server. Using multiple synchronization servers and load balancers (failover cluster) can remove the single point of failure in the system. The dotted lines in figure 5 represent the failover of the synchronization server if a role switch occurs in the database mirroring environment or if there is a failure of synchronization server #1.

With the introduction of load balancers (failover cluster) and multiple synchronization servers, each component of the data synchronization environment is redundant. The data synchronization system will now function even if hardware or software failures occur in the environment.

High Availability for Remote Databases

A database mirroring environment can be used for configuring the remote database system, but initiating data synchronization is more complex. Data synchronization is initiated when the synchronization client establishes a connection with the remote database and synchronization server. However, in a database mirroring environment it is not always known which database is acting as the primary server.

The synchronization client needs to establish a connection with the primary server (or mirror server if role switch has occurred), so logically the synchronization client should be located on the same machine as the remote database. This allows the synchronization client to connect to the active database and prevents a single point of failure in the system.

High Availability for Synchronization Clients

A solution is to install the synchronization client on both the primary and mirror server machines then initiate synchronization through an external procedure.

This can be accomplished with xp_cmdshell. The xp_cmdshell system procedure allows the execution of operating system commands directly to the Windows command shell via SQL code. This does not require knowing which machine is designated as the primary server, because:

• When an application calls this external procedure, it will only be fired on the computer that is currently designated as the primary database server.
• The synchronization client is called relative to the database server, so will not have any problem finding the database files.

Here is an example of an external procedure to initiate synchronization:

CALL xp_cmdshell( 'START dbmlsync -c dsn=MyDB -k -o c:\\out\\ml.out 
        -n MyPub -e sv=Version1', 'no_output' )

The xp_cmdshell process can be initiated through an application connection or via system process on the database server machine.

Removing Single Point of Failure with High Availability Synchronization Clients

In a database mirroring system, at least two machines must fail before the entire synchronization system fails. However, in the above solution, if the machine running the synchronization client fails then synchronization fails. To avoid this single point of failure an implementation using an external procedure called via a scheduled event that is stored in the database on both database servers will be sufficient. This prevents the need for an external application to initiate data synchronization. Scheduled events are a feature of Sybase iAnywhere’s SQL Anywhere that allows logic to be triggered based on a predefined schedule. The event will only fire on the server that is currently the primary server.

Here is an example of a scheduled event to call the external procedure:

CREATE EVENT "SyncEvent" 
HANDLER
BEGIN
     event_parameter( 'NumActive' )
     IF CAST( event_parameter('NumActive' ) AS INTEGER ) > 1 THEN RETURN;
     END IF;
     CALL xp_cmdshell( 'START dbmlsync -c dsn=MyDB -k -o c:\\out\\ml.out 
            -n MyPub -e sv=Version1', 'no_output' )
END;
ALTER EVENT "SyncEvent"
ADD SCHEDULE "daily_sync"
     EVERY 10 MINUTES BETWEEN '09:00:00' AND '22:00:00';

In this statement, the event parameter NumActive is used to ensure that an instance of synchronization is not already running. Synchronization is scheduled to occur every 10 minutes during business hours.

Summary

Corporations are going to continue to move towards always available data and occasionally connected applications. This will continue to increase the importance of enterprise database management systems to remain functional and online in 24/7 environments.

Database mirroring is an effective way to provide database availability and disaster-recovery for data synchronization environments. Failover of database mirroring environments is both fast and automatic, providing seamless, reliable service. Coupled with initiating synchronization from the remote database using the xp_cmdshell in a scheduled event will eliminate single points of failure in the mirrored architecture.

References

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/

Author

Joshua Savill is a Product Manager of data synchronization technologies for Sybase iAnywhere.