Detailed description of consolidated replication in ms SQL Server database

 

Overview of merge replication

Readers may not be unfamiliar with the following examples. In the distribution system of a large enterprise, the sales manager or some key sales personnel often go out to process the business, transfer the signed contract to the Headquarters sales information database through the laptop at hand. In this example, there are two main features: any sales manager and sales backbone can modify the sales information database; the source database is connected to the target database only when data is transferred. In SQL Server, combined replication provides a better solution for this situation.

The merge replication act as a method for distributing data from publishers to subscribers that allows publishers and subscribers to modify the published data, regardless of whether the subscribers and publishers are connected or disconnected, and then when all (or part) nodes are merged when they are connected to each other. In merge replication, each node independently completes its own tasks. Unlike transaction replication and snapshot replication, buyers and Publishers need to connect to each other without having to connect to other nodes, you do not need to use ms dtc for two-phase commit to modify the publishing on multiple nodes, it is only at a certain time that the node is connected to other nodes (other nodes do not necessarily refer to all other nodes ), then, copy the data changes to the databases of these connected nodes.
If a conflict occurs when the same data is updated during replication, the final result of the data is not always the modified result of the publisher, nor is it necessarily included in all the modifications made on a node. Because each node has autonomy, you can modify the publications (copy data) so that after the conflict is handled according to the configured conflict resolution rules, the final result of the database is often the modification of multiple nodes.

It can be seen that although all databases have the same result set in the end, this result is formed with the participation of multiple nodes and is the result of merging multiple modifications into the target database. Therefore, merging and replication does not maintain transaction consistency.

Compared with creating snapshot copies and transaction replication publications, when creating a consolidated publication, SQL Server processes the database and the publishing table as follows:

(1) SQL Server adds an ID column to each row in the publishing table, so that the row can be uniquely identified among multiple copies of the table. If an identifier column with the rowguidcol attribute already exists in the basic table, SQL Server automatically uses it as the row ID of the copy table. If no, when these tables are activated during the creation of the publication, or when the SQL Server Agent provides services for the publication for the first time, SQL Server adds a rowguid with the rowguidcol attribute to the table.

(2) SQL Server adds a trigger to track the changes of each row or column of data, and stores the captured changes in several system tables, these tracking triggers will be created when the retabulation is activated during the creation of a publication, or when the SQL Server Agent provides services for the publication for the first time.

(3) SQL Server adds the system tables tracked by users to the database to detect, resolve, and record conflicts. The msmerger_contents msmerger_tombstone system table is used to track update, delete, and inserts operations on data in a publication.

Merge and copy execution steps

The execution of merge replication requires a snapshot proxy and a merge proxy. The main steps are as follows:

(1) similar to snapshot replication and snapshot proxy in transaction replication, the snapshot proxy for merged replication also needs to complete two tasks before starting replication. Create a snapshot file (synchronization set) it will be stored in the publisher's copy directory; and the synchronization job will be recorded in the Publishing Database. The Merge Agent distributes the initial snapshot file to the subscriber to initialize the subscription (synchronize the publishing database with the ordering database ).

(2) When a row of a table in the publication is modified at a node (subscription), the trigger is triggered and the generation column of the row is set to zero. When the merge proxy is executed, it merges all generated columns into one or multiple groups. If the new generated column value is larger than the original value, it replaces the old value with the new value.

(3) During synchronization, the Merge Agent copies all columns with zero generated column values (modified columns) to all other subscribers.

(4) In the target database, the data sent from the node is merged with existing data, and the proxy is merged for conflict detection. If no conflict exists, the copied data is received. If a conflict occurs, the merge proxy resolves the conflict based on the default or configured conflict resolution rules.

 

Http://tech.sina.com.cn/s/2009-12-14/15281173251.shtml