Introduction to SQL Server replication Article 1: Approaching replication (I)

SQL ServerReplicatioN Article 1: Approaching replication (I)

For many SQL Server DBAs, replication is not a new thing.Copy", Many friends have already used it in the project. Like other technologies: Some people can use it well, and some people complain that the technology is not good.

WeAgilesharpThe Team has also gone through many highly available cases, and replication is still very valuable. Therefore, we sorted out a lot of resources and decided to release a series of ReplicationArticleFirst, to help everyone understand replication, and to pave the way for high availability in the future.

For more information about high availability, see this article.TalkHigh Availability of SQL Server.

Understand publishing industry publications

When talking about replication, the common point is to transfer data and synchronize data. Since data is synchronized and transmitted, it can be performed in real time or separated by time. Since it is synchronous, there may be multiple data sources, and the data source location can be arbitrary, maybe on the same server, or on different servers or even networks.

In order to better discuss replication, it is necessary to discuss several important components involved: distributor (Distributor), Publisher, subscriber, publication, article, and subscriptions ). In order to make everyone better understand these components, first of all, I suggest you combine the replication data synchronization process with the publishing house to send publications to readers. Next, let's take a look at three important components: publisher, distributor, and subscriber ). And then expand them one by one.

 

 

The following describes replication with the help of the figure above and the example of the publishing house.

 

First, let's take a look at the example of the publication and subscription publications that we are most familiar.

 

 

The entire process is as follows:

1. The reader (subscribers) goes to the publishing house (publisher) and requests to subscribe to books and periodicals.

2. Since there is a lot of data, readers subscribe to agilesharp technology weekly (called subscriptions) in the publishing house ).

3. The publisher agrees with the reader's requirements and adds the reader to their subscriber list.

4. The publication was published, and the Publishing House informed the following agents at all levels (distributor) of the new book.

5. agents at all levels send people to bring the list of new books and subscribers.

6. The agent makes a record of new book sales every time to facilitate subsequent settlement.

7. The agent then delivers the new book to every reader.

 

 

Further discussion Replication Components

Replication uses publishing terminology to represent components in the replication topology, including the Publishing Server, distribution server, subscription server, publishing, project, and subscription:

· Magazine publishers (publishing servers) produce one or more publications (publishing)

· Publications (Publications) include articles (projects)

· Publishers (publishers) can issue (distribute) magazines directly or use Publishers (distribution servers)

· Subscribers (subscriber) receive subscribed publications (published)

Although the journal term helps to understand replication, it is important to note that SQL Server replication contains a feature that is not described in this term, in particular, the subscription server updates and the server sends incremental changes to the released project.
The replication topology defines the relationship between servers and data copies, and clarifies the logic that determines how data flows between servers. The replication process (called a "proxy") is responsible for copying and moving data between the publisher and the subscription server.

This is an overview of the components and processes involved in replication.

Publishing Server

A Publishing Server is a database instance that provides data to other locations through replication. The Publishing Server can have one or more releases. Each release defines a set of logically related objects and data to be copied.

Distributor

A distributor is also a database instance that acts as a storage zone and is used to copy specific data associated with one or more publishing servers. Each Publishing Server is associated with a single database (called a distribution database) in the distribution server. The distribution database stores the copy status data and metadata about the release, and in some cases it queues data that is moved from the Publishing Server to the subscription server. In many cases, a database server instance acts as a publishing server and a distribution server. This is called "local distribution server ".

When the distribution server and the distribution server are configured according to their respective database server instances, the distribution server is called the remote distribution server ".

Subscription Server

The subscription Server is a database instance that receives replicated data. The subscription server can receive data from multiple publishing servers and releases. Based on the selected replication type, the subscription server can also pass the data changes back to the publisher or re-publish the data to another subscription server.

Project

Project is used to identify the database objects contained in the release. A single release can contain different types of projects, including tables, views, stored procedures, and other objects. When a table is published as a project, you can use a filter to restrict the columns and rows of data sent to the subscription server.

Release

Publishing is a collection of one or more projects in a database. Grouping multiple projects into one release makes it easier to specify a group of database objects and data that are copied as a unit and have a logical relationship.

Subscription

Subscription refers to the request to pass the published copy to the subscription server. Subscription defines the time and place at which the subscription will be published and received. There are two types of subscription: Push subscription and request subscription. The example of our previous publishing house is push subscription, that is, push.