Keeping many databases in sync all the time

Question

I have a network of desktop PCs (Windows 7) which are located geographically apart from each other (connected with LAN).

The network has an Oracle back-end.

I want to install a database locally on each PC (about 12 of them, currently thinking of SQLite but open to other possibilities).

I need to guarantee the local databases are kept in sync with each other and with the Oracle db all the time or at least as long as there is network connectivity (mesh topology).

The synchronization involves only a single table.

What are some possible effective solutions for this problem ? preferably something you have worked with beforehand.

Answer 1

As I do not vast Knowledge in this area I'm not sure this will completely address your problems,but reading this question I realized that you need a database replication software package,so I would like to suggest if you could look into SymmetricDS Why I am suggesting this ? Basic Introduction from its official website.SymmetricDS is open source software that is free to use

SymmetricDS is open source software for multi-master database replication, filtered synchronization, or transformation across the network in a heterogeneous environment. It supports multiple subscribers with one direction or bi-directional asynchronous data replication. It uses web and database technologies to replicate tables between relational databases, in near real time if desired. The software was designed to scale for a large number of databases, work across low-bandwidth connections, and withstand periods of network outage.

By using database triggers, SymmetricDS guarantees that data changes are captured and atomicity is preserved. Support for database vendors is provided through a Database Dialect layer, with implementations for MySQL, Oracle, SQL Server, SQL Server Azure, PostgreSQL, DB2, Informix, Interbase, Firebird, HSQLDB, H2, Apache Derby, Greenplum, and SQLite included.

Synchronization can be configured to push data or pull data on a periodic basis. SymmetricDS allows for synchronization between two or more tiers of nodes, as might be needed in the following scenarios:

• A handful of regional servers synchronizing from the general office to remote geographical areas
• Dozens of Point of Sale (POS) register nodes using an embedded database to sync with a store server
• Thousands of store server nodes using a departmental class database to sync with a regional node

Features

• Data Channels - Table synchronizations are grouped into independent channels
• Guaranteed Delivery - Synchronized data is guaranteed to arrive at the target destination. If a synchronization fails, the same batch of data will be retried until it succeeds or manual intervention is taken. All other data synchronization is halted for the failed channel only.
• Transaction Aware - Data updates are recorded and replayed with the same atomicity
• Centralized Configuration - All configuration is downloaded from a central registration server
• Multiple Deployment Options - Standalone engine, web application, embedded software component
• Data Filtering and Rerouting - Allows for localized passwords and sensitive data filtering/routing
• HTTP Transport - Pluggable transport defaults to Representation State Transfer (REST-style) HTTP services
• Payload Compression - Optionally compresses data on transport
• Notification Schemes - Push (trickle-back data) or Pull (trickle-poll data) changes
• Symmetric Data Protocol - A fast streaming data format that is easy to generate, parse, and load
• Plug-In API - Add customizations through extensions and plug-in points
• Two-Way Table Synchronization - The same table can be synchronized both to and from the host system while avoiding update loops
• Database Versioning - Specify data synchronization by version of target database
• Auto Database Creation - Optionally allow creating and upgrading of database schema
• Embeddable - Small enough to embed or bootstrap within another application (i.e. a POS application)
• Multiple Schemas - Supports multiple database schemas naturally through the existence of Data Channels
• Primary Key Updates - Captures the "before" and "after" data being changed, allowing updates to primary key data
• Remote Management - Administration through a Java Management Extensions (JMX) console
• Remote Database Administration - SQL can be delivered and run at remote databases via the synchronization infrastructure
• Initial Data Load - Prepare the satellite database with an initial or recovery load of data

Hope My answer Helps!