MS SQL Basics Tutorial: Snapshot replication

16.3.1 snapshot Replication Overview

Snapshot replication is a time to the publication of the data for a "photo" to generate a description of the publication of the data in the database instantaneous state of the static file, and finally at the specified time to copy it to the Subscriber database. Snapshot replication does not constantly monitor and track data changes that occur in the publishing database as transactional replication does, and it replicates content that is not an INSERT, UPDATE, DELETE statement (a feature of transactional replication), nor is it limited to those modified data (features of merge replication). It actually makes a periodic table refresh of the subscription database, sending the data from all the publication databases from the source database to the target database, not just the data that changed. If the paper is very large, then to replicate a lot of data, so the demand for network resources is higher, not only to have a faster transmission speed, but also to ensure the reliability of transmission.

Snapshot replication is the simplest type of replication that can guarantee the potential consistency of transactions between publishers and Subscribers. Snapshot replication is typically used on the following occasions: when there is no need for real-time data, such as when making decision support, querying static table information, read-only Subscribers (the Subscriber does not modify the publication data), and do not need the most recent data, the database is modified and the amount of data is reduced with the immediate updating of the Subscriber.

The execution of snapshot replication requires only the Snapshot Agent and the Distribution Agent. The Snapshot Agent prepares the snapshot files (including the published table's data files and description files) and stores them in the Distributor's snapshot folder, in addition to the Snapshot Agent tracking synchronization jobs in the Distributor's distribution database. The Distribution Agent distributes the snapshot jobs in the distribution database to the destination table for the subscriber server. The distribution database is for replication only and does not include any user tables.

Each time the Snapshot Agent executes, it creates a data file and a description file (also known as a synchronized collection) that will be distributed to the Subscriber. The Snapshot Agent does this primarily through the following steps (see Figure 16-24).

(1) The connection from the Distributor to the Publisher and the sharing of locks on all the tables in the publication paper. Shared locks are designed to ensure the consistency of snapshot data, because shared locks prevent all other users from modifying the table. The Snapshot Agent should be scheduled to execute during less database activity.