Database system providing improved methods for data replication

• US 8,121,978 B2
• Filed: 09/11/2003
• Issued: 02/21/2012
• Est. Priority Date: 11/15/2002
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Wiss, Richard, Puttagunta, Naveen, Reiger, Derek Gerald, Setzke, David R. Jr.
• Primary Examiner(s): PHAM, KHANH B
• Application Number:
  US10/605,154
• Publication Number:
  US 20040098425A1
• Time in Patent Office: 3,085 Days
• Field of Search: 707/8, 707/10, 707100-102, 707201-204, 707/615, 707/624, 707/634
• US Class Current: 707/615
• CPC Class Codes: G06F 11/2071 using a plurality of controllers
• CPC Class Codes: G06F 16/275 Synchronous replication
• CPC Class Codes: G06F 2201/855 Details of asynchronous mirroring using a journal to transfer not-yet-mirrored changes

Abstract

A system providing improved methods for data replication is described. A method for replicating a transaction from a primary database to a replicate database while the replicate database remains available for use comprises: recording information about a transaction being performed at a primary database in a transaction log; synchronously copying the information about the transaction in the transaction log to a mirrored transaction log; generating a reconstructed transaction based on the information about the transaction copied to the mirrored transaction log; and applying the reconstructed transaction at the replicate database while the replicate database remains available for use.

Claims

1. A method for capturing database changes at a primary database and applying those changes to a replicate database while the replicate database remains on-line and available for use, the method comprising:

monitoring transactions occurring at the primary database for detecting changes made to the primary database;

recording information about transactions observed to have occurred at the primary database in a transaction log;

synchronously copying the information about the transactions recorded in the transaction log to a remote site, so as to create at the remote site a mirrored transaction log that is guaranteed to contain at a synchronized point in time an exact copy of the transactions recorded in the transaction log at the primary database;

copying database schema from the primary database to the remote site when the database schema is determined to be modified, to enable transactions to be reconstructed and applied at the replicate database;

wherein said monitoring and recording steps occur at a primary site;

wherein, while the replicate database remains on-line and available for use, changes made at the primary database are replicated to the replicate database by;

said transactions at the replicate database being reconstructed based on the information about the transactions copied to the mirrored transaction log, the information including database schema of the primary database identified as modified and copied to the remote site; and

the reconstructed transactions being asynchronously applied at the replicate database;

wherein the primary site and remote site are different physical locations.

2. The method of claim 1, wherein said transactions include selected ones of a Structured Query Language (SQL) “

INSERT”

, “

UPDATE”

, “

DELETE”

, “

DDL” and

“

Procedure”

operation.

3. The method of claim 1, wherein said recording step includes recording at least one log record about one of the transactions in the transaction log.

4. The method of claim 3, wherein said at least one log record characterizes changes made to the primary database in the transaction.

5. The method of claim 1, wherein said synchronously copying step includes using a file mirroring module.

6. The method of claim 1, wherein said synchronously copying step includes using file replication hardware.

7. The method of claim 1, wherein said synchronously copying step includes using file replication software.

8. The method of claim 1, wherein said synchronously copying step includes synchronously copying information to the transaction log and the mirrored transaction log before completing the transaction at the primary database.

9. The method of claim 1, wherein said synchronously copying step includes replicating at a file block level the information about the transactions in the transaction log to the mirrored transaction log.

10. The method of claim 1, wherein, at the replicate database, said transactions are formatted to have the same format as the transactions at the primary database.

11. The method of claim 1, wherein, at the replicate database, said reconstructed transactions are asynchronously applied and verified that the reconstructed transactions are ordered correctly.

12. The method of claim 1, wherein, at the replicate database, said reconstructed transactions are asynchronously applied in the same order as the transaction order at the primary database.

13. The method of claim 1, further comprising:

wherein, at the replicate database, a database query is responded to while a transaction is being replicated from the primary database to the replicate database.

14. The method of claim 1, further comprising:

downloading a set of computer-executable instructions for performing the method of claim 1.

15. A system for replicating transactions from a source database to a standby database, the system comprising:

source and standby databases sharing a common schema, the source database having a transaction log for recording log records for transactions performed at the source database, wherein the transaction log is at a primary site;

a mirrored transaction log operable to record mirror copies of the log records for transactions performed at the source database, so as to create at a remote site an exact synchronous copy of the transaction log at a given point in time;

a file mirroring module operable to synchronously replicate log records from the transaction log to the mirrored transaction log as transactions are performed at the source database;

database schema for the source database, wherein the database schema is copied to the remote site to enable transactions to be reconstructed and applied at the standby database;

a log reader module at the remote site operable to read log records in the mirrored transaction log and reconstruct transactions for application to the standby database based upon the database schema and log records in the mirrored transaction log;

a distribution module operable to asynchronously apply the transactions reconstructed by the log reader module to the standby database; and

a module operable to determine that the database schema of the primary database has been modified, and copy the modified database schema to the remote site;

wherein both the source and standby databases remain on-line for use while transactions are replicated from one to the other; and

wherein the primary site and remote site are different physical locations.

16. The system of claim 15, wherein said standby database is available for responding to database queries while transactions are being replicated from the source database to the standby database.

17. The system of claim 15, wherein said transactions include a selected one of a Structured Query Language (SQL) “

INSERT”

, “

UPDATE”

, “

DELETE”

, “

DDL” and

“

Procedure”

operation.

18. The system of claim 15, wherein said log records characterize changes made to the source database based upon transactions performed at the source database.

19. The system of claim 15, wherein said file mirroring module comprises file replication hardware.

20. The system of claim 15, wherein said file mirroring module comprises a disk mirroring module.

21. The system of claim 15, wherein said file mirroring module is operable to replicate log records in the transaction log to the mirrored transaction log at a file block level.

22. The system of claim 15, wherein said file mirroring module is operable to replicate log records relating to a particular transaction performed at the source database to the mirrored transaction log before said particular transaction is completed at the source database.

23. The system of claim 15, wherein said log reader module is operable to format the reconstructed transactions so that the reconstructed transactions are in the same format as the transaction at the source database.

24. The system of claim 15, wherein said distribution module is operable to apply reconstructed transactions at the standby database in the same order as the order of transactions applied at the source database.

25. A method for replicating database operations from a first database to a second database while making the second database available for decision support purposes, the method comprising:

copying database schema from the first database to the second database prior to performing an operation at the first database;

as database operations are performed at the first database, generating a first set of log records characterizing said operations and recording them in a first log, so that the first log reflects all changes made to the first database, wherein the first log and first database are located at a primary site;

as the first set of log records are recorded in the first log, synchronously recording in a second log located at a remote site a second set of log records that comprise an exact copy of the first set of log records, so that said second log comprises at a synchronized point in time an exact copy of said first log; and

wherein, while the second database is available for decision support purposes, said operations performed at the first database are asynchronously replicated at the second database by asynchronously performing the substeps of;

constructing replicate operations based, at least in part, on said second set of log records recorded in the second log and said database schema; and

applying the replicate operations at the second database;

wherein the primary site and the remote site are different physical locations.

26. The method of claim 25, wherein said operations include selected ones of a Structured Query Language (SQL) “

INSERT”

, “

UPDATE”

, “

DELETE”

, “

DDL” and

“

Procedure”

operation.

27. The method of claim 25, wherein said synchronously recording step includes file mirroring.

28. The method of claim 25, wherein said synchronously recording step includes replicating said at least one log record to the second log at a file block level.

29. The method of claim 25, wherein said synchronously recording step includes using a disk mirroring module.

30. The method of claim 25, further comprising:

tracking modifications to said database schema at the first database; and

constructing a replicate operation based on said database schema in effect when the operation is performed at the first database.

31. The method of claim 25, further comprising:

assigning a unique identifier to database objects at the first database;

if a database object is modified, assigning a different unique identifier to the database object that is modified; and

determining a particular database object to be used in constructing a replicate operation based upon said unique identifier assigned to said particular database object.

32. The method of claim 25, wherein said constructing substep includes formatting the replicate operations in the same manner as said operations at the first database.

33. The method of claim 25, wherein said applying substep includes applying the replicate operations at the second database in the same order as said operations are applied at the first database.

34. The method of claim 25, wherein making the second database available for decision support purposes includes responding to a database query as said operation is being replicated.

35. The method of claim 25, wherein making the second database available for decision support purposes includes providing access to data in the second database as said operation is being replicated.

36. A method for replicating transactions from a primary database to a replicate database while the replicate database remains available for use, the primary database and the replicate database sharing a common schema, the method comprising:

recording log records for transactions being performed at a primary database in a primary transaction log at a primary site;

synchronously creating a mirrored transaction log at a remote site, the mirrored transaction log comprising an exact and synchronized copy of the log records in the primary transaction log;

copying database schema from the primary database to the remote site to enable transactions to be reconstructed and applied at the replicate database when the database schema is determined to be modified;

generating reconstructed transactions based on the copies of the log records in the mirrored transaction log and the copied database schema at the remote site; and

asynchronously applying the reconstructed transactions at the replicate database while the replicate database remains available for use;

wherein the primary site and the remote site are different physical locations.

37. A computer storage medium encoded with a computer program, the program comprising instructions that when executed by one or more processors cause the one or more processors to perform operations comprising:

monitoring transactions occurring at the primary database for detecting changes made to the primary database;

recording information about transactions observed to have occurred at the primary database in a transaction log;

synchronously copying the information about the transactions recorded in the transaction log to a remote site, so as to create at the remote site a mirrored transaction log that is guaranteed to contain at a synchronized point in time an exact copy of the transactions recorded in the transaction log at the primary database;

copying, database schema from the primary database to the remote site to enable transactions to be reconstructed and applied at the replicate database when the database schema is determined to be modified;

wherein said monitoring and recording steps occur at a primary site;

wherein, while the replicate database remains on-line and available for use, changes made at the primary database are replicated to the replicate database by;

said transactions at the replicate database being reconstructed based on the information about the transactions copied to the mirrored transaction log, the information including database schema of the primary database identified as modified and copied to the remote site; and

the reconstructed transactions being asynchronously applied at the replicate database;

wherein the primary site and remote site are different physical locations.