System and method of distributing replication commands

• US 7,707,181 B2
• Filed: 02/19/2003
• Issued: 04/27/2010
• Est. Priority Date: 02/19/2003
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Guo, Qun, Habben, Michael E.
• Primary Examiner(s): Cottingham; John R.
• Assistant Examiner(s): TIMBLIN, ROBERT M
• Application Number:
  US10/370,389
• Publication Number:
  US 20040162859A1
• Time in Patent Office: 2,624 Days
• Field of Search: 707200-204, 714/2, 714/16
• US Class Current: 707/636
• 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 transactional replication system wherein each replicated command applies to a single row as identified by the primary key, replicated commands are dispatched among multiple connections by hashing of primary key from a single command queue to ensure that changes to the same data row are always dispatched to the same connection to maintain the order of commands as originally occurred on publisher. All connections commit their transactions at the same time in a coordinated fashion without a two phase commit. Retry logic based on a logical sequence number is used to reapply commands from any connection that fails to commit.

Claims

1. A system for distributing replication commands from a distributor to a subscriber, the system comprising:

a memory;

a distributor for execution on a first computer in a network including a plurality of computers, the distributor comprising;

a command queue that receives transactional replication commands from a publisher and stores the transactional replication commands in the memory, wherein each replication command corresponds to a change in data in a data row and is associated with a respective hash of a primary key of the data row to which the replication command is associated;

a plurality of command buckets, wherein each of the plurality of command buckets is associated with a respective connection to the command queue and only one data row, and wherein at least a portion of a communication and logging overhead associated with a two phase commit operation is avoided by including for each of the plurality of command buckets an executor that executes a coordinated commit operation upon the plurality of command buckets at substantially the same time;

a dispatcher, wherein the dispatcher;

sorts the transactional replication commands stored in the command queue as a function of each hash associated with a transactional replication command, and by applying a logical sequence number of commit record in order to identify which commands amongst the transactional replication commands of the command queue need to be re-applied as a result of an unsuccessful commit operation;

transmits each transactional replication command in the command queue to an associated command bucket as a function of the hash associated with the transactional replication command using the respective connection for the associated command bucket such that each command bucket stores a respective set of replication commands having a same hash value;

marks each of the command buckets as ready to be committed;

stops sending new transactional replication commands to the marked command buckets; and

waits to receive from each executor, an event that signals a successful completion of the coordinated commit operation, thereby detecting a mixture of successful and unsuccessful commit operations amongst the plurality of command buckets.

2. The system of claim 1, wherein as part of the coordinated commit operation, at substantially the same time, each executor reads from its associated command bucket and applies changes as a function of the reads from the associated command bucket to the subscriber over a plurality of connections.

3. The system of claim 2, wherein each executor is a thread running on a respective command bucket.

4. The system of claim 1, further comprising a retry mechanism.

5. The system of claim 4, wherein the retry mechanism identifies which commands have been committed successfully, and reapplies only the replication commands having newer logical sequence numbers than a last logical sequence number of a replication command to successfully commit.

6. A method for distributing transactional replication commands in a system having at least one of a distributor or a publisher on a first computer and a subscriber on a second computer, the method comprising:

receiving each transactional replication command and a respective associated indicia for each replication command from the publisher;

wherein each received replication command correspond to a change in a respective data row of a table, and wherein each indicia is a hash of a primary key of a respective row of the table on which the replication command operates;

storing the replication commands and the indicia in a command queue;

sorting the transactional replication commands stored in the command queue using a logical sequence number of commit record;

transmitting each transactional replication command in the command queue to an associated command bucket as a function of the associated indicia via a respective dedicated connection to the associated command bucket such that each command bucket stores a respective set of replication commands having a same hash value, wherein each command bucket is associated with a respective connection to the command queue and only one data row;

marking each of the command buckets as ready to be committed;

stopping new transactional replication commands from being sent to the marked command buckets;

avoiding incurring at least a portion of a communication and logging overhead associated with a two phase commit operation by executing a coordinated commit operation at substantially the same time;

waiting to receive from an executor associated with a command bucket, an event that signals a successful completion of the coordinated commit operation, thereby detecting a mixture of successful and unsuccessful commit operations; and

using the logical sequence number of commit record in order to identify which commands amongst the transactional replication commands of the command queue need to be re-applied as a result of an unsuccessful commit operation.

7. The method of claim 6, wherein executing the coordinated commit operation comprises reading each set of replication commands from each command bucket and applying changes to the subscriber based upon each replication command in the set, over a plurality of connections.

8. The method of claim 6, further comprising:

determining if the replication commands of each bucket have committed successfully at the subscriber.

9. The method of claim 8, further comprising:

restarting the commit process from an earliest failed point in accordance with the logical sequence number of commit record; and

applying only the replication commands having newer logical sequence numbers than a last logical sequence number of a replication command to successfully commit.

10. A computer-readable storage medium having computer-executable instructions for distributing transaction replication commands from a publisher to a subscriber, said instructions comprising:

receiving each transactional replication command and a respective associated indicia for each replication command from the publisher;

wherein each received replication command corresponds to a change in a respective data row of a table, and wherein each indicia is a hash of a primary key of a respective row of the table on which the replication command operates;

storing the replication commands and the indicia in a command queue;

sorting the transactional replication commands stored in the command queue using a logical sequence number of commit record;

transmitting each transactional replication command in the command queue to an associated command bucket as a function of the associated indicia via a respective dedicated connection to the associated command bucket such that each command bucket stores a respective set of replication commands having a same hash value, wherein each command bucket is associated with a respective connection to the command queue and only one data row;

marking each of the command buckets as ready to be committed;

stopping new transactional replication commands from being sent to the marked command buckets;

executing a coordinated commit operation at substantially the same time, thereby avoiding incurring at least a portion of a communication and logging overhead associated with a two phase commit operation;

waiting to receive from an executor associated with a command bucket, an event that signals a successful completion of the coordinated commit operation, thereby detecting a mixture of successful and unsuccessful commit operations; and

using the logical sequence number of commit record in order to identify which commands amongst the transactional replication commands of the command queue need to be re-applied as a result of an unsuccessful commit operation.

11. The computer-readable storage medium of claim 10, further comprising instructions for reading each set of transaction replication commands from each command bucket and applying changes to the subscriber based upon each transaction replication command in the set.

12. The computer-readable storage medium of claim 10, further comprising instructions for:

determining if the replication commands of each bucket have committed successfully at the subscriber.

13. The computer-readable storage medium of claim 12, further comprising instructions for:

restarting the commit process from an earliest failed point in accordance with the logical sequence number of commit record; and

applying only the replication commands having newer logical sequence numbers than a last logical sequence number of a replication command to successfully commit.