Getting Started with SQL Server replication (i) Introduction to----replication

Introduction

Replication in SQL Server (Replication) is one of the core features of SQL Server high availability, which in my view is not just a technology, but a collection of column technologies, ranging from storing and forwarding data to synchronizing data to maintaining data consistency. Using replication not only requires familiarity with your business, but also a comprehensive understanding of the overall replication functionality, and this series of articles is intended to be a simple and comprehensive discussion of replication in SQL Server. (PS: In my last article I found that some pictures of some articles used MSPaint hand-painted more feel, but was a lot of people spit groove, so without considering the personal sense of shame, some of the pictures in this series continue to use MSPaint).

What is replication?

Copy, English is replication, the word originates from the Latin replicare, the original intention is to repeat. This is also the meaning of replication in SQL Server, which means that the core function of replication is to store and forward, meaning that after changing the data in one place and adding and removing it, the action is repeated to the other data sources, as shown in concept 1.

Figure 1: Basic Concepts of replication

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

1. Load balancing----Reduce the load on the current server by replicating data to other database servers, for example, the most typical application is distributing data to separate OLTP and OLAP environments.

2. Partitioning----isolates frequently used data from historical data and replicates historical data to other databases

3. Authorizing----to provide a portion of the data to the person who needs to use the data for its use

4. Data merging-Each region has its own data and merges its data. For example, a large company, each region has its own sales data, the headquarters need to summarize the data.

5. The failover----replicate all data so that it can be transferred when it fails.

There are a variety of reasons to use replication, but before you use it, you first need to understand the constituent elements required by replication technology.

Components of replication

The concept of replication is much like the model of a magazine, which, when published from a publisher, needs to be distributed to the people who subscribe to the magazine through the newsstand and other places. This is the same concept for SQL Server replication. For SQL Server replication, publishers, kiosks, and subscribers correspond to publishers, distributors, subscribers, respectively. As shown in concept 2.

Figure 2: Basic concepts for publishing a distribution subscription

Publishing Server

The publisher in Figure 2 contains the database that needs to be published. That is, the source database that needs to distribute the content to other data sources. Of course, the published data needs to be allowed to be published first. The detailed settings here are mentioned later in this article.

Distribution server

The distributor in Figure 2 contains the distribution database, which is the role of the distribution database to store data sent from the forwarding publisher. A Distributor supports multiple publishers, just as a newsstand can sell magazines from multiple publishers. Similarly, the distributor can be the same instance as the Publisher, as publishers do not sell their magazines directly through newsstand.

Subscriber server

The subscriber in Figure 2 contains a copy of the data published by the publisher. This copy can be a database, or a table, or even a subset of a table. Depending on the settings, some publishers publish updates to Subscribers that are read-only (for example, an OLAP environment for reporting), or that subscribers can update to submit these changes to the publisher.

Publications and articles

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

Figure 3. Content that can be published

When we publish a table, we can also publish only a subset of the table based on the qualification criteria.

Subscription

A subscription is a concept of relative publishing, which defines which distributor the Subscriber receives the publication from. There are two types of subscriptions, push subscriptions (push) and pull subscriptions, which, by name, can be words too literally to know that, in the case of a push subscription, the Distributor updates the content of the subscription directly when the publisher generates the update, and the pull subscription requires subscribers to periodically check to see if updates are available at the Distributor , the Subscriber updates the data if there are available updates.

Type of replication

SQL Server divides replication into three main categories, each of which can have only one type of replication: snapshot replication, transactional replication, and merge replication.

snapshot replication

Snapshot replication makes all of the published tables a mirror and then copies them to the Subscriber at once. Intermediate updates are not automatically routed to subscribers like other replication types. This concept is not difficult to see, the characteristics of snapshot replication will be:

1. Occupy the network bandwidth, because the entire mirror is transmitted at once, so the content of the snapshot copy should not be too large.

2. Suitable for those with less frequent updates, but each update is relatively large data. such as Enterprise Employee Information table, every six months to update this kind of business scenario.

3. Suitable for subscribers is an OLAP-only environment.

The mapping from MSDN is a good illustration of snapshot replication, as shown in 4.

Figure 4: Snapshot replication

Transactional replication

Transactional replication, like its name, replicates transactions. After the transaction replication is set up for the first time, the published tables, stored procedures, and so on will be mirrored, and each subsequent change to the Publisher will be routed to the subscriber as a log. Enables the Publisher and subscribers to be almost synchronized. Therefore, it can be seen that transactional replication is characterized by:

1. Publisher and subscriber content can be synchronized basically

2. The Publisher, distributor, and network connections between subscribers should be kept unblocked.

3. Subscribers can also be set up as pull subscriptions, so that subscribers can also stay connected to the distributor without having to keep them on the server.

4. Suitable for environments where real-time is required.

The mapping from MSDN is a good illustration of transactional replication, as shown in 5

Figure 5: Transactional replication

Merge replication

Merge replication allows the Publisher to update the database and also allows subscribers to update the data. These updates are periodically merged so that the published data remains consistent across all nodes. As a result, it is possible that the Publisher and Subscribers update the same data, and when conflicts arise, they are not handled exactly as the publisher takes precedence over the conflicts, but are processed according to the settings, which are described in subsequent articles.

The map from MSDN is a good illustration of merge replication, as shown in 6.

Figure 6: Merge replication

Create a simple transactional replication

Below I make a simple transactional replication. First, install two instances of SQL Server locally, and two of my native installations are SQL Server 2008R2 and SQL Server 2012, where SQL Server 2008r2 as a publishing and distribution server, SQL Server 2012 as Subscribers, 7 shows.

Figure 7. Two instances of replication

First configure the Publisher and Distributor on SQL Server 2008R2, and select Configure distribution, as shown in 8.

Figure 8: Configure distribution

Select the Publisher and Distributor as the same as 1, 9 as shown.

Figure 9: Setting up the Publisher and distributor for the same server

Set up the snapshot folder, as shown in the above MSDN diagram, the Snapshot Agent is required to stage the snapshot at the Distributor, set this directory, 10.

Figure 10. Setting the snapshot folder

It is worth noting here that you need to set read permissions to this directory for everyone, as shown in 11.

Figure 11: Set Read permissions

The next Configuration Distribution Wizard will follow the default values, as shown in 12.

Figure 12: Configure the Distribution Wizard

The remaining steps remain the default values, and finally the Publisher and Distributor are successfully configured on the SQL Server 2008r2 instance, as shown in 13.

Figure 13: Successfully configuring the publishing and distribution server

The following is the establishment of a release, choose New Release, 14 shows.

Figure 14: New Publication

Next, select a transactional publication when selecting a release type, as shown in 15.

Figure 15: Select a transactional publication

Publish a table for testing with only two columns, one for the self-increment int type primary key ID, and the other for the arbitrarily set column, as shown in 16.

Figure 16: Set up a published table (article)

The next page does not filter the article, keep the default value directly next. In the next window, select Create Snapshot now and initialize: As shown in 17.

Figure 17. Create Snapshot and initialize now

The security settings remain the same as SQL Server agent accounts, as shown in 18.

Figure 18. Snapshot Agent and log read proxy settings and SQL Server agent same account

The rest of the steps are all the way to the next step, after setting the release name, create the publication successfully, as shown in 19.

Figure 19: Successful creation of the publication

Let's create a subscription on an instance of SQL Server 2012, choose New Subscription, 20.

Figure 20: Create a new subscription

After selecting Next on the Welcome screen, select the publication you just created, as shown in 21.

Figure 21: Selecting a Publisher

The next step is to select a push subscription so that changes made by the Publisher can be automatically changed to Subscribers, as shown in 22.

Figure 23: Select a push subscription

Select Keep the connection, next leave the default value, and then under Distribution Agent security, select the impersonated process account. As shown in 24.

Figure 24: Select the demo process account

Keep the default value, all the way to the next until the subscription creation is complete, as shown in 25.

Figure 25: Create a subscription successfully

Now we are testing, inserting 100 data into the table, monitoring the status, and discovering that 100 transactions have been successfully uploaded to the Subscriber, as shown in 26.

Figure 26: The 100 data inserted has been successfully delivered to the subscriber

Now let's look at the Subscriber (SQL Server 2012), where the 100 data inserted at the Publisher has successfully been present at the Subscriber, as shown in 27.

Figure 27.1 Data has been successfully published to the subscriber

Summary

This article provides a brief overview of SQL Server replication and enables a simple replication. The concept of replication needs to be covered in all aspects of SQL Server, and the next article in this series will replicate some of the patterns applied.

Getting Started with SQL Server replication (i) Introduction to----replication