Keeping many databases in sync all the time

Loading...

Keeping many databases in sync all the time

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.

Solutions/Answers:

Solution 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!

Solution 2:

You need a PHP-Script which checks for changes in one table in one line.
$dbArray is an Array which all Computernames with the 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 a function UpdateData which swaps the data.

function UpdataData ($ComputerChancedData){
}

The function has to read the data from the Computer $ComputerChancedData and write it to all Computer which 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);
  }
}

Because this have to be reloaded every Minute, there have to be a reloading Javascript:

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

EDIT: I use MySqli for this script

EDIT2: All DBs have to have the same username and password. If not you have to create a new user with the right ones.

References

Loading...