Introduction to SQL Server replication (1) -- Introduction to replication

Introduction

Replication in SQL Server is one of the core features of high availability of SQL Server. In my opinion, replication is not just a technology, but a collection of column technologies, this includes data forwarding from storage to synchronization to Data Consistency Maintenance. To use the replication function, you not only need to be familiar with the business, but also need to have a comprehensive understanding of the overall replication function.ArticleIt aims to make a simple and comprehensive discussion on replication in SQL Server. (PS: In my previous article, I found that image painting with mspaint in some articles is more appealing, but many people speak out, so without considering personal shame, some images in this series continue to use mspaint ).

 

What is replication?

Replication, which is derived from the Latin replicare. Replication in SQL Server also means that the core function of replication is to store and forward data. This means that after adding, deleting, and modifying data at a location, the operation is repeated to other data sources, as shown in Concept 1.

Figure 1. Basic concepts of Replication

 

Of course, the above model is the simplest model for replication. The actual model may be much more complicated, but most of the reasons for using replication can be divided into the following categories:

1. Server Load balancer-you can copy data to other database servers to reduce the load on the current server. For example, the most typical application is to distribute data to separate the OLTP and OLAP environments.

2. Partitioning-isolate frequently used data from historical data and copy historical data to other databases

3. Authorization-provide part of the data to the person who needs the data for use

4. data merging-each region has its own data and combines its data. For example, for a large company, each region has its own sales data, which needs to be summarized by the headquarters.

5. Failover-copy all data to facilitate failover during the fault.

 

Although there are many reasons for using replication, before using it, you must first understand the components required by replication technology.

 

Components of Replication

The concept of replication is similar to the model of publishing a magazine. After publishing a magazine from a publisher, it needs to be distributed to the people who subscribe to the magazine through a newsbox or other places. This concept also applies to SQL Server replication. For SQL Server replication, publishers, kiosks, and subscribers correspond to the Publishing Server, distribution server, and subscription server respectively. Concept 2.

Figure 2. Basic concepts of publishing, distribution, and subscription

 

Publishing Server

The Publishing Server in Figure 2 contains the database to be released. That is, the source database that needs to distribute content to other data sources. Of course, the data to be published must first be allowed to be published. Detailed settings here will be mentioned later in the article.

Distributor

The distribution server in Figure 2 contains the distribution database, which stores the data sent by the distribution server. One distribution server supports multiple publishing servers, just like a newsstand can sell magazines from multiple publishers. Likewise, the distribution server can be the same as the Publishing Server. This is like a publisher selling a magazine without passing through a newsstand.

Subscription Server

The subscription server in Figure 2 contains a copy of the data published by the publishing server. This copy can be a database, a table, or even a subset of a table. According to different settings, some publishers publish updates to the subscription server as read-only (for example, OLAP environment for report output ), or the subscription server can also update these changes and submit them to the publisher.

 

Publishing and article

Publishing refers to a set of articles that can be published. These articles include tables, stored procedures, views, and user-defined functions, as shown in 3.

Figure 3. Publishable content

 

When publishing a table, you can also publish only the subset of the table according to the conditions.

 

Subscription

Subscription is a relative release concept. Subscription defines the distribution server from which the subscription server receives the release. There are two kinds of subscription methods: Push and pull. You can find out the meaning of push and subscription by name. When the Publishing Server generates an update, the distributor directly updates the subscription content, and the subscriber needs to regularly check whether the distributor has available updates. If there are available updates, the subscriber will update the data.

 

Replication type

SQL Server divides replication methods into three categories. Each release can only have one replication type, namely snapshot replication, transaction replication, and merge replication.

 

Snapshot replication

Snapshot replication creates an image for all published tables and copies them to the subscription server at a time. Updates in the middle are not automatically transmitted to the subscription server as those in other replication types. From this concept, we can easily see that snapshot replication features:

1. The network bandwidth is used. Because the entire image is transmitted at one time, the snapshot copy content should not be too large.

2. Suitable for data that is not updated frequently but is relatively large each time. For example, the enterprise employee information table is updated every six months.

3. The subscription server is suitable for OLAP read-only environments.

The image matching from msdn can elaborate snapshot replication, as shown in figure 4.

Figure 4. snapshot replication

 

Transaction Replication

Transaction replication is like its name. After the transaction replication is set for the first time, the published tables and stored procedures will be mirrored, and every change made to the publishing server will be sent to the subscription server in the form of logs. This makes the Publishing Server and subscription server almost synchronous. Therefore, we can see that the features of transaction replication are:

1. The content of the Publishing Server and the subscription server can be basically synchronized.

2. The network connections between the Publishing Server, distribution server, and subscription server must be smooth.

3. The subscription server can also be set to request subscription, so that the subscription server does not need to be connected to the distribution server all the time.

4. Suitable for real-time environments.

 

The matching diagram from msdn can elaborate on transaction replication, as shown in Figure 5.

Figure 5. Transaction Replication

 

Merge and copy

Merge replication allows the Publishing Server to update the database and the subscription server to update data. These updates are merged regularly to ensure that the released data is consistent across all nodes. Therefore, it is possible that the publishing server and the subscription server have updated the same data. When a conflict arises, the publisher does not take precedence over the publishing server, but processes the conflict according to the settings, these will be discussed in subsequent articles.

The matching diagram from msdn can elaborate merging and copying, as shown in 6.

Figure 6. Merge and copy

 

Create a simple transaction Replication

Next I will perform a simple transaction replication. First, install two SQL Server instances locally. The two instances installed on my local machine are SQL Server 2008r2 and SQL Server 2012, where SQL Server 2008r2 serves as the release and distribution server, SQL Server 2012 is used as the subscription server, as shown in figure 7.

Figure 7. Two copied instances

 

Configure the Publishing Server and distribution server on SQL Server 2008r2, and select configure distribution, as shown in figure 8.

Figure 8. Configure Distribution

 

Select the same server as the distribution server, as shown in figure 9.

Figure 9. Set the Publishing Server and distribution server to the same server

 

Set the snapshot folder. As shown in the above msdn figure, the snapshot proxy needs to save snapshots on the distributor. Set this directory, as shown in 10.

Figure 10. Set the snapshot folder

 

It is worth noting that the read permission needs to be set for this directory for everyone, as shown in 11.

Figure 11. Set read permission

 

The next step is to configure the distribution wizard according to the default value, as shown in Figure 12.

Figure 12. Configure the distribution wizard

 

Keep the default values for the remaining steps, and finally configure the Publishing Server and distribution server on the SQL Server 2008r2 instance, as shown in Figure 13.

Figure 13. publishing and distribution Servers configured successfully

 

Create a release. Select create release, as shown in figure 14.

Figure 14. New Release

 

All the way to next, select transaction release when selecting the release type, as shown in Figure 15.

Figure 15. Select transaction release

 

Publish a table for testing. There are only two columns, one is the auto-incrementing int type primary key ID, and the other is the randomly set column, as shown in 16.

Figure 16. Set the published table (Article)

 

If the next page does not filter articles, keep the default value for the next step. In the next window, select create snapshot now and initialize... 17.

Figure 17. Create a snapshot and initialize it now

 

The Security Settings keep the same account as the SQL Server Agent, as shown in Figure 18.

Figure 18. The snapshot proxy and log reading proxy settings are the same account as the SQL Server Agent.

 

The remaining steps are all the way to the next step. After the release name is set, the release is successfully created, as shown in Figure 19.

Figure 19. Publishing created successfully

Next, create a subscription on an instance of SQL Server 2012, and select create subscription, as shown in Figure 20.

Figure 20. Create a subscription

 

On the welcome page, select next and select the release you just created, as shown in Figure 21.

Figure 21. Select a Publishing Server

 

Next, select push subscription so that the changes made by the Publishing Server can be automatically changed to the subscription server, as shown in Figure 22.

Figure 23. Select push subscription

 

Select keep connection. Next, retain the default value, and then select the simulated process account under the security of the distribution agent. 24.

Figure 24. Select a simulated process account

 

Keep the default value until the subscription is created, as shown in Figure 25.

Figure 25. Subscription created successfully

 

Now let's test, insert 100 pieces of data into the table, monitor the status, and find that 100 transactions have been successfully transferred to the subscription server, as shown in Figure 26.

Figure 26. The 100 inserted data records have been successfully transferred to the subscription server.

 

Now let's take a look at the subscription server (SQL Server 2012). The 100 pieces of data inserted on the publishing server have been successfully stored on the subscription server, as shown in 27.

Figure 27.100 data entries have been successfully published to the subscription Server

 

 

Summary

This article provides a general description of SQL Server replication and implements a simple copy. The concept of replication needs to be involved in all aspects of SQL Server. The next article in this series will copy some of the application modes.