SQL Server replication entry (2)-replication Modes

Introduction

This seriesArticleIn the previous article, I gave an overview of what replication is. This article describes several common replication modes based on the organization and replication types of the Publishing Server, distribution server, and subscription server.

 

Mode Selection

The replication mode depends on multiple aspects. First, consider the specific business needs, and then consider the hardware and network restrictions. The business requirements can be divided into two aspects:Autonomy and latency. Autonomy refers to "the degree to which data is not affected". For example, in a business scenario, the company's headquarters are in Beijing, and the publishing servers and distribution servers are all in the Headquarters. Each regional branch has a subscription server, use snapshot replication to receive the company employee address book that is pushed and subscribed to by the Headquarters once a month. In this business scenario, the subscription server only receives the address book information published by the publishing server. modifications to this information will not be sent back to the Headquarters server, the autonomy of this business scenario is very low. For latency, it is "the time when the data on the Publishing Server is modified and applied to the subscription server". For example, in the above example, the subscription cycle of each subscription server is one month, during this period, the address book of the Headquarters may have been modified several times, but it will be synchronized to the subscription server after one month. Therefore, the latency in this scenario is very high.

Second, there are hardware and network restrictions. For example, if you set the Publishing Server and distribution server on one server, can the CPU be able to bear these burdens under the existing circumstances? Or is the network bandwidth between the publishing server and the subscription server capable of transmitting data within a certain release period using snapshot replication?

After a brief understanding of the pattern selection criteria, let's look at several common replication modes.

 

Replication mode centered on the Publishing Server

In this mode, multiple subscription servers subscribe to one Publishing Server as the center, as shown in 1.

Figure 1. Publish server-centric mode for multiple subscription servers

 

This mode is also the simplest mode in the replication mode, which can use snapshot publishing and transaction Publishing. It is not hard to see that the autonomy of such scenarios is relatively low, so this mode is suitable for the following business scenarios.

• The subscriber server is used to generate reports.
• The Publishing Server is used to publish master information, such as the employee address book and product data, similar to the previous article.
• Use transaction publishing to make the subscriber take part of the load
• As an emergency backup server after the Publishing Server is down

 

Of course, the defects of this model are also obvious.

• The first problem is whether the server hardware can withstand the CPU and memory consumption on the same server as the distribution server.
• In the OLTP environment, if the amount of data to be modified is too large every day, for example, more than 10%, the large amount of data that needs to be transferred to the subscription server is also a problem to be considered.

 

Replication mode centered on distribution server and distribution server

This mode is actually not much different from the previous mode, but separates the publishing server and the distribution server, as shown in figure 2.

Figure 2. Replication mode centered on distribution server and distribution server

 

This mode transfers the load from the distribution task to the CPU, memory, and network to another distribution server. This reduces the load on the Publishing Server and supports more subscription servers. In addition, we know that one distributor supports multiple publishers, so we can also use one distributor for multiple publishers.

Another advantage of this mode is that the distribution server can be placed in the DMZ region and connected to the subscription server to avoid direct leakage of the distribution server over the Internet.

Of course, the most important thing about this mode is that the publishing server and the distribution server must have reliable network connections. This mode is basically the same as the business scenario used in the first mode mentioned in Figure 1.

 

Publishing-subscription replication Mode

In this mode, the Publishing Server is also the subscription server, as shown in 3.

Figure 3. Publish-subscription replication Mode

 

This mode is applicable when the network of server a and server B is very expensive, but the network cost of server B and each subscription server is very low. For example, the company is headquartered in Beijing, server A is located in Beijing, server B is located in Shanghai, and each subscription server is connected to server B through a LAN. In this example, the cost of Connecting Server A and server B through VPN is very expensive, and the cost of Connecting Server B and each subscription server through LAN can be ignored.

 

Subscription server-centered replication Mode

This mode is centered on the subscription server, as shown in figure 4.

Figure 4. Subscription server-centric replication Mode

 

This mode is applicable to scenarios where business data of each region is aggregated to the headquarters.

 

Replication mode for multiple publishing servers and multiple subscription servers

This mode is applicable to Data peer-to-peer environments, as shown in simplified version 5.

Figure 5. Multiple publishing servers and multiple subscription servers

 

This model is very suitable for business data peer-to-peer environments. For example, in such business scenarios, a sales company has three branches in the same city, inventory is synchronized between them through replication. So that each store can understand the inventory of other stores. This type of business scenario is suitable for the replication mode of multiple publishing servers and multiple subscription servers.

 

Transaction publishing mode with updatable subscription

This mode is very similar to the mode described in Figure 1, but this mode allows the subscription server to update data. 6.

Figure 6. Publish mode with updatable subscription transactions

 

In this mode, for example, if subscription server B updates the data, the data will be sent back to the publishing server. If the publishing server receives the data, the data will be synchronized to other subscription servers at the same time.

 

Merge publishing Mode

The combined publishing mode is applicable to scenarios where all servers share part of the data, as shown in figure 7.

Figure 7. Combined publishing Mode

 

In this mode, each server is not subscribed to each other, but shared with each other. This mode also applies to the business model described in Figure 5.

 

Summary

This article describes several replication modes and their applicable scenarios. Most of the more complex replication modes are combinations or extensions of the above modes. Understanding the above simple replication mode is the basis for understanding the complex replication mode.