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.

Ka

Modifications can be made on any of the nodes? – Ballata

@Ballata I guess so, but what modifications are you thinking of ? – Ka

What is the reasoning for wanting these local databases? WHat is the front-end that currently accesses this oracle db? – Photochemistry

@Photochemistry The front end isn't implemented yet (probably a Windows Forms) but it doesn't affect anything. The local dbs are used to keep the nodes up and running during network down times. – Ka

I believe that the "central database replicating to/from a bunch of local databases" is a classic database anti-pattern. I don't think you understand the kind of quagmire you're planning on diving into. If your network is so unreliable that connecting to a central database will be a problem I suggest you invest your time and money into improving your network; it will be easier and much cheaper to do so. Besides, if you have network problems now, adding additional database sync traffic will just make it worse - so you'll have to invest in your network anyways. Best of luck. – Hunyadi

@BobJarvis The network is pretty good actually but the chance of trouble is always there. You say it's an anti-pattern, then what is the pattern ? – Ka

It sounds as if you are still at the decision step given your 'The front end isn't implemented yet (probably a Windows Forms)' comment. A web front end with a single, central database instance would be my preference unless there is a compelling reason not to. Yes it can suffer from network problems, which will need to be considered in caching and other design concerns, but that'll be nothing to the pain that will result from trying to keep multiple databases in sync with one another. – Boanerges

@Boanerges Well there are two concerns with a website. First of all, the application need to read from the computers serial port so it will need to access hardware ( I don't know if this is possible without an ActiveX and it will complicate matter otherwise because you will need a service or something). The other thing, what if the network is down. You will need to install the website locally on each device while in the other situation you can continue working and then sync later. – Ka

For 13x13 DB synching you need proper software - this is not trivial. The minimum price for something like GoldenGate would be $8,750 going up to $227,500ish (it's worth talking to an Oracle representative). If your network's down then you could have 12 people simultaneously updating the same record - what happens when it comes back up again? I'm with everyone else. Put your app online and if you're into ultra-special-once-in-a-millenium DR then have a redundant network with a secondary non-VPN route to the open interwebs - you can then access your app if your network exists or not. – Rosinarosinante

@Rosinarosinante We are not expecting conflicts due to the nature of the application. Nevertheless there will be a policy to handle them if they happen. In our scenario every PC is like a service point. Customers come in very regularly and need to be served in real time, we can't afford any delays so if the network is down then the user works locally until restore. It is like if these PCs are roaming users that might lose connectivity and need to access stock for instance. – Ka

Ok, interesting situation. "The synchronization involves only a single table" - how much data will the table contain? What frequency of update do you need to support (i.e. how frequently will the table's rows be inserted/updated/deleted in each local database)? – Boanerges

@Boanerges a lot of data. CRUD frequency depends on business circumstances but on average we are expecting a CRUD operation every 4-5 minutes. – Ka

I'm working right now in a sychronization mechanism across SQLite databases hosted on Windows destop having SharePoint as the central store. I have just finished the local change tracking part. In my scenario no changes are done on server, it just serves as the central store. I use SQLiteConnection.Update and SQLiteConnection.Commit events to track changes. Changes are tracked on a separate database with just one table LocalChanges, this table has the changed data as JSON. This JSON is uploaded later to SharePoint. Apps poll SharePoint for changes. If you are interested I can share my code – Supposition

@JesúsLópez Thank you for the offer. When I asked about this I expected for well proven solution using perhaps a popular framework. I had in mind that I might implement a custom solution like yours. Nevertheless if you are willing to share then I will consider it. – Ka

Ok, I will create a repo on GitHub.By the way, this is the third time I create a solution like this. – Supposition

Two questions. Will the table on the ORACLE database be modified directly by some application?. You will need to create a web service that will be called by the destop apps to upload and download changes. Have you got a web server in your environment to deploy it? – Supposition

Do you need to support each CRUD operation on each database? For example if you can allow just select on the SQLite databases and you do the other work on Oracle then the problem becames much easier and in case the network is down your app can warn the user about this and support just select (read) operation from the local SQLite "cache". – Mortonmortuary

@Mortonmortuary Unfortunately that is not possible. – Ka

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!

Disaccharide