Synchronization of plural databases in a database replication system

• US 7,003,531 B2
• Filed: 12/10/2002
• Issued: 02/21/2006
• Est. Priority Date: 08/15/2001
• Status: Active Grant

• Current Assignee: RPX Corporation
• Original Assignee: Gravic Incorporated
• Inventors: Holenstein, Paul J., Holenstein, Bruce D., Strickler, Gary E.
• Primary Examiner(s): Corrielus, Jean M.
• Application Number:
  US10/317,026
• Publication Number:
  US 20030131027A1
• Time in Patent Office: 1,169 Days
• Field of Search: 707/200, 707/203, 707/10
• US Class Current: 1/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 one or more blocks of data from a source database at a first node;

(b) assigning a marker to the one or more blocks 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 one or more blocks of data from the first node to the second node without passing the one or more blocks of data through the audit trail;

(f) at the second node, storing the one or more blocks of data in the target database upon receipt at the second node of the one or more blocks of data and the marker assigned to the one or more blocks 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 one or more blocks 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 one or more blocks of data has a predetermined byte size, and in step (a), as many blocks 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 one or more blocks of data includes one or more rows of data.

6. The method of claim 1 wherein each of the one or more blocks of data includes one or more rows of data, the method further comprising:

(h) 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.

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

8. The method of claim 1 wherein each one or more blocks 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 one or more blocks 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 one or more blocks of data indicates the keys for rows of data within the source database.

12. The method of claim 1 wherein either the marker for each of the one or more blocks of data, or the one or more blocks of data itself, indicates the keys for 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 block of data, or a key range inclusive of the block 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 one or more blocks of data having a key range, wherein step (f) further comprises for each one or more blocks of data sent from the first node, comparing the corresponding key range in the target database, and the one or more blocks of data is processed as follows:

(i) if a row in the one or more blocks 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 one or more blocks of data is present in the target database but has content that does not match the content of the row in the one or more blocks of data, updating the row with the new content; and

(iii) if any rows are missing from the one or more blocks 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 one or more blocks of data into the transaction transmitter at the first node after the one or more blocks of data is read, and step (e) further comprises using the transaction transmitter at the first node to send the one or more blocks 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 one or more blocks of data corresponding to a newly received marker.

16. The method of claim 1 wherein a unique marker is assigned to each subsequent one or more blocks of data.

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

18. The method of claim 1 wherein the audit trail includes a transaction completion state entry for each of the one or more blocks of data, and the marker assigned to the one or more blocks of data is represented by the transaction completion state entry associated with the one or more blocks of data.

19. 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 one or more blocks of data from a source database at a first node;

(b) assigning a marker to the one or more blocks 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 one or more blocks of data to the second node and receiving the indicia and the marker at the second node;

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

(g) upon receipt at the second node of the marker for the corresponding one or more blocks of data, synchronizing the target database with the source database based upon the results of the comparison; and

(h) repeating steps (a) through (g) for additional blocks of data in the source database, wherein a marker is assigned to each subsequent one or more blocks of data.

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

21. The method of claim 19 wherein each of the one or more blocks of data includes one or more rows of data, the method 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.

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

(i) if a row in the one or more blocks of data 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 in the one or more blocks of data 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.

23. The method of claim 19 wherein the action taken in step (g) includes the step of detecting if a row in the one or more blocks of data 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.

24. The method of claim 19 wherein the action taken in step (g) includes the step of detecting if any rows in the one or more blocks of data 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.

25. The method of claim 19 wherein the indicia of the contents of the one or more blocks of data is one of a CRC, sequence number or timestamp of the row of data.

26. The method of claim 19 wherein the one or more blocks of data have a specified key range.

27. The method of claim 19 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.

28. The method of claim 19 wherein a unique marker is assigned to each subsequent one or more blocks of data.

29. The method of claim 19 wherein the markers are virtual markers.

30. 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 one or more blocks of data having a key range, each one or more blocks 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.

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

(i) if a row in the one or more blocks 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 one or more blocks of data is present in the target database but has content that does not match the content of the row in the one or more blocks of data, updating the row with the new content; and

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

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

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

34. 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 one or more blocks 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.

35. The method of claim 34 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.

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

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

38. 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 one or more blocks 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.

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

40. 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) initiating a transaction at the source database in a first node, the transaction initiation generating a transaction identifier;

(b) reading one or more blocks of data from a source database at the first node;

(c) assigning the transaction identifier to the one or more blocks of data, the transaction identifier being a marker for the one or more blocks of data;

(d) completing the transaction and entering the transaction identifier into the audit trail;

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

(f) sending the one or more blocks of data from the first node to the second node without passing the one or more blocks of data through the audit trail;

(g) at the second node, storing the one or more blocks of data in the target database upon receipt at the second node of the one or more blocks of data and the marker assigned to the one or more blocks of data; and

(h) repeating steps (a) through (g) for additional blocks of data in the source database, wherein a marker is assigned to each subsequent one or more blocks 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.

41. The method of claim 40 wherein each of the one or more blocks of data includes one or more rows of data, the method 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.

42. The method of claim 40 wherein step (d) further comprises completing the transaction by executing a commit or abort operation.

43. 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 one or more blocks of data from a source database at a first node;

(b) assigning a marker to the one or more blocks of data, wherein the marker indicates the current audit trail insertion point;

(c) sending the one or more blocks of data and the marker from the first node to the second node without passing the one or more blocks of data or the marker through the audit trail, wherein the marker is piggybacked onto the one or more blocks of data;

(d) at the second node, storing the one or more blocks of data in the target database upon receipt at the second node of the one or more blocks of data and the marker assigned to the one or more blocks of data, wherein the one or more blocks of data are stored in the target database at the insertion point indicated by the marker; and

(e) repeating steps (a) through (d) for additional blocks of data in the source database, wherein a marker is assigned to each subsequent one or more blocks 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.

44. The method of claim 43 wherein each of the one or more blocks of data includes one or more rows of data, the method further comprising:

(f) 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.