Transactionally consistent change tracking for databases

• US 20040199552A1
• Filed: 04/01/2003
• Published: 10/07/2004
• Est. Priority Date: 04/01/2003
• Status: Abandoned Application

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Hollingsworth, John Matthew, Ward, Sandra L.
• Application Number:
  US10/404,229
• Publication Number:
  US 20040199552A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 16/275 Synchronous replication

Abstract

A change tracking technique is used to maintain the consistency of the source data with the destination data during transactional replication. A first functional characteristic is that it is a transactional database replication technique. A second characteristic is that the technique supports an incremental tracking of transactional states of the source system. A third characteristic of the technique is that it utilizes triggers to track changes while satisfying the first two characteristics.

Claims

1. A method of maintaining a transactionally consistent group of tables at a destination database, comprising:

capturing a plurality of changes from a source database;

grouping the changes into at least one consistent set; and

applying at least one consistent set of changes to the destination database.

2. The method of claim 1, wherein capturing the plurality of changes comprises:

for each of a plurality of events;

recognizing a database event;

firing a trigger responsive to the event;

generating an identifying number for the event; and

storing data related to the event and the identifying number in a change tracking table as a change.

3. The method of claim 2, wherein grouping the changes comprises:

identifying the plurality of consistent sets of changes, and

storing information to recover the consistent sets in a consistent set table.

4. The method of claim 1, wherein each consistent set comprises a portion of the plurality of changes, and for each change in the portion, all other changes to source tables committed in the same transaction are also in the set.

5. The method of claim 1, wherein applying the change statements to the destination database comprises:

extracting a set of changes from change tracking tables based upon membership in a consistent set; and

applying the set of changes to the destination database in one transaction.

6. The method of claim 1, wherein the steps of capturing, grouping, and applying are asynchronous.

7. The method of claim 1, wherein each change corresponds to an event, the event being one of an insert, update, and delete on data in a source database.

8. A computer-readable medium having stored thereon computer-executable instructions for performing a method of maintaining a transactionally consistent set of destination tables at a destination database, comprising:

capturing a plurality of changes from a source database;

grouping the changes into at least one consistent set;

applying at least one consistent set of changes to the destination database.

9. The computer-readable medium of claim 8, wherein capturing the plurality of changes comprises:

for each of a plurality of events;

recognizing a database event;

firing a trigger in response to the event;

generating an identifying number for the event; and

storing data related to the event and the identifying number in a change tracking table as a change.

10. The computer-readable medium of claim 9, wherein grouping the changes comprises:

identifying the plurality of consistent sets of changes, and

storing information to recover the consistent sets in a consistent set table.

11. The computer-readable medium of claim 8, wherein each consistent set comprises a portion of the plurality of changes, and for each change in the portion, all other changes to source tables committed in the same transaction are also in the set.

12. The computer-readable medium of claim 8, wherein applying the change statements to the destination database comprises:

extracting a set of changes from change tracking tables based upon membership in a consistent set; and

applying the set of changes to the destination database in one transaction

13. The computer-readable medium of claim 8, wherein the steps of capturing, grouping, and applying are asynchronous.

14. The computer-readable medium of claim 8, wherein each change corresponds to an event, the event being one of an insert, update, and delete on data in a source database.

15. A system for maintaining transactional consistency between source tables in a source database and destination tables in a destination database, comprising:

at least one source table having an associated trigger, the trigger executing in response to an event, each source table associated with the source database;

at least one change tracking table for recording data related to the change event; and

a consistent set table to group the entries of at least one change tracking table into at least one ordered consistent set.

16. The system of claim 15, wherein each of the source tables has at least one corresponding change tracking table.

17. The system of claim 15, wherein the change tracking table is populated with data related to the event and an identifying number for the event in response to the trigger executing.

18. The system of claim 17, wherein the entries are grouped into consistent sets of changes.

19. The system of claim 15, wherein for a plurality of events corresponding to changes, each consistent set comprises a portion of the plurality of changes, and for each change in the portion, all other changes to source tables committed in the same transaction are also in the portion.

20. The system of claim 15, wherein the change tracking table comprises at least one change to be applied to the destination database.

21. The system of claim 15, wherein data is extracted from the change tracking tables based on membership in a consistent set of changes identified via the consistent set table.

22. The system of claim 21, wherein a set of extracted changes is applied to the destination database in one transaction.