Synchronization of plural databases in a database replication system

• US 20030037029A1
• Filed: 08/15/2001
• Published: 02/20/2003
• Est. Priority Date: 08/15/2001
• Status: Active Grant

• Current Assignee: RPX Corporation
• Original Assignee: ITI Limited (Government of India)
• Inventors: Holenstein, Bruce D., Holenstein, Paul J., Strickler, Gary E.
• Granted Patent: US 6,745,209 B2
• Time in Patent Office: Days
• US Class Current: 707/1
• CPC Class Codes: G06F 16/2343 Locking methods, e.g. distributed locking or locking implementation details
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users
• CPC Class Codes: Y10S 707/99953 Recoverability
• CPC Class Codes: Y10S 707/99954 Version management

Abstract

In a database synchronization process, a block of data is read from a source database at the first node. A marker is assigned to the block of data, and the marker is then written to an audit trail at the first node. The marker is sent from the audit trail to a second node having a target database. The block of data is sent from the first node to the second node without passing the block of data through the audit trail. At the second node, the block of data is stored in the target database upon receipt at the second node of the block of data and the marker assigned to the block of data. This process is repeated for additional blocks of data in the source database, wherein a marker is assigned to each subsequent block of data. In this manner, some or all of the source database may be replicated to the target database without having to store or pass any data in the source database in or through the audit trail at the first node.

Claims

1. A method of synchronizing plural databases in a database replication system including a plurality of nodes connected via communication media in a topology, each node including (i) a database, (ii) an audit trail of all transactions posted to the database, and (iii) a transaction transmitter which sends selected transactions in the audit trail to one or more other nodes, the method comprising:

(a) reading a block of data from a source database at the first node;

(b) assigning a marker to the block of data;

(c) writing the marker to the audit trail at the first node;

(d) the transaction transmitter at the first node sending the marker in the audit trail to a second node having a target database;

(e) sending the block of data from the first node to the second node without passing the block of data through the audit trail;

(f) at the second node, storing the block of data in the target database upon receipt at the second node of the block of data and the marker assigned to the block of data; and

(g) repeating steps (a) through (f) for additional blocks of data in the source database, wherein a marker is assigned to each subsequent block of data, the method thereby allowing some or all of the source database to be replicated to the target database without having to store or pass any data in the source database in or through the audit trail at the first node.

2. The method of claim 1 wherein the block of data has a predetermined byte size, and in step (a), as many rows of data as can fit within the predetermined byte size are read from the source database.

3. The method of claim 2 further comprising:

(h) dynamically changing the predetermined byte size based upon processing conditions at one or more of the nodes.

4. The method of claim 1 wherein at least one of the nodes further includes (iv) a loader, the loader performing steps (a), (c) and (e).

5. The method of claim 1 wherein each block of data includes one or more rows of data.

6. The method of claim 1 further comprising:

(h) locking each block of data in the source database after each block of data is read, and unlocking each block of data in the source database after the corresponding marker is written to the audit trail.

7. The method of claim 1 wherein step (f) is performed using an insert operation.

8. The method of claim 1 wherein each block of data includes one or more rows of data, step (a) is performed in primary key order of the target database, and step (f) is performed by a single append block operation which automatically inserts all of the rows of data within the block of data into the target database.

9. The method of claim 1 wherein the blocks of data and the corresponding markers may flow from the first node to the second node asynchronously, and thus the blocks of data may be stored in the target database asynchronously with respect to the order in which the blocks of data are read from the source database.

10. The method of claim 1 wherein the source database and the target database are both on-line during the synchronization.

11. The method of claim 1 wherein the marker for each block of data indicates the keys for the rows of data within the source database.

12. The method of claim 1 wherein either the marker for each block of data, or the block of data itself, indicates the keys for the rows of data within the source database, and any transactions executed on a row of data in the source database during the synchronization are sent to the audit trail, and then are sent by the transaction transmitter from the audit trail to the second node for posting to the target database, the method further comprising:

(h) at the second node, posting the received data associated with the executed transactions in the target database only if the row of data, or a key range inclusive of the row of data, corresponding to the received data has been previously posted to the target database through the synchronizing process.

13. The method of claim 1 wherein the target database is being resynchronized and thus is not initially empty, each block of data having a key range, wherein step (f) further comprises for each block of data sent from the first node, comparing the corresponding key range in the target database, and the block of data is processed as follows:

(i) if a row in the block of data is not present in the target database, inserting the row and its content into the target database,

(ii) if a row in the block of data is present in the target database but has content that does not match the content of the row in the block of data, updating the row with the new content; and

(iii) if any rows are missing from the block of data but are present in the target database, then deleting such rows from the target database.

14. The method of claim 1 wherein step (a) further comprises writing the block of data into the transaction transmitter at the first node after the block of data is read, and step (e) further comprises using the transaction transmitter at the first node to send the block of data to the second node.

15. The method of claim 1 wherein the second node receives replication stream commands intermixed with markers, wherein step (f) further comprises suspending processing of the replication stream commands upon receipt at the second node of a marker until step (f) is performed for the block of data corresponding to a newly received marker.

16. The method of claim 1 wherein a unique marker is assigned to each subsequent block of data.

17. The method of claim 1 wherein the markers are virtual markers.

18. A method of synchronizing plural databases in a database replication system including a plurality of nodes connected via communication media in a topology, each node including (i) a database, (ii) an audit trail of all transactions posted to the database, and (iii) a transaction transmitter which sends selected transactions in the audit trail to one or more other nodes, the method comprising:

(a) reading at least one row of data from a source database at the first node;

(b) assigning a marker to the at least one row of data;

(c) writing the marker to the audit trail at the first node;

(d) the transaction transmitter at the first node sending the marker in the audit trail to a second node having a target database;

(e) sending indicia of the contents of the at least one row of data to the second node and receiving the indicia and the marker at the second node;

(f) comparing indicia of any corresponding rows of data in the target database;

(g) upon receipt at the second node of the marker for the corresponding at least one row of data, taking action to synchronize the target database with the source database based upon the results of the comparison; and

(h) repeating steps (a) through (g) for additional rows of data in the source database, wherein a marker is assigned to each subsequent at least one row of data.

19. The method of claim 18 wherein at least one of the nodes further includes (iv) a loader, the loader performing steps (a), (c) and (e).

20. The method of claim 18 further comprising:

(i) locking each row of data in the source database after each row of data is read, and unlocking each row of data in the source database after the corresponding marker is written to the audit trail.

21. The method of claim 18 wherein the action taken in step (g) includes the following steps:

(i) if a row sent from the source database is not present in the target database, then the content of the row is requested from the first database, and upon receipt, the row and its content are inserted into the target database,

(ii) if a row sent from the source database is present in the target database but has indicia that does not match the indicia of the corresponding row in the target database, then the content of the row is requested from the source database, and upon receipt, the second node updates the row with the new content.

22. The method of claim 18 wherein the action taken in step (g) includes the step of detecting if a row sent from the source database is present in the target database and has indicia that matches the indicia of the corresponding row in the target database, and, if so, then no change is made to the content of the row in the target database and no content is requested from the first database.

23. The method of claim 18 wherein the action taken in step (g) includes the step of detecting if any rows are missing from the data sent from the source database but are present in the target database, and, if so, then such rows are deleted from the target database.

24. The method of claim 18 wherein the indicia of the row of data is one of a CRC, sequence number or timestamp of the row of data.

25. The method of claim 18 wherein the at least one row of data is a block of data having a specified key range.

26. The method of claim 18 wherein the second node receives replication stream commands intermixed with markers, and step (g) further comprises suspending processing of the replication stream commands upon receipt at the second node of a marker until step (g) is performed for the rows of data corresponding to a newly received marker.

27. The method of claim 18 wherein a unique marker is assigned to each subsequent block of data.

28. The method of claim 18 wherein the markers are virtual markers.

29. A method for synchronizing a target database with a source database in a database replication system wherein the source database is on-line during the synchronization, the target database thus receiving replication data during the synchronization, the source database being located at a first node of the system, and the target database being located at a second node of the system, the method comprising:

(a) sending successive blocks of data from the first node to the target database, each block of data having a key range, each block of data including one or more rows of data;

(b) posting the successively sent blocks of data to the target database;

(c) storing the total key range of the blocks of data received at the target database; and

(d) posting any replication data received at the target database only if the row of data, or a key range inclusive of the row of data, corresponding to the received data has been previously posted to the target database through the synchronizing process.

30. The method of claim 29 wherein the target database is being resynchronized and thus is not initially empty, wherein step (b) further comprises for each block of data sent from the first node, comparing the corresponding key range in the target database, and processing the block of data as follows:

(i) if a row in the block of data is not present in the target database, inserting the row and its content into the target database,

(ii) if a row in the block of data is present in the target database but has content that does not match the content of the row in the block of data, updating the row with the new content; and

(iii) if any rows are missing from the block of data but are present in the target database, then deleting such rows from the target database.

31. The method of claim 29 wherein the target database and the source database are both on-line during the synchronization.

32. The method of claim 29 wherein the successive blocks of data in step (a) are sent to the target database directly from the source database.

33. A method for synchronizing a target database with a source database and replicating the source database to the target database in a database replication system, wherein the source database is on-line during the synchronization, the source database being located at a first node of the system, and the target database being located at a second node of the system, the method comprising:

(a) replicating changes in the source database to the target database by sending a replication stream from the first node to the second node;

(b) sending successive blocks of data from the first node to the target database to synchronize or resynchronize the target database with the source database, each block of data including one or more rows of data;

(c) inserting the successively sent blocks of data into the replication stream at a point when the respective blocks of data are current or consistent in the source database; and

(d) posting the replication stream to the target database, the method thereby allowing synchronization or resynchronization to occur simultaneously with replication.

34. The method of claim 33 wherein a marker is assigned to each of the blocks of data and is sent to the second node, wherein step (c) further comprises inserting the successively sent blocks of data into the replication stream at a point when the marker arrives at the second node.

35. The method of claim 34 wherein the markers are sent to the second node along with their respective blocks of data.

36. The method of claim 33 wherein the target database and the source database are both on-line during the synchronization or resynchronization and replication.

37. A method for synchronizing a target database with a source database and replicating the source database to the target database in a database replication system, wherein the source database is on-line during the synchronization, the source database being located at a first node of the system, and the target database being located at a second node of the system, the method comprising:

(a) replicating changes in the source database to the target database by sending a replication stream from the first node to the second node;

(b) sending successive blocks of data from the first node to the target database to synchronize or resynchronize the target database with the source database, each block of data including one or more rows of data, the successively sent blocks of data defining a loading stream; and

(c) posting the successively sent blocks of data into the target database when the replication stream is occurring slower than the loading stream, the method thereby allowing synchronization or resynchronization to occur simultaneously with replication.

38. The method of claim 37 wherein the target database and the source database are both on-line during the synchronization or resynchronization and replication.