There are two mainstream ways to replicate data between databases.
Take a snapshot of the source database and duplicate the data into the target database. This is usually done in a batch or in an on-demand environment.
Capture changes Real Time as they happen in the source database and perform the exact same changes on the target database. This is usually done in a live data or transaction-oriented environment.
Live
data updates are an evolution of the snapshot idea. We first capture an
image of the source data and create target data. Then we watch the source
data to see if there are any changes. As changes occur, we capture these
changes in real time and perform the same changes we found on the source
database against the target database.
Live data event replication is the most efficient way to move information as it changes from the source database to the target database. When we first capture an image of the source database and create the target database the system must read and write every row of data in the entire database. From that point, live data event replication only accesses information in the original database that has been altered. If an organization has tens of millions of rows of data, only a very small fraction of the data typically changes during daily operations. Even the most efficient snapshot operations must still examine every row of data from the source every time they synchronize the source and target databases. Live data event replication consumes less computing resources than the other database replication alternatives.
Live data event replication data does not differ from reality in the same way snapshots of data differ. If we take weekly snapshots of our database system to create our target database, we will have an excellent image of our current business situation in both the target and source databases right after the snapshot is taken. But as time passes, the source database starts to diverge from the last snapshot. Transactions applied to the source database will not show up in our aging target database until we take another snapshot. Eventually, the duplicated data loses any value, so we are forced to update the target database; usually by creating a new snapshot of the source data.
Live data event replication does not suffer from this defect. As transactions are performed on the source database, the information is immediately moved into the target database and the source and target databases stay current.
Companies may need multiple database formats for business reasons. A particular group may need to analyze some facet of the financial picture such as current inventory levels or the status of accounts receivable but the group may not know how to use the source database system. A live image of the source database system in a format that the group knows how to access solves this problem.
Since badly formed queries can negatively impact critical databases, users may not be authorized to perform ad-hoc queries against the source database. Target databases containing current images of all or part of the source database can allow ad-hoc queries without any danger of impacting the performance of the source database.
Live data event replication is not appropriate in every situation. The method of choice (to make a snapshot or to use live data event replication) depends on the eventual use of the data.
There are times when it makes sense to use a snapshot of the source database. For month-end reporting we do not want the financial picture to give us new answers every time we query it.
Initial State
To start live data event replication, we must take the source database and create a snapshot that duplicates all of the data at a single point in time. With InstantdbSync, this step is called the "Initial State".
Performing an initial state operation moves a copy of the source database into the target database.
Queuing
After we have created the initial state, we will have an exact image of the database as it looked at a single point of time - the beginning of the snapshot creation step. But between the time when the initial state operation started and the time when it finished, it is possible that some updates have occurred against the source database. We don't want to lose these changes. The updates are stored in a message queue as they occur and at the end of the initial state, we apply the changes to the target database so it is current again. As new changes come in, we add them to the bottom of the message queue. We continue to use the message queue to update the target database. After a change has been applied to the target database it is removed from the queue.
The message queue allows us to preserve the state of the target database, even if the target database is taken off line. We simply add new data changes to the message queue and when the target database is ready to be brought online, we restart the target server and update the target database with the stored changes until the queue is exhausted.
ACID
ACID stands for Atomicity, Consistency, Isolation, and Durability. For a database to be reliable, the system must preserve the ACID of the data transactions.
Atomicity means that a group of operations is performed as a unit. With InstantdbSync, we process the transactions in the exact time order in which they occurred and have designed the target database process so it preserves the atomicity of the updates.
Consistency means that our database must be in a fully functional state when the transaction begins and when it ends. The target database system's transactions ensure the system retains database integrity.
Isolation means that the database changes happen as a group so that outside users of the system do not have to worry about the individual updates. Although moving money from one account to another account requires the money to be removed from the source account before it is added to the target account, an outside observer will see the accounts either in the original state or with both the addition and the subtraction. They will not see just the addition or just the subtraction.
With InstantdbSync, if the transaction in the target database does not succeed, the transaction is retained on the message queue and a problem notification message is generated. Once the problem has been corrected, then the transaction will be replayed. Partial transactions are not committed.
Durability
guarantees (not just promises) that a transaction has been fully
and correctly completed and the state of the changed records will
not be partly done or somehow undone. InstantdbSync
transactions are durable because of the target database's
relational properties; uncommitted transactions are retained in
a durable format in the message queue until they have been successfully
completed.
Problem Resolution
Because the changes have been stored once the transaction completes, we know that the changes are permanent and cannot be lost due to distressing events such as power outage. However complications can occur in the live data event replication process. Two possible problems are:
The source database may be damaged by hardware failure and have to be restored from backup.
Resources needed to store and forward a transaction may not be available. The system detects this and similar problems and automatically performs an initial state (if required) to create a new snapshot at a feasible time.
Certain kinds of problems can occur that the replication system won't be aware of but the system users will be. For example, data may be accidentally deleted from a target table. Since InstantdbSync only monitors changes on the source database, but not on the target database, it will not be aware of this problem. However, the system users can simply request a new initial state, and the target system will once again be synchronized with a true picture of the current data.