Permanent storage of many databases

Question

I have a network of desktop PCs (Windows 7) that are geographically separate from each other (connected to a local network).

There is an Oracle server on the network.

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

I need to guarantee that the local databases will be synchronized with each other and with Oracle db all the time, or at least as long as there is a network connection (grid topology).

Synchronization includes only one table.

What are the possible effective solutions to this problem? preferably something you worked with before.

Answer 1

Since I do not have extensive knowledge in this area, I am not sure that this will completely solve your problems, but after reading this question, I realized that you need a software package for database replication, so I would suggest if you would could learn SymmetricDS Why am I suggesting this? Basic information from the official site. SymmetricDS is open source software that is freely used.

SymmetricDS is an open source multi-wizard database software for replication, filtered synchronization, or network conversion in a heterogeneous environment. It supports multiple subscribers with one direction or bidirectional asynchronous data replication. It uses Internet technology and databases to replicate tables between relational databases, in the near real time, if necessary. The software is designed to scale for a large number of databases, work through low-bandwidth connections and withstand periods of network outage.

Using database triggers, SymmetricDS ensures that data is changed captured and atomicity is maintained. Database provider support is provided through the Dialect Database layer, with implementations for MySQL, Oracle, SQL Server, SQL Server Azure, PostgreSQL, DB2, Informix, Interbase, Firebird, HSQLDB, H2, Apache Derby, Greenplum, and SQLite.

Synchronization can be configured to periodically transmit data or retrieve data. SymmetricDS allows you to synchronize between two or more levels of nodes, which may be required in the following scenarios:

• Several regional servers synchronizing from a common office to remote geographic areas.
• Dozens of Point Registration Points (POS) using the built-in database to synchronize with the warehouse server.
• Thousands of storage server nodes using a department database to synchronize with a regional node

Functions

• Data feeds. Table synchronization is grouped into independent channels
• Guaranteed Delivery - Guaranteed to receive synchronized data for the intended purpose. If synchronization is unsuccessful, the same batch of data will be repeated until it succeeds, or manual intervention is accepted. All other data synchronization is stopped for failed channel only.
• Transaction Aware - data updates are recorded and played using the same atomicity
• Centralized configuration - the entire configuration is downloaded from the central registration server
• Several deployment options - stand-alone engine, web application, embedded software component
• Data filtering and redirection - allows you to localize passwords and sensitive data filtering / routing
• HTTP Transport - The default transport to connect to the State view. Transfer (REST-style) HTTP services
• Payload compression - the ability to compress data in transport
• Notification schemes - click (return line data) or Pull (drop survey) data).
• Symmetric Data Protocol - Fast data stream format to generate, analyze and load data
• Plug-In API - adding settings through extensions and plugins Points
• Double-sided table synchronization - the same table can be synchronized both to the system and to it, avoiding update cycles.
• Database Versions — Specify the synchronization of data by version of the target database.
• Automatic database creation. Allows you to create and update database schema
• Embeddable - Small enough to embed or load into another application (i.e. a POS application).
• A few schemes. Supports multiple database schemas. through the existence of data channels
• Primary Key Update. Captures data "before" and "after". changed, allowing you to update the primary key data.
• Remote Management - Administration through Java Management Extension Consoles (JMX)
• Remote database administration - SQL can be delivered and run on remote databases through the synchronization infrastructure
• Initial data loading. Prepare a satellite database using initial or recovery data loading

Hope my answer helps!

Answer 2

You need PHP-Script that checks for changes in one table on one row. $ dbArray is an array in which all computational names with databases.

$dbArray[0]="Comp1"; $dbArray[1]="Comp2"; $checkvalue=""; foreach ($dbArray as $value){ $db=mysqli_connect($value,"Username","Password"); mysqli_set_charset($db,"utf8"); mysqli_select_db($db,"DB_NAME"); $sql = "SELECT * FROM TABLE_NAME WHERE id=1"; $result = mysqli_query($db,$sql); while ($row = mysqli_fetch_assoc($result)){ if($row["Rowname"]!=$checkvalue&&$checkvalue!=""){ UpdateData ($value); }else{ //Value did not change } } } 

And you need the UpdateData function, which changes the data.

 function UpdataData ($ComputerChancedData){ } 

The function should read data from the computer $ComputerChancedData and write it to all computers that are not $ComputerChancedData .

 // function UpdateData // Here have to be the Code which gets the new Value $checkvalue=""; foreach ($dbArray as $value){ if($value!=$ComputerChancedData){ $db=mysqli_connect($value,"Username","Password"); mysqli_set_charset($db,"utf8"); mysqli_select_db($db,"DB_NAME"); $sql = "UPDATE TABLE_NAME SET ROW_NAME='$NEWVALUE' WHERE id=1"; $result = mysqli_query($db,$sql); } } 

Since it needs to be reloaded every minute, you need to reload Javascript:

 <script> (function(){(setTimeout(function(){window.location.href="servername/seitenname.php";},60000))}); </script> 

EDIT: I am using MySqli for this script

EDIT2: All databases must have the same username and password. If not, you need to create a new user with the correct ones.