MS SQL Basics Tutorial: Transactional replication

Features of 16.4.1 transactional replication

Earlier we pointed out that the essence of replication is to copy data from the source database to the target database, but there is always a difference for different types of replication. From the specific content of replication, snapshot replication is the true meaning of data replication, regardless of the type of data received (such as the table deleted before rebuilding or delete the data in the table but preserve the table structure), in the network transmission of data. Transactional replication transfers transactions across the network (by one or more inserts, deletes, and updates); From the amount of data transferred, transactional replication transmits only the changes that occurred to the Subscriber, an incremental replication, but snapshot replication copies the entire publication to the Subscriber.

Because transactional replication constantly monitors the data changes in the source database, its server load is heavier than snapshot replication.

When a publishing database changes in transactional replication, the change is immediately delivered to the subscriber and completed in a short time (seconds or less), rather than a long interval of time as snapshot replication does. Therefore, transactional replication is a nearly real-time method of distributing data from the source database to the target database. Due to the high frequency of transactional replication, it is important to ensure that there is a reliable network connection between the Subscriber and the publisher.

Transactional replication only allows publishers to modify replicated data (if you set up an immediate updating subscriber option, allows subscribers to modify replicated data, unlike merge replication, all nodes (publishers and Subscribers) are allowed to modify replicated data, so transactional replication guarantees transactional consistency. It implements transactional consistency between immediate transaction consistency and potential transactional consistency.

Because transactional replication distributes data to subscribers in minimal Sienne, publishers are always connected to subscribers. However, in snapshot replication, it is not necessary to allow subscribers and publishers to maintain a permanent connection because of the long delivery interval between neighboring two replicated data.

Another unique feature of transactional replication is the support for parallel snapshot processing, which is also a new feature of SQL Server 2000 transactional replication. As described in the snapshot replication section, in general, a shared lock is placed on the publication table during the entire process of creating the initial snapshot file to prevent more publication. Parallel snapshot processing, which is supported by new but transactional replication, allows the shared locks not to be persisted to the end of the snapshot file creation throughout the snapshot file creation process. The specific process is that the Snapshot Agent places a shared lock on the publication table at the beginning of the replication. The shared lock is freed when the event that represents the start of the snapshot is written to the transaction log. This allows the publication table to be modified at a later time, even if the snapshot file is still in the build process. This shows that shared locks last a short time in the publication table. The time to release the shared lock is when the Snapshot Agent begins to create the snapshot file. At the end of the snapshot file creation. Indicates that the event that the creation ended is logged in the transaction log. What happens when the entire snapshot is generated from the beginning to the end the transaction of the publication table is sent to the distribution database by the log reading agent.