This article discusses why article processing order is important.
- In many instances, the Merge Agent must process changes to articles that participate in Declarative Referential Integrity (DRI) constraints in a specific order to attain optimal performance. If not, the Merge Agent may have to retry Data Manipulation Language (DML) operations it tried in an incorrect order (that is, try to INSERT a child row ahead of that of its parent).
- Applications that use triggers to maintain referential integrity require the Merge Agent to send changes in a specific order. If the Merge Agent sends changes in an incorrect order, the trigger will most likely roll back the change and the change does not propagate throughout the replication topology.
For the two reasons that were listed earlier, the order in which the Merge Agent delivers changes to a partner replica is important.
Before beginning a discussion of article processing order, it is important to have an understanding of two key concepts. The two key concepts are as follows:
- An article nickname.
- A generation.
A nickname is an integer value that the Merge Agent uses to identify an article (a SQL Server table) to merge replication. The merge setup process assigns an article nickname when it adds the article to a merge publication. If an article participates in DRI constraints, the merge setup process tries to generate an article nickname that reflects defined DRI constraints. The merge process assigns tables referenced by a FOREIGN KEY constraint (a parent) a smaller article nickname than that of the referencing table (the child table, or the table on which the FOREIGN KEY constraint is defined).
If a table does not participate in DRI constraints, the merge setup process assigns the article nickname based on the order in which it adds the article to the publication (in ascending order).
A generation is an integer value that the Merge Agent uses to track a logical group of changes to a particular article. All of the changes made to a particular article at a particular replica between merge synchronizations are associated with the same generation. Each time the Merge Agent runs, it closes the existing open generation, and then opens a new generation with which to associate the next set of changes.
Processing INSERTs, UPDATEs, and DELETEsThe Merge Agent partitions the articles for a particular publication into two distinct groups:
- The Merge Agent places articles that are not involved in any join filter relationships, and not related through DRI to any articles involved in join filters into one group.
- The Merge Agent places articles that are explicitly involved in join filter relationships, and articles related to join filter articles through DRI into a second distinct group.
The Merge Agent uses the groups to determine the overall processing order of UPDATEs, INSERTs and DELETEs for all articles defined to the publication.
In each of the two respective groups, the Merge Agent processes INSERTs and UPDATEs in ascending article nickname order, and processes DELETEs in descending article nickname order. First, the Merge Agent processes all DELETEs in their entirety in a particular group, followed by UPDATEs and INSERTs (also in a particular group). Conceptually, the Merge Agent appends the two aforementioned groups to one another (not merged) in the order listed previously. The Merge Agent begins by processing DELETEs for the first group, and then extends DELETE processing to the second group and the remainder of changes for the two groups are processed in parallel. Although the Merge Agent maintains article processing order in each respective group, the Merge Agent does not maintain strict article processing order across the two respective groups. As such, in the case of an INSERT or UPDATE, it is possible that changes from the first group with a higher article nickname can arrive ahead of those from the second group with a lower nickname. The converse situation can also occur for a DELETE. Both of these behaviors are by design.
Possible effects of generation batching on article processing orderAs stated earlier, with a generation you can logically group changes (INSERTs, UPDATEs and DELETEs) that occur for a particular article at a particular replica between synchronization sessions. Ultimately, the Merge Agent works with generations when it determines which changes it must exchange between two replicas. The Merge Agent negotiates a common generation at the following points in the synchronization process:
- Before it uploads changes from the subscriber to the publisher.
- Before it downloads changes from the publisher to the subscriber.
The Merge Agent processes generations in batches, also referred to as generation batches. By default, 100 generations are included in each generation batch that the Merge Agent uploads to the publisher from the subscriber, or downloads to the subscriber from the publisher. The generation batch size is configurable through the
-UploadGenerationsPerBatch and the -DownloadGenerationsPerBatch Merge Agent parameters, or through the Merge Agent profile. In the default case, if there are more than 100 generations that you need to exchange (that is, download and upload, or both) between a publisher (or a re-publisher) and a subscriber, the Merge Agent processes multiple generation batches. The number of batches depends on the number of generations that the Merge Agent has to exchange, and the generations per batch settings in-force for a particular merge session.
In a situation where multiple generation batches are exchanged, the Merge Agent may split related parent and child changes across two separate generation batches. If that is the case, the Merge Agent may deliver a child change in a generation batch ahead of the generation batch that contains the associated parent change. In hierarchical merge topologies that use re-publishers, there is one rare situation in which the splitting of parent and child changes across generation batches can lead to non-convergence. For more information about non-convergence, see the following article in the Microsoft Knowledge Base:
You can increase the -UploadGenerationsPerBatch
and the -DownloadGenerationsPerBatch parameters discussed previously to avoid splitting parent and child changes across generation batches.
Article processing order is maintained in a particular generation batch pursuant to the rules discussed previously. However, the Merge Agent cannot maintain article processing order across generation batches.
ID articol: 307356 - Ultima examinare: 6 apr. 2011 - Revizie: 1