Copy (1) SQL server copy Overview

SQLServer replication technology has emerged at least in the SQLServer2000 era. It was originally for distributed computing, not for high availability. However, replication has become a highly available technology and is widely used. Many problems are solved through replication. Copy component: replication is a publishing and subscription mode, but this mode

SQLServer replication technology has emerged at least in the SQLServer2000 era. It was originally for distributed computing, not for high availability. However, replication has become a highly available technology and is widely used. Many problems are solved through replication. Copy component: replication is a publishing/subscription mode, but this mode

Preface:

SQLServer replication technology has emerged at least in the SQLServer2000 era. It was originally for distributed computing, not for high availability. However, replication has become a highly available technology and is widely used. Many problems are solved through replication.

Copy component:

Replication is a publishing/subscription mode, but this mode is not easy to understand. The following describes some concepts of replication technology. Note that the Replication here is not COPY, but Replication. It consists of three components: publisher, publisher, and subscriber. The three items are indispensable, and the items defined in the release and subscription are the operational units.

• Project (Article): it is the smallest configurable operation unit in replication. It can be a separate SQL Server object or an object itself. These objects are usually tables, views, and stored procedures. It can also be a set of some rows or columns in a table. In a non-strict environment, you can define multiple objects as a project to publish and subscribe.
• Publication: A logical set of projects. Each release can contain one or more projects. A configuration item in a release affects all the projects it contains. In this way, the impact of management objects can be reduced. The most important configuration item is the replication type.
• Publisher: runs the released instance. The Publisher monitors changes to all projects and notifies the Publisher of the information.
• Distributor: an instance that tracks changes to all subscription and release activities. It acts as the intermediary during the replication process, most changes will be stored in a distribution database (stored in the system database directory with the database name distribution), which can be a separate instance, a subscription server, or a Publishing Server, but it usually runs on the machine where the publisher is located.
• Subscriber: A Subscriber is also an instance used to receive information sent from all publishers by the Subscriber.
• Subscriber: paired with publish, used to determine which server (subscriber) receives information transmitted from publish. Each subscription creates a connection between the publisher and the subscriber. Replication Technology has two subscription methods: push and pull ). For push and subscription, the publisher updates data directly in the subscription database. For subscription extraction, the subscriber periodically asks the submitter if any new change is available. If yes, the submitter updates its own data.

In the figure, the upper part is the case where both the publisher and the publisher are on the same instance, and the lower part is that the three instances are separated.

Copy type:

Roughly speaking, SQLServer has three main replication types: snapshot replication, merge replication, and transaction replication. Here, we will briefly introduce each type and demonstrate it in detail later.

Snapshot copy:

During each running, a complete copy of the released object and its data are created, and the content of each table is written to the snapshot folder using the BCP tool of SQLServer. These snapshot folders are shared folders created by the senders. All participants in the replication process must be able to access the snapshot folder.

After each snapshot Copy starts to run, the publisher will capture the current snapshot of the configured release item from the release, send it to the subscriber, and then apply it to the subscription database. When a new snapshot is applied, the items on the subscribed database will be deleted and rebuilt based on the content of the new snapshot. This process will only be executed once each time the snapshot copy is started. There is no continuous data flow between the publishing and subscription ends, in addition, this process is a high bandwidth and storage overhead operation.

By default, other types of replication synchronize all the subscription items through the publisher through snapshots during initialization. This type of replication is most suitable for relatively static data environments.

Transaction replication:

A transaction-based replication type. Each committed transaction in the publish item is scanned and transmitted to the subscription end by the distributor. This scan is performed by the log reader agent by reading the transaction logs in the published database. If there are changes in the released items, the changes will be recorded in the distribution database of the distributor. Then, the database is applied to the subscriber according to the current subscription type.

Transaction replication can be close to real-time synchronization, and only occupies a small amount of publisher space. At the same time, this type of replication can be configured as two-way data movement, but the original design of transaction replication is only used for one-way.

Merge and copy:

The original design was used to allow modifications to occur on publishers and subscribers. At the same time, merged replication is useful in scenarios where the subscriber cannot connect to the publisher continuously and may connect to the publisher the next day. In this scenario, data can be synchronized every night, but a conflict occurs when a data is modified by the data of different publishers at the same time. Some configurations can be used.