官术网_书友最值得收藏!

Types of Replication

SQL Server has snapshot, transactional, and merge replication. Each replication type is best suited for one or more sets of scenarios. This section discusses different types of replication and scenarios in which they should be used.

Transactional Replication

Transactional replication, as the name suggests, replicates the transactions as and when they are committed at the publisher to the subscribers.

It's one of the most commonly used replications to load balance read-write workloads. The writes are done at the publisher and the reads (or reporting) are done at the subscriber, thereby eliminating read-write blocking. Moreover, the subscriber database can be better indexed to speed up the reads and the publisher database can be optimized for Data Manipulation Language (DML) operations.

The log reader and distribution agent carry out the transactional replication, as stated earlier. The agents are implemented as SQL agent jobs, that is, there's a SQL agent job for a log reader agent and a SQL agent job for the distribution agent.

There are two other transactional replications that allow changes to flow from subscriber to publisher: transactional replication with updatable subscription (bidirectional transactional replication) and peer-to-peer transactional replication.

Transaction replication is discussed in detail in Lesson 2, Transactional Replication.

Merge Replication

Merge replication, as the name suggests, replicates changes from publishers to subscribers and from subscribers to publishers. This sometimes results in conflict in cases where the same row is updated with different values from the publisher and subscriber.

Merge replication has a built-in mechanism to detect and resolve conflicts; however, in some cases, it may get difficult to troubleshoot conflicts. This makes it the most complex replication type available in SQL Server.

Merge replication uses the merge agent to initialize subscribers and merge changes. Unlike transaction replication, where the snapshot agent is used to initialize subscribers, in merge replication, the snapshot agent only creates the snapshot. The merge agent applies that snapshot and starts replicating the changes thereafter.

Merge replication isn't covered in this book as it's not used as an HA and DR solution anymore.

Snapshot Replication

Snapshot replication generates a snapshot of the articles to be replicated and applies it to the subscriber. The snapshot replication can be run on demand or as per schedule. It's the simplest form of replication and is also used to initialize transactional and merge replication.

Consider the following diagram:

Figure 1.6: Snapshot replication example

The preceding diagram demonstrates how snapshot replication works. The finance database is replicated from publisher to subscriber. Here's how it works:

  1. A publication for the finance database is created at the publisher.
  2. The snapshot agent creates the snapshot (.sch files for object schema and .bcp files for data). The snapshot files are kept at a shared folder that's accessible by the publisher and the distributor.
  3. A subscription for the finance publication is created at the subscriber.
  4. The distribution agent applies the snapshot at the subscriber's finance database.
主站蜘蛛池模板: 绥中县| 太康县| 内黄县| 昭觉县| 开平市| 济南市| 枣强县| 桑日县| 南宁市| 东港市| 政和县| 紫阳县| 七台河市| 加查县| 吴忠市| 中超| 绿春县| 华安县| 三门县| 金塔县| 华坪县| 亚东县| 宜川县| 东安县| 茶陵县| 邹平县| 涪陵区| 大渡口区| 新田县| 临海市| 蒙城县| 河北省| 花莲市| 弥勒县| 黔南| 申扎县| 宿松县| 九龙县| 东阳市| 砀山县| 大姚县|