SQL Server replication subscriptions and publications

Replication technologies for SQL Server include subscriptions and publishing

Components of Replication: publishers (Publishers), distributors (post Offices), Subscribers (readers, recipients)

Role of the Publisher: maintain the source database, data published by these databases can be exploited for replication, detect and send changes to all published data to the Distributor

Distribution server: The Distributor includes the distribution database and stores metadata, historical data, and transactions. Depending on the type of replication being implemented, the role of the distributor can vary, and in general, the Distributor is more important to the role of snapshot replication and transactional replication compared to merge replication.

The role of the Subscriber: to maintain a copy of the data and to receive changes to the modified publication. Depending on the replication options that are implemented, it may also allow the updater to update the data and replicate it back to the server or to other Subscribers.

Mode of replication: Push mode (push), Pull mode difference: The Distribution Agent runs at the Distributor, which is push mode, which is the pull mode at the Subscriber.

Types of replication: snapshot replication, transactional replication, merge replication. Allows subscription updates for snapshot replication. Allows subscription of updated transactional replication.

Detailed classification: Snapshot replication with immediate update, transactional replication with immediate update, both of which are distributed transaction processing principles. Snapshot replication is the basis for transactional replication, including information such as the table structure, which is transmitted by snapshot replication. It is created at the Subscriber, and then the Distribution Agent transmits the data.

Publication: A whole published by the publisher. One publication can include one or more articles, which can be data or database objects. Because all articles in the publication can be kept in sync at the same time. A publication is the basis of a subscription, and a subscription to a publication includes all articles in a publication, and each user database is able to construct one or more publications.

Article: A part of a publication that filters partition data all or part of a table or database object (stored procedures, views, user-defined functions).

Replication agent: Snapshot Agent (Snapshot Agent) distributed Agent (Distribution Agent) log read agent (Log Reader Agent) Merge Agent (Merge Agent) queue read proxy (Queue Reader Agent)

How snapshot replication works:

1, the publisher, the database will be published a snapshot of the whole,

2. The Snapshot Agent of the subscriber reads the snapshot from the publisher and places it in the local snapshot folder

3. The publisher of the Subscriber publishes the snapshot in the snapshot folder to the Subscriber. History and snapshots are recorded at the Distributor.

Fundamentals of Transactional replication:

1, the initial data and architecture (through snapshot replication to complete), from here can be reflected in snapshot replication, is the basis of all replication. Snapshot replication establishes a subscription database at the Subscriber.

2, after the data modification of the publisher, write the transaction log,

3. The log read agent of the distributor reads the transaction log of the changed data and stores these transaction logs in the publisher's publication database.

4. The Distribution Agent of the Distributor distributes the transaction logs from the distribution database to the individual subscribers, and then records the history and errors in the distribution database.

SQL Server replication subscriptions and publications