MS SQL Basics Tutorial: Merge replication

Overview of merge Replication

Perhaps the reader is not unfamiliar with the actual example, in a large enterprise distribution system, Sales Manager or some sales backbone personnel often to go out to deal with the business, the contract signed through the hand of the notebook computer to the headquarters sales information database. In this example, there are two major special features; any sales Manager or sales backbone can modify the sales information database; The source database is connected to the target database only when data is passed. In SQL Server, merge replication provides a better solution for this situation.

Merge replication as a way of distributing data from publishers to subscribers allows publishers and subscribers to modify published data, regardless of whether the subscriber is connected or disconnected from the publisher, and then merges the changes that occur at each node when all (or part) nodes are connected. In merge replication, each node completes its own task independently, unlike transactional replication and snapshot replication, where subscribers and publishers are connected to each other, without having to connect to other nodes at all, without having to use MS DTC to implement two-phase commits to modify publications across multiple nodes. The node is only connected to other nodes at some point (at which point the other nodes do not necessarily refer to all other nodes), and the data changes that occur are replicated to the databases of the connected nodes. If a conflict occurs when the same data is updated at the time of replication, the final result of the data is not always the result of the publisher's modification, nor does it necessarily contain all the modifications made on a node. Because each node has autonomy, it is possible to modify the publication (copy data) so that after the conflict is resolved according to the conflicting rules set, the final result of the database is often the modification of multiple nodes.

It can be seen that although all the last databases have the same result set, this result is formed with the participation of multiple nodes, and is the result of multiple modifications being merged into the target database. Therefore, merge replication does not maintain the consistency of transactions.

When you create a merged publication, SQL Server handles the database as well as the publishing table (see Figure 16-54) in comparison to creating a snapshot replication and transactional replication publication:

(1) SQL Server adds an identity column to each row in the publication table, which uniquely identifies the row between multiple copies of the table. If an identity column with the ROWGUIDCOL property already exists on the base table, SQL Server automatically identifies the row as the duplicate table, or if it is not, or when the tables are activated during the creation of the publication, or when the SQL Server Agent first serviced the publication, the SQL Server adds a rowguid with the ROWGUIDCOL property to the table.

(2) SQL Server adds a trigger to track changes in each row or column of data. These trace triggers are created when the captured changes are stored in several system tables, or when replication tables are activated during the creation of a publication, or when the SQL Server Agent first provides services for the publication.

(3) SQL Server adds user-tracked system tables to the database to perform conflict detection, resolution, and logging. Msmerger_contents Msmerger_tombstone system tables are used to track update, DELETE, and inserts operations on data in a publication.

16.5.2 merge Replication Execution steps

The execution of merge replication requires the Snapshot Agent and the Merge Agent. The main steps are:

(1) As in snapshot replication and transactional replication, the Snapshot Agent for merge replication also completes two tasks before beginning replication, and creating snapshot files (synchronized collections) is stored in the Distributor's replication directory, and synchronization jobs are recorded in the publication database. The Merge Agent distributes the initial snapshot files to the Subscriber to complete the order initialization (the publication database synchronizes with the subscription database).

(2) When a row of a table in a publication is modified by a node (the subscriber), the trigger triggers and sets the row's generation column to zero. When the Merge Agent executes, it combines all the generated columns into one or more groups, replacing the old values with the new values, whenever the new generated column value is larger than the original.

(3) When synchronizing, the Merge Agent copies all columns that generate a column value of zero (the modified column) to all other subscribers.

(4) in the target database, the data sent from the node is merged with the existing data, the Merge Agent is the conflict detection, and if there is no conflict, the replication data is received; If a conflict occurs, the Merge Agent resolves the conflict based on the default or set of conflict resolution rules.

See the full set of "MS SQL Basics Tutorials"