Generations

Each row tracked in MSmerge_contents and MSmerge_tombstone is assigned a generation number. A generation is a simple integer column that functions as a sort of logical clock that enables the Merge Agent to determine when a change was made to a row and how the time of that change relates to changes to the same row made by other parties in the replication scenario. An integer rather than a datetime value is used because it avoids having any sort of dependency on synchronized clocks between sites and is more resilient to common intersite issues such as time zone differences.

Only one row is maintained in MSmerge_contents for each row inserted or updated in a table. Each time a row is updated, its generation number is updated in MSmerge_contents with the current generation number as specified in MSmerge_genhistory. Each time the Merge Agent synchronizes the article with the publisher/subscriber, it updates the MSmerge_replinfo table to indicate the last generation that was sent and received (via calls to the sp_MSsetlastsentgen and sp_MSsetlastrecgen procedures, respectively), then calls sp_MSupdategenhistory to update MSmerge_genhistory to the next generation number for each article for which it retrieves changes.

A generation permits the changes against different articles to be organized into separate batches or groups. This might lead you to believe that generation numbers are unique to each article in a publication and serve as a sort of secondary key to the article ID, but that's not the case. Generation numbers are not reused across articles; they are global (across the MSmerge_ genhistory table) in scope. When the system needs a new generation number for an article that does not yet have a generation number in MSmerge_ genhistory (for example, when the first row is inserted into the table), it simply takes the maximum generation number in MSmerge_genhistory and adds 1 to it. Two articles in the same publication will not share a generation number or use the same generation number. Each article has its own current generation number, which is maintained in MSmerge_ genhistory. This allows for flexibly applying changes to the other party in a merge synchronization and allows the changes to different articles to be tracked separately.

The current generation number for a published database is incremented by Merge Agent runs. Until the Merge Agent runs, changes made to a particular article use the current generation number for that article when they're recorded in MSmerge_comments and MSmerge_tombstone.

To understand how merge generations work, let's walk through an example. Let's say that TableA and TableB are published in two separate merge publications and that they are the only two tables published from a given database. A couple of rows in TableA are updated, and these rows are recorded in MSmerge_contents with the current (maximum) generation for TableA found in MSmerge_genhistory, which is 2. The Merge Agent then runs, retrieves the updated rows from MSmerge_contents, and updates MSmerge_genhistory's maximum generation number for TableA to 4 because 3 is the maximum generation value used by any article in the table. Later, a row in TableB is updated. It records its modified rows in MSmerge_ contents using the current generation for TableB, which is generation 3. The Merge Agent runs again, picks up the new modifications, and updates MSmerge_genhistory to use generation number 5 for TableB because 4 is the maximum generation value on file in the table. At this point, the current generation for TableA is 4 and the current generation for TableB is 5. If we then update another row in TableA, that change will be recorded in MSmerge_contents using generation 4. When the Merge Agent runs and retrieves the changes made to TableA, it will set the current generation value for TableA to 6 because 5 is the highest generation value currently on file. So changes to TableA can be found under generations 2, 4, and 6; changes to TableB will be found under generations 3 and 5. Each time the Merge Agent sets the current value for a given article, it also sets the high-water mark for the next generation number that must be produced, regardless of the article.

In certain circumstances, the generation number for a row can be set to 0, independent of the generation number for its article recorded in MSmerge_genhistory. One example is when a row is updated at a publisher but deleted at a subscriber. Assuming the default conflict resolution strategy (where the publisher always wins), the update on the publisher will conflict with the delete on the subscriber, forcing the delete on the subscriber to have to be reversed. What happens in that scenario is that the row is reinserted at the subscriber, and an entry is made in the subscriber's MSmerge_contents table for that row, with a generation of 0. The row in the subscriber's MSmerge_tombstone table is deleted, and the subscriber behaves as though the deletion never occurred. The conflict is logged in the publisher's MSmerge_ delete_conflicts table with a reason_text value along the lines of, “The same row was updated at 'TUKPHRIP.Northwind' and deleted at 'TUK PHRIP.testrepl'. The resolver chose the update as the winner.”